CODEWITHSUNDEEP
sqlsql-serversql-server-2008
atricapilla
atricapilla

Reputation: 2640

SQL Server: CTE, how to get last row number

I have a CTE like this:

;WITH Lastdt AS
(
    SELECT database_name, backup_finish_date,
    ROW_NUMBER() OVER (PARTITION BY database_name ORDER BY backup_finish_date, database_name) AS 'RowNumber'
    FROM bckHist
) 
SELECT database_name, backup_finish_date
FROM Lastdt
WHERE RowNumber = '7'

How can I get last row with highest number from the query (replace hardcoded 7)?

Upvotes: 2

Views: 6725

Answers (4)

Sumon Sarker
Sumon Sarker

Reputation: 129

SELECT database_name, backup_finish_date FROM bckHist WHERE RowNumber = (SELECT MAX(RowNumber) FROM bckHist)

Upvotes: 0

oryol
oryol

Reputation: 5248

If you really need only last row (from your query) then it even simpler (for given case):


select database_name, max(backup_finish_date)
from bckHist
group by database_name

Upvotes: 2

Prashant Lakhlani
Prashant Lakhlani

Reputation: 5806

I think it's easy...

;WITH Lastdt AS
(
    SELECT database_name, backup_finish_date,
    ROW_NUMBER() OVER (PARTITION BY database_name ORDER BY backup_finish_date desc, database_name) AS 'RowNumber'
    FROM bckHist
) 
SELECT database_name, backup_finish_date
FROM Lastdt
WHERE RowNumber = '1'

Upvotes: 2

marc_s
marc_s

Reputation: 754953

Try this:

;WITH Lastdt AS
(
    SELECT database_name, backup_finish_date,
    ROW_NUMBER() OVER (PARTITION BY database_name ORDER BY backup_finish_date, database_name) AS 'RowNumber'
    FROM bckHist
) 
SELECT TOP 1 
   database_name, backup_finish_date
FROM Lastdt
ORDER BY RowNumber DESC

Basically order the RowNumber descending (largest first), and pick only the first one.

Upvotes: 1

Related Questions