CODEWITHSUNDEEP
sqlsql-servert-sql
timmack
timmack

Reputation: 590

fetching last 3 rows in ascending order with t-sql?

CommentID   Name       Replies                             OrderID
313         Ed         dlfkndl sdknldgf dlffdg               496
313         James      sdsflsdf snflsf sfnslf sfnklsdf       499
313         Jeff       sdsflsdf snflsf sfnslf sfnklsdf       500
313         Alan       sdsflsdf snflsf sfnslf sfnklsdf       501
313         William    sdflksnfdlsk sdfknslnf slfnks         503

I have a sample table above which is in an ascending order by [OrderID]. I want to fetch the last 3 rows of the table which is in an ascending order as well which is shown below.

CommentID   Name       Replies                              OrderID
313         Jeff       sdsflsdf snflsf sfnslf sfnklsdf        500
313         Alan       sdsflsdf snflsf sfnslf sfnklsdf        501
313         William    sdflksnfdlsk sdfknslnf slfnks          503

What is the exact syntax to do this in T-SQL? Thanks... I tried to come up with this but still not not fetching the last 3 rows in an asc order.

SqlCommand cmd = new SqlCommand("SELECT * FROM [RepTab] WHERE [OrderID] > (SELECT MAX([OrderID]) - 3 FROM [RepTab] WHERE [CommentID]='" + Id + "') ", con);

Upvotes: 2

Views: 1307

Answers (3)

Deepshikha
Deepshikha

Reputation: 10274

You can also write the query using Common Table Expression as:

With CTE as
( select row_number() over ( partition by CommentID order by OrderID desc) as rownum,
         CommentID,
         Name,
         Replies,
         OrderID
  From reptab
 ) 
 select  CommentID,
         Name,
         Replies,
         OrderID
 from CTE 
 where rownum <=3
 and CommentID = 313
 order by OrderID asc

Upvotes: 1

crthompson
crthompson

Reputation: 15865

Selecting the bottom 3 records in a subquery ordered descending, then order the outer ascending.

Select *
from
  (select top 3 orderid, commentid, replies, name
   From
     [RepTab] 
   Order by orderid desc
   ) t
Order by order id

fiddle

Upvotes: 1

Navneet
Navneet

Reputation: 447

Please try below query :

In above query paqogomez missed the table alias 

Select *
from
  (select top 3 orderid, commentid, replies, name
   From
     [RepTab] 
   Order by orderid desc
   ) as tbl
Order by orderid

Upvotes: 0

Related Questions