Search code examples
mysqlsqldatabasesequelpro

First and second date from table in SQL

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

Solution

  • 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"