TOP 3 - 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 2000 Forums

Transact-SQL (2000)

TOP 3

Author

Topic

smemis
Starting Member

1 Post

Posted - 2002-02-28 : 03:51:00

My database table is like this

USERS
-----
id-username-hit
1-ahmet-10
2-tuncay-2
3-fatih-3
4-semih-3
5-ali-8
6-veli-9
7-orhan-7
8-mustafa-7
9-sibel-8
10-özlem-8

my sql string is like this
"select top 3 * from USERS order by hit desc"

and returning recordset is "ahmet, veli, ali, sibel, özlem"
but i want to "ahmet, veli, ali"

Please help me, how can i do it?

Note: I dont speak English very well. I'm sorry.

Thanks for your helps.

nr
SQLTeam MVY

12543 Posts

Posted - 2002-02-28 : 04:07:07

Are you sure that is your query?
it should return 3 rows.
Post it with a populated temp table and the result.

try also
set rowcount 3
select top 3 * from USERS order by hit desc
set rowcount 0

Have you something more like

select * from users where hit in (select top 3 hit from users order by hit desc)

select top 3 * from USERS order by hit desc, id
or
select top 3 * from USERS order by hit desc, username

depending on the ordering for ties that you want.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Edited by - nr on 02/28/2002 04:09:38

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-02-28 : 19:51:31

Or, another option nr doesn't specifically mention that could give you the results you are seeing is if you have the WITH TIES option included in your SELECT TOP... statement. Make sure that is not there, and then as nr says, add an additional field to your order by which will determine which one of the three tied users will be returned and which will be left out.

------------------------
GENERAL-ly speaking...

joldham
Wiseass Yak Posting Master

300 Posts

Posted - 2002-03-01 : 08:26:43

Not sure if this would work, but what if you added another criteria to your ORDER statement like ORDER BY hit desc, username

Let me know if this works.

Jeremy

** Sorry guys. I just noticed nr suggested the same query.

Edited by - joldham on 03/01/2002 08:29:14

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources