Link to home
Start Free TrialLog in
Avatar of ronin83
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
Avatar of Brendt Hess
Brendt Hess
Flag of United States of America image

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

ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of ronin83
ronin83

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
Avatar of ronin83

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.
Avatar of ronin83

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