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

pauldesAsked:
Who is Participating?
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.

Scott PletcherSenior DBACommented:
*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 )
LowfatspreadCommented:
use group by and max(createdate)

select top 10 a,b,max(createdate)
from ...
group by a,b
order by 3 descending

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
Scott PletcherSenior DBACommented:
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
pauldesAuthor Commented:
Thanks guys. I went with the group by and max from LFS. Seems to work....
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
Microsoft SQL Server

From novice to tech pro — start learning today.