CODEWITHSUNDEEP
sqlsql-serversql-server-2008ms-access
mwolfe02
mwolfe02

Reputation: 24227

ORDER BY items must appear... but I have no ORDER BY clause

I am getting the well-documented error:

ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

Here's my SQL statement:

INSERT INTO PropertyList
            (PropertyID, Initials)
SELECT DISTINCT P.PropertyID, "vSTEBCodes"
FROM Property AS P INNER JOIN ValiditySTEBCodes AS VC 
              ON P.ControlNumber=VC.ControlNumber

As you can see there is no ORDER BY statement in sight. Presumably there is an implied ORDER BY in there somewhere, but I can't see it.

I am running this statement against linked tables in MS Access that are linked to a SQL Server 2008 back-end. When I run it from a full version of Access 2002, it runs without issue. If I try to run it from an Access 2000 runtime I receive the error shown.

I know that Access has a tendency to re-write queries in the background before forwarding them to ODBC sources (especially multi-record INSERTs and UPDATEs). I'm thinking that may be the issue.

Upvotes: 0

Views: 240

Answers (1)

anon
anon

Reputation:

Compiling from the comments, try:

INSERT INTO dbo.PropertyList(PropertyID, Initials)
  SELECT P.PropertyID, Initials = 'vSTEBCodes' -- alias and single quotes
  FROM dbo.Property AS P -- always use schema prefix
  INNER JOIN dbo.ValiditySTEBCodes AS VC 
  ON P.ControlNumber = VC.ControlNumber
  GROUP BY P.PropertyID  -- instead of DISTINCT
  ORDER BY P.PropertyID; -- maybe overrides Access stupidity

Upvotes: 1

Related Questions