0

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)

3
  • 1
    I suggest you try removing the MAX() and the GROUP BY from the external, main query. May 24 at 17:51
  • ty, it does make more sense without the MAX() and the GROUP BY as you suggested.
    – Enirdas
    May 24 at 18:14
  • You don't have a row with C4,48,990000. Your second query is grouping by running and getting the max of each of those columns - these maxes come from different rows. Your first query is just getting the max resourceid per running and then joining back on that to get the rest of the row. May 24 at 22:25

1 Answer 1

0

use row_number() to find the row with the highest resourceid. And then filter it at the outer query

select running, resourceid, item
from   ( 
           select *, rn = row_number() over (partition by running 
                                                 order by resourceid desc)
           from   runningResources 
       ) as a
where  a.rn = 1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.