How to get the second last record per group?

Question

I have an table called DemoTable contains some fields like com_name, updated_date, demo_var, I queried on it like

select * from DemoTable where demo_var=100;

so for example here i got 1000 records. On this 1000 record i want to query to get the last updated row of com_name based on updated_date

My table looks like

id   demo_var   com_name   updated_date

1     100         XYZ         2017-11-10

2     100         XYZ         2017-11-09

3     100         ABC         2017-10-10

4     100         ABC         2017-10-11

5     150         AJD          2017-11-11

First I want to fecth where demo_var=100 and get the different com_name which are last before updated ones.

For example like

2     100         XYZ         2017-11-09

3     100         ABC         2017-10-10

I want these two records to be fetched.

if id field is a auto increment value then you can use order by id desc limit 0,1 — Manoj Gupta, Nov 21 '17 at 12:10
You want last updated records but your result said otherwise. — Harshil Doshi, Nov 21 '17 at 12:12
Your expected output is against your statement it shows with older updated_date — M Khalid Junaid, Nov 21 '17 at 12:13
Do you mean "second last"? And did your searches really not find any info on how to do this? For instance, https://stackoverflow.com/questions/15453231/select-the-second-last-record-in-each-group / https://stackoverflow.com/questions/39087627/second-last-records / https://stackoverflow.com/questions/12325142/retrieve-the-second-last-record-for-each-user-from-the-database — underscore_d, Nov 21 '17 at 12:15
ya second last. I have tried but i didn't get, so if anyone helped me it would be nice — praveen jp, Nov 21 '17 at 12:16
What have you tried? [Edit] your post to show it and why it didn't work. That way, people might avoid telling you things you already know and/or already discounted. — underscore_d, Nov 21 '17 at 12:17

Harshil Doshi · Answer 1 · 2017-11-21T12:36:27.747

1

Following query will work:

select t.demo_var,t.com_name,max(t.updated_date)
from
    (
    select demo_var,com_name,upddated_date
    from DemoTable
    where demo_var=100
    and (com_name,updated_date) not in (select com_name,max(updated_date)
                                        from DemoTable
                                        where demo_var=100
                                        group by demo_var,com_name
                                       )
    )t
group by t.demo_var,t.com_name;

edited Nov 21 '17 at 12:36

answered Nov 21 '17 at 12:10

Harshil Doshi

3,497
3
14
37

1

Not very scalable though – Strawberry Nov 21 '17 at 12:33
@Strawberry Yes, I know. I am trying come up with better solution.Will update here once I figured. – Harshil Doshi Nov 21 '17 at 12:34
You have written only for id 1. I want dynamic query – praveen jp Nov 21 '17 at 12:35
You asked only for `demo_var=100`. If you want result for each `demo_var`, just remove both `where` clause from query and it'll work – Harshil Doshi Nov 21 '17 at 12:37
Please try the edited query. – Harshil Doshi Nov 21 '17 at 12:42

score 1 · Accepted Answer · answered Nov 21 '17 at 12:34

To achieve second latest row per group you can use following query

select a.*
from demo a
where  a.demo_var = 100
and (
    select count(*) 
    from demo b
    where b.demo_var = 100
    and a.com_name = b.com_name
    and case when a.updated_date = b.updated_date
        then a.id > b.id 
        else a.updated_date < b.updated_date
        end
) = 1 /* here 1 is for second last , 0 for latest and so on */

Note it compare rows by updated_date so if there are 2 rows for same updated_date and com_name then i have used id column to pick the second latest row and is assume that id column is set to auto increment by default

Demo

How to get the second last record per group?

2 Answers2