CODEWITHSUNDEEP
sqloracle-database
user3232446
user3232446

Reputation: 439

How can I get the PK of the Max Row in SQL

I have a SQL Query which gets the Order with the Most Items. I did it like this:

select max(ab.num) as anzahl 
     from ( select auf.anr as anr,count(*) as num from
        Auftrag auf, Table(auf.positionen) po group by auf.anr  ) ab ;

My result look like this:

enter image description here

I want to know how to get the Order-ID [auf.anr] of this Order. How can I modify my query to get the desired Result?

This is the Order Table (Auftrag):

enter image description here

Upvotes: 0

Views: 87

Answers (1)

sstan
sstan

Reputation: 36483

One way to do this would be to use the row_number analytic function:

with cte as (
  select auf.anr as anr,
         count(*) as num
    from Auftrag auf, Table(auf.positionen) po
   group by auf.anr
)
select anr
  from (select anr,
               row_number() over (order by num desc) as rn
          from cte)
 where rn = 1

... or, using the method that Juan Carlos was proposing (using rownum), this would be the syntax:

with cte as (
  select auf.anr as anr,
         count(*) as num
    from Auftrag auf, Table(auf.positionen) po
   group by auf.anr
)
select anr
  from (select *
          from cte
         order by num desc)
 where rownum = 1

Upvotes: 1

Related Questions