troubleshooting Question

Get get Select distinct top 10 to order properly

Avatar of pauldes
pauldes asked on
Microsoft SQL Server
4 Comments1 Solution475 ViewsLast Modified:
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

ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros