SQL join unexpected result

0 votes

1 answer

224 views

join sql-server-2017

                          I have the following table:

|item    |    running   |  resourceid |
|--------|--------------|-------------|
|017510  |      C1      |  	43        |
|338877  |      C4      |  	44        | 
|451233  |      C1      |  	45        |
|771225  |  	C4      |   41        |
|011212  |  	C4      |  	47        |
|313366  |  	C3      |  	34        |
|771226  |  	C4      |  	48        |
|990000  |  	C4      |  	46        |

for each "running" I need to get the Max resourceid and it's item which give me the expected result:

|running  |   resourceid |  item |
|---------|--------------|-------|
|  C1     |   	45	     | 451233 |    
|  C3     |   	34	     |    313366   | 
|  C4     |   	48	     |    771226   | 

With this code:

    SELECT
    b.running,
    MAX(b.resourceid)as MaxResourceid,
    MAX(b.item) as item
    FROM runningResources as b
    inner join (SELECT 
    	running,
    	MAX(resourceid) as MaxValue
    	FROM runningResources
    	GROUP BY running ) a ON
    	a.running=b.running and a.Maxvalue=b.resourceid
    	group by b.running

Since the query

    SELECT
    b.running,
    MAX(b.resourceid)as MaxResourceid,
    MAX(b.item) as item
    FROM runningResources as b
    group by b.running

gives the result:

| running  |   resourceid  | item |
|----------|---------------|-------|
|  C1      |  	45	       |  451233    |
|  C3      |  	34	       |  313366    |
|  C4      |  	48	       |  **990000** |   

why is C4 in the final result showing **771226**    ?
I would think that the join would give the item of the outer SELECT (C4,48,990000)
 

                        

Asked by Enirdas (11 rep)

May 24, 2022, 05:18 PM
Last activity: Jun 25, 2025, 02:02 PM

SQL join unexpected result

Related Questions