Max rows by group

Question

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

possible duplicate of [Simple Query to Grab Max Value for each ID](http://stackoverflow.com/questions/755918/simple-query-to-grab-max-value-for-each-id) — HaveNoDisplayName, Jan 28 '15 at 21:03
Trying to figure out what would be wrong with : select id, max(seq) from t1 group by id having id in ('2','3','4'); — tvCa, Jan 28 '15 at 21:54

Michael Humelsine · Accepted Answer · 2015-01-28T21:01:23.137

5

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

edited Jan 28 '15 at 21:01

answered Jan 28 '15 at 20:36

Michael Humelsine

601
4
6

Sometimes the simplest approach is elusive. – Gordon Linoff Jan 28 '15 at 20:39
@Michael Humelsine How do I do this if I'm selecting by `t.*`? My table actually has more fields than what I've given in the example. – O P Jan 28 '15 at 20:41
@OP . . . In that case, you should use Jean-Francois's deleted answer. `row_number()` is the way to go. – Gordon Linoff Jan 28 '15 at 20:46
You could always use the [first](http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions056.htm)/last aggregate functions to "keep" the values of the related columns. – Boneist Jan 28 '15 at 21:33
@OP: Your opening post says ID and SEQ are the only columns. – tvCa Jan 28 '15 at 22:09

score 0 · Answer 2 · answered Jan 28 '15 at 20:36

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

score 0 · Answer 3 · answered Jan 28 '15 at 22:28

0

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.

answered Jan 28 '15 at 22:28

tvCa

796
6
13

score 0 · Answer 4 · answered Jan 29 '15 at 09:42

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.

Max rows by group

4 Answers4