Query help please - 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)

Query help please

Author

Topic

kkmurthy
Starting Member

41 Posts

Posted - 2009-05-20 : 09:05:54

I have to query the database to pull top ten values. When I use select Top 10 it only pulls Top 10 rows and not Top Ten values.
Here is the data I have:

27 PASSWORD
23 OUTLOOK
20 WINXP
17 NET_ACCESS
15 HOMELESS
15 VPN
11 BLACKBERRY
8 NET_ACCOUNT
7 Network Printer
6 SPYWARE
6 PASSWORD RESETS
6 SW_APP_INSTALL
6 NTWK_WIRELESS

In the above situation I want to pull all the thirteen rows > Actually they represent top ten values:

Help Please.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-20 : 09:10:29

[code]
SELECT *
FROM
(
SELECT *, rank = dense_rank()OVER (ORDER BY value DESC)
FROM data
) d
WHERE rank <= 10
[/code]

KH
[spoiler]Time is always against us[/spoiler]

keyursoni85
Posting Yak Master

233 Posts

Posted - 2009-05-20 : 09:18:32

DECLARE @KSam TABLE (iden int identity, ID INT, Name VARCHAR(9))

INSERT @KSam
SELECT 3, 'Car' UNION ALL
SELECT 1, 'Lion' UNION ALL
SELECT 1, 'Rat' UNION ALL
SELECT 1, 'Mouse' UNION ALL
SELECT 2, 'Apple' UNION ALL
SELECT 2, 'Orange'

Select * from
( Select (Select Count(*) from @KSam
Where ID = Z.ID and iden <= Z.iden) as Item, ID, name
from @KSam Z) M order by name desc

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-20 : 13:56:15

[code]
SELECT TOP 10 WITH TIES *
FROM YourTable
ORDER BY field1 DESC
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources