How do I use SQL to return the first entry in a chronological sort?

I have a table named Dates, let’s just say with two columns for simplicity purposes. One is labeled “date” and that column is of a date data type, and the other one is labeled timestamp and it’s labeled as a Timestamp data type.

For my program the user selects a date to make a booking. Every time I book, I add a timestamp to my booking object. This way, I can track which bookings came first or not.

So what do I want to do? Well essentially I want the first result of a date in which the timestamp came first.

Let’s say I have a table like this:

Date          Timestamp
11/2/2019     50
11/2/2019     20
11/1/2019     10

And I want to select the date 11/2/2019 and my SQL code would return the first inputted value of this, which is the middle entry.

So my SQL should return this:

Date          Timestamp
11/2/2019     20

How would I write an SQL to get the earliest entry of a specific date given that there is a timestamp attached to it.

Here’s what I have so far:

SELECT*FROM DATES WHERE Date=? ORDER BY timestamp LIMIT 1

But I’m getting a syntax error. How would I rewrite this query to get the first entry based on the value of the timestamp?

Answer

You can use aggregation:

select date, min(timestamp)
from dates
group by date;

If you want other columns, one method is a correlated subquery:

select d.*
from dates d
where d.timestamp = (select min(d2.timestamp) 
                     from dates d2
                     where d2.date = d.date
                    );

If your data has duplicate minimum times, this returns all of them.

Or row_number():

select d.*
from (select d.*,
             row_number() over (partition by date order by timestamp) as seqnum
      from dates d
     ) d
where seqnum = 1;

This returns one row when there are duplicates. If you want all of them, use rank() instead.