Query to fetch max records from a table

Question

I am having a table name batch_log whose structure is as below

batch_id    run_count   start_date  end_date
1           4           03/12/2014  03/12/2014
1           3           02/12/2014  02/12/2014
1           2           01/12/2014  01/12/2014
1           1           30/11/2014  30/11/2014
2           5           03/12/2014  03/12/2014
2           4           02/12/2014  02/12/2014
2           3           01/12/2014  01/12/2014
2           2           30/11/2014  30/11/2014
2           1           29/11/2014  29/11/2014
3           3           02/12/2014  02/12/2014
3           2           01/12/2014  01/12/2014
3           1           30/11/2014  30/11/2014

I need to fetch rows for all the batch_id with max run_count. result of the query should be :

batch_id    run_count   start_date  end_date
1           4           03/12/2014  03/12/2014
2           5           03/12/2014  03/12/2014
3           3           02/12/2014  02/12/2014

I tried many options using, group by batch_id and run_count but not able to get the correct result

select a.* from batch_log a,batch_log b 
where a.batch_id =b.batch_id
and a.run_count=b.run_count
and a.run_count in (select max(run_count) from batch_log
group by batch_id ) order by a.batch_id

Plese help

http://stackoverflow.com/questions/27185746/how-to-get-latest-two-rows-with-certain-value-by-date-in-sql or http://stackoverflow.com/questions/27011494/group-by-with-maxtimestamp or http://stackoverflow.com/questions/3491329/group-by-with-maxdate — , Dec 03 '14 at 08:36

score 4 · Answer 1 · answered Dec 03 '14 at 08:37

4

select *
from(
select a.*, max(run_count) over (partition by batch_id) max_run_count
from batch_log a)
where run_count=max_run_count;

answered Dec 03 '14 at 08:37

a.j. tawleed

894
1
8
22

1

You should learn using over, it really solves most of those problems. – a.j. tawleed Dec 03 '14 at 09:00

score 1 · Answer 2 · answered Dec 03 '14 at 08:46

1

This should also work:

SELECT * FROM batch_log b1
WHERE b1.run_count = (SELECT max(b2.run_count) 
                      FROM batch_log b2 
                      WHERE b2.batch_id = b1.batch_id 
                      GROUP BY b2.batch_id)

answered Dec 03 '14 at 08:46

iris

139
5

score 0 · Answer 3 · answered Dec 03 '14 at 08:51

You can do it by this query :

select * 
from batch_log a
inner join (
            select b.batch_id , max(run_count) as run_count 
            from batch_log b 
            group by b.batch_id 
            ) c on a.batch_id = c.batch_id and a.run_count = c.run_count

Hope it helps

score 0 · Answer 4 · answered Dec 03 '14 at 09:15

Answer given by Arion is looking perfect to me. You can modify to this as per below to achieve your exact requirement

SELECT batch_id,run_count,start_date,end_date
FROM
( 
    SELECT
        ROW_NUMBER() OVER(PARTITION BY batch_id ORDER BY run_count DESC) AS RowNbr,
        batch_log.*
    FROM
        batch_log
) as batch
WHERE 
    batch.RowNbr=1

Query to fetch max records from a table

4 Answers4