SQL join unexpected result



  • 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)



  • 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
    



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2