CODEWITHSUNDEEP
sqloracle-databasegreatest-n-per-group
codeman coder
codeman coder

Reputation: 51

Query to fetch max records from a table

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

Upvotes: 0

Views: 234

Answers (4)

Krishan Kumar Gorav
Krishan Kumar Gorav

Reputation: 57

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

Upvotes: 0

dramixx
dramixx

Reputation: 274

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

Upvotes: 0

iris
iris

Reputation: 139

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)

Upvotes: 1

a.j. tawleed
a.j. tawleed

Reputation: 904

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;

Upvotes: 4

Related Questions