CODEWITHSUNDEEP
sqloracle-database
O P
O P

Reputation: 2365

Max rows by group

Current SQL:

select t1.*
  from table t1
 where t1.id in ('2', '3', '4')

Current results:

id | seq
---+----
 3 |   5
 2 |   7
 2 |   5
 3 |   7
 4 |   3

Attempt to select maxes:

select t1.*
  from table t1
 where t1.id in ('2', '3', '4')
   and t1.seq = (select max(t2.seq)
                   from table2 t2
                  where t2.id = t1.id)

This obviously does not work since I'm using an in list. How can I adjust my SQL to get these expected results:

id | seq
---+----
 2 |   7
 3 |   7
 4 |   3

Upvotes: 1

Views: 78

Answers (4)

Boneist
Boneist

Reputation: 23578

Here's another example, using the first/last method I mentioned earlier in the comments:

with sd as (select 3 id, 5 seq, 1 dummy from dual union all
            select 2 id, 7 seq, 2 dummy from dual union all
            select 2 id, 5 seq, 3 dummy from dual union all
            select 3 id, 7 seq, 4 dummy from dual union all
            select 3 id, 7 seq, 5 dummy from dual union all
            select 4 id, 3 seq, 6 dummy from dual)
select id,
       max(seq) max_seq,
       max(dummy) keep (dense_rank first order by seq desc) max_rows_dummy
from   sd
group by id;

        ID    MAX_SEQ MAX_ROWS_DUMMY
---------- ---------- --------------
         2          7              2
         3          7              5
         4          3              6

The keep (dense_rank first order by ...) bit is requesting to keep the values associated with the rank of 1 in the order list of rows. The max(...) bit is there in case more then one row has a rank of 1; it's just a way of breaking ties.

Upvotes: 0

tvCa
tvCa

Reputation: 816

select *
from table
where (id,seq) in
(
select id,max(seq)
from table
group by id
having id in ('2','3','4')
);

That is if id and/or seq are completely part of the PK of that table.

Upvotes: 0

Michael Humelsine
Michael Humelsine

Reputation: 601

Group By is your friend:

SELECT 
    id,
    MAX(seq) seq
FROM TABLE
GROUP BY id

EDIT: Response to comment. To get the rest of the data from the table matching the max seq and id just join back to the table:

SELECT t1.*
FROM TABLE t1
INNER JOIN (
    SELECT 
        id
        MAX(seq) as seq
    FROM TABLE
    GROUP BY id
) as t2
on t1.id = t2.id
and t1.seq = t2.seq

EDIT: Gordon and Jean-Francois are correct you can also use the ROW_NUMBER() analytic function to get the same result. You need to check the performance difference for your application (I did not check). Here is an example of that:

SELECT *
FROM (
    SELECT ROW_NUMBER() OVER (
        PARTITION BY id 
        ORDER BY seq DESC) as row_num
        ,*
    FROM TABLE
) as TMP
WHERE row_num = 1

Upvotes: 5

HaveNoDisplayName
HaveNoDisplayName

Reputation: 8487

This SQL Query will give you max seq from individaul ID.

SELECT t1.*
FROM t1
WHERE t1.id in ('2', '3', '4')
   AND NOT EXISTS (
                    SELECT * 
                    FROM t1 t2
                    WHERE t2.id = t1.id 
                    AND t2.seq > t1.seq

Upvotes: 0

Related Questions