CODEWITHSUNDEEP
sqlsql-server
user3262364
user3262364

Reputation: 371

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 don't want to show top 5 records, I need to show recent 5 records, I mean last 5 records, but I cannot to ORDER BY pout DESC, because I want to show report in correct format, but I need to show only last 5 records of my transaction without using descending order

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

Upvotes: 2

Views: 172

Answers (2)

Esty
Esty

Reputation: 1912

You may use ROW_NUMBER but there also you have to use a DESC with it.

; 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

Upvotes: 0

sagi
sagi

Reputation: 40481

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 ;

Upvotes: 4

Related Questions