user3262364 user3262364 - 3 days ago 5
SQL Question

show last 5 records in sql without using desc

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 is working fine,but i dont want to show top 5 records,i need to show Recent 5 records,i mean last 5 record,but i cannot do pout desc,because i want to show report in correct format,but i need to show only last 5 recordes of my transaction witout using descending order

example if i have records 10 transaction ,i want to show only last 5 records in correct format

Answer

Use ROW_NUMBER() instead:

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, alternatively , use a derived table :

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