This site was going to originally be about Linux and PHP programming, but I thought I’d through this ASP/MSAccess problem in the mixer since it might benefit someone. I haven’t worked with Microsoft Access databases since my early days with ColdFusion. Just recently I had to work on a traditional ASP site using a MSAccess backend, and came across this problem with DISTINCT keyword and the ORDER BY clause. The error is “ORDER BY clause conflicts with DISTINCT”. After doing some research I discovered that I the ORDER BY column needs to be within the SELECT for it to work. Unfortunately, I was ordering by ‘date’, and that is unique with every record, so in turn, I would receive all records in the table.

This site was going to originally be about Linux and PHP programming, but I thought I’d through this ASP/MSAccess problem in the mixer since it might benefit someone. I haven’t worked with Microsoft Access databases since my early days with ColdFusion. Just recently I had to work on a traditional ASP site using a MSAccess backend, and came across this problem with DISTINCT keyword and the ORDER BY clause. The error is “ORDER BY clause conflicts with DISTINCT”. After doing some research I discovered that I the ORDER BY column needs to be within the SELECT for it to work. Unfortunately, I was ordering by ‘date’, and that is unique with every record, so in turn, I would receive all records in the table.

Here is my scenario. I have table one called ‘cities’ and table two called ‘stories’. Stories is joined to cities based on CityID. I need to have a listing of DISTINCT cities but with the latest story only showing per city

sql = "SELECT DISTINCT(cities.CityValue) , COUNT(stories.ID) AS CountVal " & _
"FROM cities LEFT OUTER JOIN stories " & _
"ON cities.CityID = stories.CityID " & _
"GROUP BY cities.CityValue, stories.ID " & _
"ORDER BY stories.pubdate "

The above sql returned the error I mentioned stating that ‘pubdate’ conflicts with the DISTINCT statement. What I needed to do was work on my GROUP BY statement and MAX().

sql = "SELECT cities.CityValue, COUNT(stories.ID) AS CountVal, MAX(stories.datepub) " & _
"FROM cities INNER JOIN stories " & _
"ON cities.CityID = stories.CityID " & _
"GROUP BY cities.CityValue " & _
"ORDER BY MAX(stories.datepub) DESC"

I’m not a big fan of Access, but I was just happy to get this working.