Get last but one row for each ID

Question

I am using query like

select * from audittable where a_id IN (1,2,3,4,5,6,7,8);

For each ID its returning 5-6 records. I wanted to get the last but one record for each ID. Can i do this in one sql statement.

do you have any created_date or some other id column? Or can you post your table script, so we could help you — vikas, Jun 06 '13 at 05:16
see here http://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group — PSR, Jun 06 '13 at 05:16
better to create one dummy column for a_id , then write the query through that column. — shankar.parshimoni, Jun 06 '13 at 05:23
in your database r u storing any date as well like `datecreated` or etc that when your entry is entered or not and show me your column structure as well. — Rahul, Jun 06 '13 at 05:26

score 0 · Answer 1 · answered Jun 06 '13 at 05:22

Try this query

SELECT 
   * 
FROM 
   (SELECT 
       @rn:=if(@prv=a_id, @rn+1, 1) as rId,
       @prv:=a_id as a_id,
       ---Remaining columns
   FROM 
       audittable
   JOIN
       (SELECT @rn:=0, @prv:=0) t
   WHERE
       a_id IN (1,2,3,4,5,6,7,8)
   ORDER BY 
       a_id, <column> desc)tmp --Replace column with the column with which you will determine it is the last record
WHERE 
   rId=1;

score 0 · Answer 2 · answered Jun 06 '13 at 05:47

If your database is having DateCreated or any column in which you are saving the DateTime as well like when your data is inserted for a particular row then you may use query like

select at1.* from audittable at1 where 
datecreated in( select max(datecreated) from audittable at2
where 
at1.id = at2.id
order by datecreated desc
);

You may also use LIMIT function as well.

Hope you understand and works for you.

malex · Answer 3 · 2014-01-22T18:38:24.853

In SQLite, you have the columns a_id and b. For each a_id you get a set of b's. Let you want to get the latest/highest (maximum in terms of row_id, date or another naturally increasing index) one of b's

SELECT MAX(b), *
FROM audittable
GROUP BY a_id

Here MAX help to get the maximum b from each group.

Bad news that MySQL doesn't associate MAX b with other *-columns of the table. But it still can be used in case of simple table with a_id and b columns!

Get last but one row for each ID

3 Answers3