First and second date from table in SQL

Question

In SQL I am trying to extract the first and the second date from a table. I am using the query below which I formulated based on a number of posts I read on here about the same topic i.e. extracting the second date etc. But for some reason it does not give me the right answer and I don't understand why.

The table is called motif_segmentvalue and it has a column called EntryDate which I am interested in using to extract the dates. I wrote a small query for a particular user 5407 to see if I was getting the right result or not.

 select sv2.UserId, sv2.EntryDate, min(sv2.EntryDate)
 from motif_segmentvalue sv2
 where sv2.EntryDate  > (select MIN(sv3.EntryDate) from motif_segmentvalue sv3)
 and sv2.UserId = "5407"

The output that I get is:

 UserId   EntryDate             min(sv2.EntryDate)

  5407    2016-01-20 12:50:00   2016-01-20 12:50:00

Based on my data, this is incorrect and the "EntryDate" column should have a different date to the min(sv2.EntryDate) - it should have the second date. But for some reason, it does not. I cannot understand what I'm doing wrong. Can anyone provide hints on what I might be doing incorrectly? Am I wrongly expecting "EntryDate" to be the second date?

I have also tried many other users and both columns give the same date.

Thanks

Have you tried it using `ORDER BY` clause and `LIMIT` it for 2 rows only — Sachin PATIL, Mar 24 '17 at 16:22
Yup... I get the same result. The end goal is to run this on the whole database for all the users I have and limit doesn't work there, it just returns the first user... — Maheen Siddiqui, Mar 24 '17 at 16:27

Johan · Accepted Answer · 2017-03-24T16:57:43.223

1

I think this will get you what you want.

It has a 2050-01-01 date as backup for when there is no second date

select sv2.UserId, min(sv2.EntryDate) as first
, min(if(sv2.EntryDate=mindate,'2050-01-01', sv2.EntryDate)) as second
from motif_segmentvalue sv2
join (
    select sv3.UserId, min(sv3.EntryDate) as mindate 
    FROM motif_segmentvalue sv3
    WHERE sv3.UserId = "5407"
) as temp ON temp.UserId = sv2.UserId
WHERE sv2.UserId = "5407"

update: This will fetch the second date, instead of the next datetime.

select sv2.UserId, min(sv2.EntryDate) as first
, min(if(date(sv2.EntryDate)=mindate,'2050-01-01', sv2.EntryDate)) as second
from motif_segmentvalue sv2
join (
    select sv3.UserId, min(date(sv3.EntryDate)) as mindate 
    FROM motif_segmentvalue sv3
    WHERE sv3.UserId = "5407"
) as temp ON temp.UserId = sv2.UserId
WHERE sv2.UserId = "5407"

edited Mar 24 '17 at 16:57

answered Mar 24 '17 at 16:39

Johan

931
9
23

I just updated it. I created it with MAX in mind, but saw afterwards I should use MIN :) – Johan Mar 24 '17 at 16:43
Great! Thanks, works perfectly... Just one question... What if I have a user who has multiple times for the same date... so for `5407` I have two times on that date, is there a way to skip to just the next date and not just the next time point? – Maheen Siddiqui Mar 24 '17 at 16:47
You may consider changing '2050-01-01' to null... probably no query we write today will last 30 years, but you never know :) – Stefano Zanini Mar 24 '17 at 16:48
Does it have a unique id column @MaheenSiddiqui ? In case it has, you could CONCAT this field to 'mindate' in the inner sub query and compare against this in you outer select – Johan Mar 24 '17 at 16:50
@Johan no... UserId is as unique as it gets... I used DATE and it seems to be fine... – Maheen Siddiqui Mar 24 '17 at 16:55

score 0 · Answer 2 · answered Mar 24 '17 at 16:37

If I understand correctly, I'd build that query in a few steps.

First of all, get the absolute minimum

select  UserId, min(EntryDate)
from    motif_segmentvalue
where   UserId = "5407" 
group by UserId

Then get the next minimum (using the query above)

select  UserId, min(EntryDate)
from    motif_segmentvalue
where   UserId = "5407" and
        EntryDate > (
            select  UserId, min(EntryDate)
            from    motif_segmentvalue
            where   UserId = "5407" 
            group by UserId
        )
group by UserId

Finally join the two

select  t1.UserId, t1.min_1, t2.min_2
from    (
            select  UserId, min(EntryDate) as min_1
            from    motif_segmentvalue
            where   UserId = "5407" 
            group by UserId
        ) t1
join    (
            select  UserId, min(EntryDate) as min_2
            from    motif_segmentvalue
            where   UserId = "5407" and
                    EntryDate > (
                        select  UserId, min(EntryDate)
                        from    motif_segmentvalue
                        where   UserId = "5407" 
                        group by UserId
                    )
            group by UserId
        ) t2
on      t1.UserId = t2.UserId

Abdullah Dibas · Answer 3 · 2017-03-24T16:49:34.290

-1

Try this:

SELECT *  FROM motif_segmentvalue sv2
WHERE sv2.UserId = "5407" 
ORDER BY EntryDate ASC
LIMIT 2

edited Mar 24 '17 at 16:49

answered Mar 24 '17 at 16:26

Abdullah Dibas

1,499
1
9
13

`Top` doesn't work in my mysql... I have tried limit 2 after order by but I still don't get the right thing. I need a column which lists the second date... – Maheen Siddiqui Mar 24 '17 at 16:29
1

For MySQL, I believe that LIMIT is used, rather than TOP – Degan Mar 24 '17 at 16:33

First and second date from table in SQL

3 Answers3