0

I have a sqlite database named heroes where I am trying to find the second oldest date for each person that has used the service. To find the oldest date I simply used:

SELECT MIN(Date), Cust_Num FROM heroes GROUP BY Cust_Num;

Now, I am using the following to find the second oldest date:

SELECT MAX(Date), Cust_Num FROM heroes WHERE Date IN (SELECT Date FROM heroes ORDER BY Date LIMIT 2) GROUP BY Cust_Num;

However, it is only giving me the 9 people who used the service for a 2nd time on the first day there is data. Any advice?

Thanks!

Nolan
  • 75
  • 9
  • I would create a temp table ordered the way I want and use the `ROWID` of the temp table to get the value you require. – Mr. Mascaro Oct 31 '14 at 20:22
  • not necessarily the cleanest way, but I just created a new database from the original csv, deleted the min rows and ran my first query again – Nolan Oct 31 '14 at 20:43
  • duplicate of [Get back the second latest date per ID instead of the latest](http://stackoverflow.com/questions/26662167/get-back-the-second-latest-date-per-id-instead-of-the-latest) – CL. Oct 31 '14 at 21:45

1 Answers1

0

If you instead join to the subquery on the customer number you'll be sure the dates match up with the appropriate customer number. With your second attempt you didn't have that certainty.

SELECT h.Cust_Num, MAX(d.Date) 
FROM 
    heroes h 
    JOIN (
        SELECT Cust_Num, Date 
        FROM heroes 
        ORDER BY Date 
        LIMIT 2
    ) d
    ON d.Cust_Num = h.Cust_Num
GROUP BY h.Cust_Num
ORDER BY h.Cust_Num;
mechanical_meat
  • 163,903
  • 24
  • 228
  • 223