Re: Query doesn't always sort

Gary,
Seeking enlightenment

I've never had to use TOP 100% in MS SQL 2000 queries in order to use an
ORDER BY clause or when using ODBC to linked MS SQL tables.

Would you mind expanding on on your statement
"... with "TOP 100%" which is necessary for ORDER BY in SQL2K"?

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Gary Walter" <gary@xxxxxxxxxxx> wrote in message
news:u5hDzKriHHA.4036@xxxxxxxxxxxxxxxxxxxxxxx
Hi Sam,

Pardon me for jumping in...

I wonder if JensB is maybe thinking of SQL Server?

I don't believe the query is from SQL2K since
it does not start with "TOP 100%" which is
necessary for ORDER BY in SQL2K
(I don't work w/ newer version of SQL Server
so don't know if that has changed).

With Jet queries, you cannot use alias in
ORDER BY clause.

ORDER BY tangents:

-- In a UNION query, you can set an alias
in the first SELECT, then at the end of all
SELECTs, you do order by that alias.

-- Your ORDER BY can use the ordinal position
of the field in the SELECT query.

SELECT f1, f2
FROM tbl
ORDER BY 2;

would sort by f2.

I defer to both John's posts (they know much
more than I), but wonder about 2 things:

1) Why is

P.PRODUCT_LOGICAL_ORDER

in the ORDER BY
but not in the SELECT clause?

Would it hurt to put it in the SELECT clause
and test for consistency?

Or just leave it out of ORDER BY and test?

2) Why use a make table?

Have you tried emptying the table in code,
then appending new data, where your append
query also includes field mentioned above in
SELECT clause (so also in empty table) ?

Good luck,

gary

"JensB" wrote
Yes
The original fieldname does not exist in your result.
JensB
<sam.alame@xxxxxxxxx> wrote in message
news:1177692179.224412.65400@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
JensB, thanks for your response. So all I need to do is order by the
alias as opposed to the original field name?

On Apr 27, 10:37 am, "JensB" <J...@xxxxxxxxxxxxxxxx> wrote:
Try to use the alias for P.TERR_CODE_IND like this:
ORDER BY P.GEOSORT, TERR_CODE, P.GEO_CODE, P.GEO_DESC,
P.PRODUCT_LOGICAL_ORDER

JensB<sam.al...@xxxxxxxxx> wrote in message

news:1177676065.778413.252420@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx



Hello

I have an access query that I run on a regular basis but that doesn't
always sort. It sorts most of the time, but once in a while it won't,
which makes it really annoying and impractical.

Does anybody know why this is happening?

Thank you.

The query is:

SELECT P.GEOSORT, P.TERR_CODE_IND AS TERR_CODE, P.GEO_CODE,
P.GEO_DESC, P.PRODUCT_SHORT_NAME, P.M23VA, P.M22VA, P.M21VA, P.M20VA,
P.M19VA, P.M18VA, P.M17VA, P.M16VA, P.M15VA, P.M14VA, P.M13VA,
P.M12VA, P.M11VA, P.M10VA, P.M9VA, P.M8VA, P.M7VA, P.M6VA, P.M5VA,
P.M4VA, P.M3VA, P.M2VA, P.M1VA, P.CMTHVA, P.M23VC, P.M22VC, P.M21VC,
P.M20VC, P.M19VC, P.M18VC, P.M17VC, P.M16VC, P.M15VC, P.M14VC,
P.M13VC, P.M12VC, P.M11VC, P.M10VC, P.M9VC, P.M8VC, P.M7VC, P.M6VC,
P.M5VC, P.M4VC, P.M3VC, P.M2VC, P.M1VC, P.CMTHVC, P.M23VSAA,
P.M22VSAA, P.M21VSAA, P.M20VSAA, P.M19VSAA, P.M18VSAA, P.M17VSAA,
P.M16VSAA, P.M15VSAA, P.M14VSAA, P.M13VSAA, P.M12VSAA, P.M11VSAA,
P.M10VSAA, P.M9VSAA, P.M8VSAA, P.M7VSAA, P.M6VSAA, P.M5VSAA,
P.M4VSAA,
P.M3VSAA, P.M2VSAA, P.M1VSAA, P.CMTHVSAA, P.M23VSAC, P.M22VSAC,
P.M21VSAC, P.M20VSAC, P.M19VSAC, P.M18VSAC, P.M17VSAC, P.M16VSAC,
P.M15VSAC, P.M14VSAC, P.M13VSAC, P.M12VSAC, P.M11VSAC, P.M10VSAC,
P.M9VSAC, P.M8VSAC, P.M7VSAC, P.M6VSAC, P.M5VSAC, P.M4VSAC, P.M3VSAC,
P.M2VSAC, P.M1VSAC, P.CMTHVSAC INTO SORTED_P
FROM P
ORDER BY P.GEOSORT, P.TERR_CODE_IND, P.GEO_CODE, P.GEO_DESC,
P.PRODUCT_LOGICAL_ORDER;- Hide quoted text -

- Show quoted text -







.

Relevant Pages

  • Re: export form filtered data to excel
    ... ' There's no ORDER BY in the SQL. ... ' Remove the semi-colon from the end, then append the WHERE clause ... Or, better yet, look at the actual SQL for your query and see what's ... Dim qdfTemp As DAO.QueryDef ...
    (microsoft.public.access.formscoding)
  • Re: Query to Count Duplicate Values in a Given Date Range
    ... Show" to note whether the client showed up at the pantry. ... Now, if I try to run the query with that field, it returns no results. ... Here's my final SQL code if you see anything else that may need adjusting. ... use a WHERE clause rather than HAVING for the dates, ...
    (microsoft.public.access.queries)
  • Re: Error: Specified field could refer to more than one table
    ... WeeklyMileage) in SELECT Clause my guess is that the reference exists ... Run the query and select Remove Filter/sort from the menu and then save the ... Copy the SQL text into a blank query and try switching into design view ... FROM clause of your SQL statement. ...
    (microsoft.public.access.queries)
  • Re: Error: Specified field could refer to more than one table
    ... WeeklyMileage) in SELECT Clause my guess is that the reference exists ... Run the query and select Remove Filter/sort from the menu and then save the ... Copy the SQL text into a blank query and try switching into design view ... FROM clause of your SQL statement. ...
    (microsoft.public.access.queries)
  • Trying to find a maximum value amongst 3 columns in a dB row
    ... The column prefix 'a1' does not match with a table name or alias name ... used in the query. ... Either the table is not specified in the FROM clause ...
    (comp.databases.sybase)
Loading