ronin83
asked on
how to SELECT TOP 5 rows with ONE DISCTINCT column?
SELECT TOP(10) files.*, profiles.profilepic FROM files INNER JOIN profiles ON files.owner = profiles.username WHERE (files.title IS NOT NULL) AND (files.status IS NULL) AND (files.description IS NOT NULL) AND (files.filetype = 'audio') ORDER BY NEWID()
I'm using the above code to present a random set of files to a user browsing his uploaded content. the thing is i want to only have results with DISTINCT (or unique) "files.owner" column and i have no idea how to accomplish this
i'm working with MSSQL2005
thanks for any help
I'm using the above code to present a random set of files to a user browsing his uploaded content. the thing is i want to only have results with DISTINCT (or unique) "files.owner" column and i have no idea how to accomplish this
i'm working with MSSQL2005
thanks for any help
this should do:
SELECT TOP 10 *
FROM (
SELECT files.*, profiles.profilepic
, ROW_NUMBER() OVER (PARTITION BY files.owner ORDER BY files.owner ) rn
FROM files INNER JOIN profiles ON files.owner = profiles.username
WHERE (files.title IS NOT NULL) AND (files.status IS NULL) AND (files.description IS NOT NULL) AND (files.filetype = 'audio')
) sq
WHERE sq.rn = 1
ORDER BY NEWID()
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
i apologize, basically i'm looking to do this
table = files, cols = owner, title, status, description, filetype, few others that aren't relevant for this
lets say i have in the table (i'm only not filling out title/descrip cus its not relevant here and i'm lazy)
row1: jason, title1, , , audio
row2: sam, , , , audio
row3: megan, , , , audio
row4: jason, title2, , , , audio
row5: laurie, , , , audio
row6: amy, , , , audio
in my results i only want, lets say the a random sampling of 3 of them, and i want to not get 2 results from jason, so that people don't get their stuff seen twice or more when other's only get one or none
so i'm looking for results
amy, jason, laurie (or) mega, laurie, amy
i DONT want
amy, jason, jason (or) jason, sam, jason
sorry if i wasn't clear
table = files, cols = owner, title, status, description, filetype, few others that aren't relevant for this
lets say i have in the table (i'm only not filling out title/descrip cus its not relevant here and i'm lazy)
row1: jason, title1, , , audio
row2: sam, , , , audio
row3: megan, , , , audio
row4: jason, title2, , , , audio
row5: laurie, , , , audio
row6: amy, , , , audio
in my results i only want, lets say the a random sampling of 3 of them, and i want to not get 2 results from jason, so that people don't get their stuff seen twice or more when other's only get one or none
so i'm looking for results
amy, jason, laurie (or) mega, laurie, amy
i DONT want
amy, jason, jason (or) jason, sam, jason
sorry if i wasn't clear
ASKER
@angel your select looks way over my head complicated but i'm going to give it a shot, will report back asap with how i do
angelIII's query should do what you need.
ASKER
works perfectly thank you so much angel for your help
thank you bhess as well for what i assume would've been help :)
if either of you are bored and feel like explaining how this thing does what it does (so i might learn and replicate it in the future) feel free :) otherwise i'll just trial and error learn it
thank you bhess as well for what i assume would've been help :)
if either of you are bored and feel like explaining how this thing does what it does (so i might learn and replicate it in the future) feel free :) otherwise i'll just trial and error learn it
remove:
WHERE sq.rn = 1
ORDER BY NEWID()
and instead write:
ORDER BY owner, rn
and you "see" what the ROW_NUMBER function is doing
WHERE sq.rn = 1
ORDER BY NEWID()
and instead write:
ORDER BY owner, rn
and you "see" what the ROW_NUMBER function is doing
try this
SELECT *
FROM (SELECT *,
ROW_NUMBER()
OVER(PARTITION BY [owner] ORDER BY CHECKSUM(NEWID())) rn
FROM files) t1
JOIN (SELECT TOP 10 [owner]
FROM (SELECT DISTINCT [owner]
FROM files) t3
ORDER BY CHECKSUM(NEWID())) t2
ON t1.[owner] = t2.[owner]
JOIN profiles p
ON t1.[owner] = p.username
WHERE (t1.title IS NOT NULL)
AND (t1.[status] IS NULL)
AND (t1.[description] IS NOT NULL)
AND (t1.filetype = 'audio')
AND rn < 6
"the thing is i want to only have results with DISTINCT (or unique) "files.owner" column"
Do you mean (1) I want to only have results with a specified files.owner column, or (2) I want to only have results where each files.owner value shows up no more than once, or (3) I want to have only files from a randomly selected files.owner value?