Link to home
Start Free TrialLog in
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

Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

*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
Avatar of Lowfatspread
Lowfatspread
Flag of United Kingdom of Great Britain and Northern Ireland 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
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
Avatar of pauldes
pauldes

ASKER

Thanks guys. I went with the group by and max from LFS. Seems to work....