CODEWITHSUNDEEP
sqlsql-servert-sql
Shan
Shan

Reputation: 423

Query for fetching last n rows excluding the last row

I have a table with 400 rows. I want to select last 6 rows excluding the last row.

With the following code I am getting the last 6 rows but I don't want the last(400th) row to be there

SELECT * 
FROM ImagesInfo 
WHERE Image_Id IN 
     (    SELECT TOP 6 Image_Id 
          FROM ImagesInfo 
          ORDER BY Image_Id DESC )

Upvotes: 4

Views: 2201

Answers (7)

user2350772
user2350772

Reputation: 11

Look at OFFSET

SELECT First Name + ' ' + Last Name FROM Employees ORDER BY First Name OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;

[Microsoft SQL Server Website]

http://technet.microsoft.com/en-us/library/gg699618.aspx

Upvotes: 1

AnandPhadke
AnandPhadke

Reputation: 13486

select top 6 * from image_info 
where image_id not in(select max(image_id) from image_info)
order by image_id desc

Upvotes: 0

Joe G Joseph
Joe G Joseph

Reputation: 24046

try this:

You Just have to eliminate the max record from the result set

 SELECT * 
FROM ImagesInfo 
WHERE Image_Id IN 
     (    SELECT TOP 6 Image_Id 
          FROM ImagesInfo 
          where Image_Id<> (select MAX(Image_Id) from ImagesInfo )
          ORDER BY Image_Id DESC )

OR

If you are using sql server 2005 or above: you can use, ROW_NUMBER() with CTE

with cte as(          
 SELECT *,
        ROW_NUMBER() over (order by Image_Id desc) as row_num 
 FROM ImagesInfo)           
 select * from cte where row_num between 2 and 7

Upvotes: 0

Grisha Weintraub
Grisha Weintraub

Reputation: 7986

SELECT top 6 *
FROM 
     (    SELECT TOP 7 *
          FROM ImagesInfo 
          ORDER BY Image_Id DESC 
     ) t
ORDER BY Image_Id

Upvotes: 2

podiluska
podiluska

Reputation: 51494

Use the ROW_NUMBER() syntax 

SELECT * 
from
(
   SELECT *, ROW_NUMBER() over (ORDER By Image_ID) rn
   FROM yourtable 
) v
WHERE rn between x and y

Upvotes: 2

Michael A
Michael A

Reputation: 9900

If I'm understanding correctly (I'm not certain) then you shoud be able to do:

SELECT * 
FROM ImagesInfo 
WHERE Image_Id IN 
     (    SELECT TOP 7 Image_Id 
          FROM ImagesInfo 
          ORDER BY Image_Id DESC )
     and Image_Id not IN
     (    SELECT TOP 1 Image_ID
          FROM ImagesInfo
          ORDER BY Image_Id DESC )

Upvotes: 0

aF.
aF.

Reputation: 66687

What about:

SELECT * FROM ImagesInfo
WHERE Image_Id IN (SELECT TOP 6 Image_Id FROM ImagesInfo ORDER BY Image_Id DESC) AND
      Image_Id <> (SELECT TOP 1 Image_Id FROM ImagesInfo ORDER BY Image_Id DESC)

OR

SELECT * FROM ImagesInfo
WHERE Image_Id IN (SELECT TOP 6 Image_Id FROM ImagesInfo ORDER BY Image_Id DESC) AND
      Image_Id <> (SELECT MAX(Image_Id) FROM ImagesInfo)

Upvotes: 0

Related Questions