Show last 5 records in sql without using desc

Question

I have a query like this:

SELECT TOP 5 CONVERT(varchar(15), CAST(Pout AS time), 100) AS 'OUT', CONVERT(varchar(15), CAST(pIn AS time), 100) AS 'IN', DATEDIFF(MINUTE, CONVERT(varchar(5), Pout, 108), CONVERT(varchar(5), pIn, 108)) AS [Total Used] FROM loginfo WHERE empid = 1001 AND CONVERT(date, pDate) = '28/Nov/2016 2:45:00 PM' AND pOut IS NOT NULL ORDER BY pOut 

This query works fine, but I don’t want to show the first 5 records, I need to show the last 5 records, I mean the last 5 records, but I can’t ORDER BY pout DESC because I want to show the report in the correct format, but I need show only the last 5 records of my transaction without using descending order

Example, if I have a transaction of 10 records, I want to show only the last 5 records in the correct format

Answer 1

Use ROW_NUMBER() :

 SELECT * FROM ( SELECT CONVERT(varchar(15), CAST(Pout AS time), 100) AS [OUT], CONVERT(varchar(15), CAST(pIn AS time), 100) AS [IN], DATEDIFF(MINUTE, CONVERT(varchar(5), Pout, 108), CONVERT(varchar(5), pIn, 108)) AS [Total Used], ROW_NUMBER() OVER(ORDER BY pOut DESC) as rnk FROM loginfo WHERE empid = 1001 AND CONVERT(date, pDate) = '28/Nov/2016 2:45:00 PM' AND pOut IS NOT NULL) p WHERE p.rnk <= 5 ORDER BY What_Ever_You_Want 

Or, conversely, use a view:

 SELECT * FROM (Your Current Query) ORDER BY pOut ; 

Answer 2

You can use ROW_NUMBER , but you also need to use DESC .

 ; WITH CTE AS ( SELECT CONVERT(varchar(15), CAST(Pout AS time), 100) AS 'OUT', CONVERT(varchar(15), CAST(pIn AS time), 100) AS 'IN', DATEDIFF(MINUTE, CONVERT(varchar(5), Pout, 108), CONVERT(varchar(5), pIn, 108)) AS [Total Used], ROW_NUMBER() OVER(ORDER BY pOut DESC) AS RN FROM loginfo WHERE empid = 1001 AND CONVERT(date, pDate) = '28/Nov/2016 2:45:00 PM' AND pOut IS NOT NULL ) SELECT * FROM CTE WHERE RN BETWEEN 1 AND 5 ORDER BY OUT