CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008
Subliminal Hash
Subliminal Hash

Reputation: 13742

TSQL query that would return me the earliest date and the latest date in a table

I have a table in a sql server 2008 database that contains bunch of records as well as a date column. The date is inserted automatically when a new entry to the table occurs. So, it contains the date of the record that has been created.

I am trying to run a query that would return me the earliest date and the latest date in this table.

I tried something like;

SELECT    TOP(1) DateAdded AS firstdate 
FROM      News 
ORDER BY  DateAdded DESC;  

SELECT    TOP(1) DateAdded AS lastdate 
FROM      News 
ORDER BY  DateAdded ASC;

but it only returned the 'firstdate'.

Can anyone guide me on how to achieve this?

Upvotes: 1

Views: 317

Answers (3)

Kamila
Kamila

Reputation: 43

You could use something like this:

    select DateAdded     from (SELECT DateAdded,
           row_number() over (order by DateAdded desc) as rn,
           count(*) over () as added_value
    FROM News
) t
where rn = 1
   or rn = added_value
ORDER BY DateAdded DESC

Upvotes: 1

gbn
gbn

Reputation: 432261

The answer is to use aggregates. 

SELECT
    MIN(DateAdded) AS firstdate,
    MAX(DateAdded) AS lastdate
FROM
    News;

Your query returns 2 results: each works individually though

Upvotes: 1

Jose Basilio
Jose Basilio

Reputation: 51478

SELECT 
       MIN(DateAdded) As FirstDate,
       MAX(DateAdded) As LastDate
FROM
       News;

Upvotes: 3

Related Questions