how to show only one record that you want from SQL data, is the best way to use select top 1?

Question

This is my sql query,

select emailid,
       emailsentime


from [dbo].[clientbase]

where emailsent time > '1995-01-02'
and   emailsent time < '1995-01-03'

order by emailsenttime desc

But it's pulling all the emailids on that date, I only want to show the top row of record(because that is the most recent emailid sent on that date.

I want to experiment, since currently I'm doing by descending, if I'm doing ascending(which would be the least recent one).

But how do I get the top record only without pulling all the data.

what I mean is one I ran this query, it shows data like this

emailid          emailsenttime

1986789          1995-01-02 22:51:12
1986788          1995-01-02 22:50:12
1986787          1995-01-02 22:49:12
1986786          1995-01-02 22:48:12

Answer 1

You want a row-limiting query.

In standard SQL, you would go:

select ...
from ...
where ...
order by emailsenttime desc
fetch first 1 row only

That said, the syntax may vary depending on your database, for example:

• MySQL, Postgres:

  select ...
  from ...
  where ...
  order by emailsenttime desc
  limit 1
  

• Older versions of SQL Server:

  select top (1) ...
  from ...
  where ...
  order by emailsenttime desc