SQL Server Performance Forum – Threads Archive

Select top N for each user

Hello! I have a query like this. select
top 10
@myusername, username, age
from
users
where
gender = ‘female’
order by
abs(@myownage-age)
This will return 10 female users ordered with the lowest age difference first. But lets say I want to run this query for all of my users, so that I would get a list of 10*N rows, where N is the number of users in my database. How can I do that? Then when I have this list, I would like to pivot so that I group by @myusername and put each user in a column instead. Any suggestions?

I also like younger women [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] SELECT A.uername, b.username,a.age, b.age FROM users A CROSS APPLY (SELECT TOP 10 uername, age FROM Users U WHERE U.gender = ‘female’ order by abs(a.age-U.age) ) B Roji. P. Thomas SQL Server MVP http://toponewithties.blogspot.com</a>

I am sorry. This message was posted in wrong forum (please move this thread). I am using SQL-Server 2000, so I cannot do CROSS APPLY. Any ideas how to do it in SQL-Server 2000?

For each male user, you want to list the 10 female users that have the closest matching age? SELECT A.uername, b.username,a.age, b.age
FROM users A, users B
WHERE A.gender = ‘male’
AND B.Username IN
(SELECT TOP 10 username FROM users C
WHERE C.username <> A.username
AND C.gender = ‘female’
ORDER BY abs(A.age – C.age)) You really need a unique key other than the user name!

Moved it here. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Moderator Microsoft SQL Server MVP Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

Ah, and don’t tell me you have a column "Age" in which you enter a person’s age – unless you’re absolutely certain that they will disappear from your database before their next birthday. The date of birth is a non-changing bit of information that you can use in exactly the same way.

Thanks for the query I tried to use the userid instead of username. But this query is slow, so now I need help to get some speed here. The table has ~100000 rows Any suggestions? What I would like most is to get the result in columns instead of rows, (can that help?) userid – my userid hit1 – the best hit for me based sorted on the one with closest age. hit2 – the second best hit for me based sorted on the one with closest age. hit10 – … … No I dont really have a age column. Just easier to explain, while we work on the problem. <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />

Post your original table structure with available indexes. Roji. P. Thomas
SQL Server MVP
http://toponewithties.blogspot.com

users
———-
userid int
username varchar(50)
sex tinyint
provinceId smallint
dateOfBirth datetime
seeksAgeFrom int
seeksAgeTo int
seeksSex tinyint
seeksProvinceId clustered index: username
unique non clustered index: userid So I would like a list of the "best" top 10 "matches" for each user, based on the "seeks…"-columns. The seeksAge and seeksSex is criteria (WHERE-clause), and then the result should be sorted with the same province first, and also sorted with the least age difference first. This query will only be run once a week.

In that case, add indexes on … -1- (Sex, SeeksSex)
-2- (SeeksAgeFrom, SeeksAgeTo)
-3- (ProvinceId)
-4- (SeeksProvinceId) The sex columns are low in selectivity when separate, but it improves if you combine the two. By the way, do you have an option for ‘both’ on SeeksSex? No point in having separate indexes on the SeeksAge… columns, as From will always be lower than To. For the provinces, it depends. And can a user select only one SeeksProvinceId?

I’ll recommend the following (covering) non-clustered indexes. 1. Sex, ProvinceID, BirthDate 2. SeeksSex, seeksProvinceId , seeksAgeFrom , seeksAgeTo
Roji. P. Thomas
SQL Server MVP
http://toponewithties.blogspot.com

]]>