Re: DISTINCT and ORDER BY

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Steve Kass (skass_at_drew.edu)
Date: 08/05/04 

Date: Wed, 04 Aug 2004 23:20:41 -0400

Sunny,

  Am I correct to understand that you are executing a query *without*
ORDER BY in it, and you are getting this error?

[SQL Server]ORDER BY items must appear in the select list if SELECT
DISTINCT is specified.

It is not easy to see how that can happen, since this message should
only appear for a query that contains ORDER BY.

Are you running this query alone with no other statements? Have you
tried running the query in Query Analyzer?

Steve Kass
Drew University

Sunny wrote:

>In article <r2s2h0dh4tib6r7vfs1ur4p9qpep0aikvv@4ax.com>,
>hugo@pe_NO_rFact.in_SPAM_fo says...
>
>
>>Hi Sunny,
>>
>>In the relational model, a table is by definition an UNordered collection
>>of rows. Even using order by doesn't guarantee that the rows will be
>>inserted in the order you specify. And even if they are, you still have no
>>guarantee that they will be SELECTed in the same order.
>>
>>ORDER BY really only should be used in a SELECT statement. It specifies
>>the order that the result set has to be sorted into before displaying.
>>
>>So you can simply remove the ORDER BY clause and you'll no longer have the
>>error message. I know this doesn't explain the error, but it does solve
>>it!
>>
>>Best, Hugo
>>
>>
>>
>
>Hi Hugo,
>thanks for the replay. The problem is that I do not need order. I put it
>there, because it gave me the same error without the ORDER BY.
>
>I Just want to get a subset of one table and put it in another. Please,
>see my second post, there I have simplified the INSERT statement, and
>still it fails with the same error. I tried to remove the ORDER By, and
>still the same problem.
>
>Thanks again
>Sunny
>
>

Relevant Pages

  • Re: DISTINCT and ORDER BY
    ... a table is by definition an UNordered collection ... Even using order by doesn't guarantee that the rows will be ... > inserted in the order you specify. ... Hi Hugo, ...
    (microsoft.public.sqlserver.programming)
  • MS Access/VBA Question - Using Multiple combo Boxes to Search on a Form
    ... -I have a form with several combo boxes and several text ... a button is clicked which launches a new form (frmResults). ... frmResults is bound to a query, the criteria of which is set to the ... specify anything for a particular field. ...
    (microsoft.public.office.developer.vba)
  • Re: ASP / Access: use of same table twice in one query
    ... are in the same table and I need them both in one query. ... I agree it is better to specify the fields I need in the select statement, ... >> The data is stored in an Access database. ... > quicker response by posting to the newsgroup. ...
    (microsoft.public.inetserver.asp.db)
  • Re: Query records with single Dates Automatically
    ... both queries pull from the same table. ... -- Two Tables Attendees and Visits ... Please copy and post the SQL of your query. ... I have to find a way to specify ...
    (microsoft.public.access.queries)
  • RE: build an if statement with conditional parameters
    ... This is my modified code, no errors, but like I said, I can query with just ... Dave Hargis, Microsoft Access MVP ... "David Pelizzari" wrote: ... specify a date, and leave the site blank, and get nothing back, if I specify ...
    (microsoft.public.access.queries)