To get highest 15 values without Top cluse

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

General SQL Server Forums

New to SQL Server Programming

To get highest 15 values without Top cluse

Author

Topic

abhiram_414
Starting Member

14 Posts

Posted - 2008-10-01 : 03:32:23

Hi I want to know to get highest 15 values in a column without using Top clause can you plese help me?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-01 : 03:35:19

Can we see what you tried till now? Also is this exam question or assignment?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-01 : 03:43:30

Anyways i'll give a hint. If you're using sql 2005, have a look at books online about ROW_NUMBER() function.

abhiram_414
Starting Member

14 Posts

Posted - 2008-10-01 : 03:49:39

set rowcount 15
select [Name], PresentBasic
from tblEmployees
where PresentBasic < max(PresentBasic)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-01 : 03:50:30

No ORDER BY?
You want random 15 records returned?

E 12°55'05.63"
N 56°04'39.26"

abhiram_414
Starting Member

14 Posts

Posted - 2008-10-01 : 04:49:45

I got it with row count
set rowcount 15
select [Name],
PresentBasic
from tblEmployees
order by PresentBasic desc

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-01 : 04:53:31

quote:
Originally posted by abhiram_414

I got it with row count
set rowcount 15
select [Name],
PresentBasic
from tblEmployees
order by PresentBasic desc

good. and if you're using sql 2005 try this also

SELECT t.[Name],
t.PresentBasic
FROM
(SELECT ROW_NUMBER() OVER(ORDER BY PresentBasic DESC) AS Seq,*
FROM  tblEmployees
)t
WHERE t.Seq<=15

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-01 : 04:55:18

quote:
Originally posted by abhiram_414

I got it with row count
set rowcount 15
select [Name],
PresentBasic
from tblEmployees
order by PresentBasic desc

You need to reset to 0 using set rowcount 0 to not to affect other manipulations in the same scope

Madhivanan

Failing to plan is Planning to fail

abhiram_414
Starting Member

14 Posts

Posted - 2008-10-01 : 05:41:01

Actually I tried the same using Row_NUMBER() in the following way,

SELECT rowid=IDENTITY(int,1,1),[Name],PresentBasic INTO tblEmployees1 from tblEmployees order by presentbasic
select rowid,[Name],PresentBasic from tblEmployees1
select Row_Number() over (order by PresentBasic desc) from tblEmployees

WITH [tblEmployees ORDERED BY ROWID] AS
(SELECT ROW_NUMBER() OVER (ORDER BY PresentBasic desc) AS ROWID, * FROM tblEmployees)
SELECT [Name],PresentBasic FROM [tblEmployees ORDERED BY ROWID] WHERE ROWID <= 15

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-01 : 06:16:34

quote:
Originally posted by abhiram_414

Actually I tried the same using Row_NUMBER() in the following way,

SELECT rowid=IDENTITY(int,1,1),[Name],PresentBasic INTO tblEmployees1 from tblEmployees order by presentbasic
select rowid,[Name],PresentBasic from tblEmployees1
select Row_Number() over (order by PresentBasic desc) from tblEmployees

WITH [tblEmployees ORDERED BY ROWID] AS
(SELECT ROW_NUMBER() OVER (ORDER BY PresentBasic desc) AS ROWID, * FROM tblEmployees)
SELECT [Name],PresentBasic FROM [tblEmployees ORDERED BY ROWID] WHERE ROWID <= 15

first try tries to create a table each time so you might need to drop the table each time after showing results. else sunsequent runs will failing as it again tries to create a table which already exists.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources