Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2001
    Location
    Bay Area, CA
    Posts
    117

    Unanswered: Union Query with Top N SQL

    I have a Union query with 5 SQL statements. All of them are collecting the Top 1 dates. The problem is that only the first statement actually returns the Top 1 Date, the others do not return the Top 1 date. When I run the SQL parts by themselves, they all work.

    Your help is appreciated.

  2. #2
    Join Date
    Jan 2003
    Location
    Rockville, MD
    Posts
    179
    hmmmmmmmmmmm....

    Are you separating the select statements with "UNION ALL"?

  3. #3
    Join Date
    Oct 2001
    Location
    Bay Area, CA
    Posts
    117
    No, here is a simplified version of the SQL,

    SELECT TOP 1 "Last Marketing Incident" AS ORGANIZATION, AIMP_AIM_FORM.DATE_OF_RECORD, AIMP_AIM_FORM.AIM_CONTROL_NO,
    CInt(Now()-[DATE_OF_RECORD]) AS IFD
    FROM AIMP_AIM_FORM
    ORDER BY AIMP_AIM_FORM.DATE_OF_RECORD DESC

    UNION

    SELECT TOP 1 "Last Supply & Operations Incident", AIMP_AIM_FORM.DATE_OF_RECORD, AIMP_AIM_FORM.AIM_CONTROL_NO,
    CInt(Now()-[DATE_OF_RECORD]) AS IFD
    FROM AIMP_AIM_FORM
    ORDER BY AIMP_AIM_FORM.DATE_OF_RECORD DESC

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    a query can have only one ORDER BY clause

    i'm surprised your query even ran, because you've got several ORDER BY clauses

    in the query you posted, the two selects would return exactly the same row if it were not for those literals -- what's the purpose of that? how can the same incident be both the last marketing and last supply & operations incident? musta been a typo

    there may be other ways to get what you want, if you're basically going after the same rows with the 5 different selects

    but if you have to use TOP 1, try saving each of the selects as a separate query, then run this --

    select * from query1
    union
    select * from query2


    rudy

  5. #5
    Join Date
    Jan 2003
    Posts
    46

    solution

    Torque

    I think you will need to approach this with an additional step (and a bit more work).

    Create a named query for each of your TOP 1 SQL statements. Then create a further SQL statement from each of the named queries and incorporate these into the UNION query.

    Access's version of SQL is perhaps not as robust as it could be (or we would like) and it is generally advantageous to break down large and complex queries into smaller, simpler components.

    Cheers
    zambezibill

  6. #6
    Join Date
    Oct 2001
    Location
    Bay Area, CA
    Posts
    117
    Thanks everybody for your help. My example was just a thinner version of the actual SQL statement, so it is actuall pulling together different data sets. We wil glue 5 queries together as you suggested.

    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •