Using DISTINCT and TOP?

Is it possible to use DISTINCT and TOP in a SELECT statement?

I’ve tried it a few ways and am getting an error.

Right now I am using MS Access but it should also be compatible with SQL Server.

:shifty:

With SQL you can do it with sub queries (or common table expressions in 2005).

That said, 95% of the time one uses DISTINCT, it indicates a deeper seated data modeling issue.

yes, you can use both TOP and DISTINCT in both ms access and ms sql server

what was the query? what was the error?

SELECT TOP 100 DISTINCT clients.clientID,clientName,address,address2,city,stateAbbreviation,zip,cusType,displayType,category,sales 
FROM ((((clients INNER JOIN associations ON clients.clientID = associations.clientID) 
INNER JOIN directMail ON clients.clientID = directMail.clientID)) LEFT JOIN groupings  
ON (clients.clientID = groupings.clientID AND (groupID = 1))) 
WHERE ((groupID = 1)) ORDER BY clients.clientName

Microsoft JET Database Engine error '80040e14'

Syntax error (missing operator) in query expression 'DISTINCT clients.clientID'.

/search.asp, line 617 

the correct syntax is DISTINCT TOP N, not TOP N DISTINCT

which table is groupID in?

Thank you. :slight_smile: :blush: I was sure that I tried that but I probably just put the same mistake in over and over. :sigh: :stuck_out_tongue:

groupings. Should I add the table name to the ON and WHERE clauses even though they are unique names?

if your query contains more than one table, it is “best practice” to qualify every column with its table name or table alias

you have groupID in the WHERE clause, and yet groupings is the right table in a LEFT OUTER JOIN, which means the LEFT OUTER JOIN will never return unmatched rows, and should probably be written as an INNER JOIN

I will take this advice. :slight_smile:

This works fine if I have only one search item in the WHERE clause or if I have more using AND but not with OR.