Return rows other than top 20 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Return rows other than top 20

Author

Topic

suryabhaskar
Starting Member

3 Posts

Posted - 2007-10-05 : 14:40:42

Hi All,

I have a question.

Select top 20 from employee

The above statement return top 20 rows.. Is there a way to get the rows other than the top 20. Means, i suggest the rest of the rows leaving the top 20. There are no key fields in the table.

iam using sql server 2005

Please suggest.. and thanks for the help

Thanks
Bhaskar

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-05 : 14:43:41

select *
from employee e1
where not exists (Select top 20 * from employee e2 where e1.id = e2.id and e1.someOtherIdIfYouNeedIt = e2.someOtherIdIfYouNeedIt)
)

also note that you need to use the order by in your top 20 select to get consistently ordered results.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

suryabhaskar
Starting Member

3 Posts

Posted - 2007-10-05 : 14:47:56

Thank you for your quick response.

But believe me it did not work for me..

No rows are returned

Select * from Employee e1 where
not exists ( Select top 3 * from Employee e2 where e1.empid = e2.empid )

Thanks
Bhaskar

suryabhaskar
Starting Member

3 Posts

Posted - 2007-10-05 : 14:54:32

The below statement works if i have a key field.. But in my case i do not have a key field

Select * from Employee e1 where empid not in
( Select top 3 Empid from Employee e2 )

Thanks for the help

bhaskar

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-05 : 15:29:23

you need an order by.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

Kristen
Test

22859 Posts

Posted - 2007-10-06 : 04:37:21

(in the inner SELECT TOP 3 bit, in case that is not obvious!)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources