Avatar of pauldes
pauldes
 asked on

Get get Select distinct top 10 to order properly

I need a list ordered by tableB.creatdate descending but I can't add tableb.createdate to the query as I am trying to use DISTINCT in the results but have it apply to tableA.filename and tableB.Description fields only. Order by only lets you use the tableB.createdate field(in this example) if it is in the select statement

If I do this I am not returned results by the last 10 for tableb.createdate

Select DISTINCT TOP 10 tablea.filename, tableb.description
from table b join tablea on tableb.fileid = tablea.fileid

If I do this, then the filename/description combination is no longer distinct because createdate is now part of the DISTINCT evaluation:
Select DISTINCT TOP 10 tablea.filename, tableb.description, tableb.createdate
from table b join tablea on tableb.fileid = tablea.fileid
order by tableb.createdate

Ideas?? Thanks

Microsoft SQL Server

Avatar of undefined
Last Comment
pauldes

8/22/2022 - Mon
Scott Pletcher

*Maybe* this will do it.  The "trick" is whether or not SQL will properly maintain the sequence of the rows in the derived table.  
Performance will likely *not* be good, especially if tableb is large and/or does not an index on createdate.


SELECT tablea.fileid, tablea.filename, tableb.description
FROM tablea
INNER JOIN tableb ON tablea.fileid = tableb.fileid
WHERE tablea.fileid IN (
    SELECT DISTINCT TOP 10 fileid
    FROM (
        SELECT TOP 100 PERCENT createdate, fileid
        FROM tableb tableb2
        WHERE tableb2.fileid = tablea.fileid
        ORDER BY createdate DESC, fileid
    ) AS recentB )
ASKER CERTIFIED SOLUTION
Lowfatspread

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Scott Pletcher

D'OH, knew I was overlooking something:

SELECT tablea.fileid, tablea.filename, tableb.description
FROM tablea
INNER JOIN tableb ON tablea.fileid = tableb.fileid
INNER JOIN (
    SELECT TOP 10 fileid, MAX(createdate) AS maxCreatedate
    FROM tableb
    GROUP BY fileid
    ORDER BY maxCreatedate DESC
) AS recentB ON recentB.fileid = tablea.fileid
pauldes

ASKER
Thanks guys. I went with the group by and max from LFS. Seems to work....
Your help has saved me hundreds of hours of internet surfing.
fblack61