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
ronin83Asked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
possibly this will be "better"
SELECT TOP 10 *
  FROM (
    SELECT files.*, profiles.profilepic 
      , ROW_NUMBER() OVER (PARTITION BY files.owner ORDER BY NEWID() ) 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()

Open in new window

0
 
Brendt HessSenior DBACommented:
Okay, just a touch unclear here.  When you say:

    "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?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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()

Open in new window

0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
ronin83Author Commented:
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
0
 
ronin83Author Commented:
@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
0
 
Brendt HessSenior DBACommented:
angelIII's query should do what you need.
0
 
ronin83Author Commented:
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
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
0
 
SharathData EngineerCommented:
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

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.