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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.