Click here to Skip to main content
14,698,883 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I want to return the rows from my table prior to last five inserted rows...

How can i do this...

I only know to get the top five rows but now i want to get the rows inserted before the top five rows.
Posted
Comments
AditSheth 27-Sep-11 0:21am
   
You have not primary key in your table.
Then any other field from which we can order the table. like datetime
Thanks

Assuming your tablename has a primary key by the name of pid :

select * from tablename where pid not in (select top 5 pid from tablename order by pid desc)
   
v2
Comments
Kishore Jangid 26-Sep-11 7:39am
   
I dont want the top 5, but to get all rows other than the top 5
Mehdi Gholam 26-Sep-11 7:53am
   
I have edited the solution.
Kishore Jangid 26-Sep-11 7:55am
   
What if i dont have primary key
Mehdi Gholam 26-Sep-11 8:03am
   
Then create a primary key, your tables absolutely need a primary key, without one performance goes down the drain.
Kishore Jangid 26-Sep-11 8:40am
   
But that table beign a master table doesnt need to have a primary key
Mehdi Gholam 26-Sep-11 8:46am
   
All tables should have a primary key.
Try this

with tmp as(
select color, row_number() over (partition by date order by date) rn
from TableA
)
select * from tmp where rn > 5
   
Comments
MaulikDusara 27-Sep-11 0:40am
   
good one :) my 5

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900