Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2005
    Posts
    4

    Question Unanswered: Selecting from subquery loses order

    example:
    Select DISTINCT [PS Number]
    from
    (
    Select [PS Number],caldate from Equip
    where Company_ID=267
    group by [PS Number],caldate
    order by caldate DESC
    )

    The subquery produces the correct order albeit with some duplicate [PS Number] rows.

    So doing the Select DISTINCT on the subquery gets me the distinct values but they are not in the same order as they were. Finally, I just want the result set to be the [PS Number] but in caldate order DESC

    Any help is much appreciated.

  2. #2
    Join Date
    May 2008
    Location
    Raleigh, NC
    Posts
    151

    Maybe

    Quote Originally Posted by wasouthpnt
    example:
    Select DISTINCT [PS Number]
    from
    (
    Select [PS Number],caldate from Equip
    where Company_ID=267
    group by [PS Number],caldate
    order by caldate DESC
    )

    The subquery produces the correct order albeit with some duplicate [PS Number] rows.
    I'm a little rusty but would adding

    order by [PS Number]

    after the closing paren of the subselect?
    --If its free, take it for what its worth!

  3. #3
    Join Date
    Apr 2005
    Posts
    4

    Selecting from subquery loses order

    Thanks for your reply.
    Actually no. Your suggestions would order by [PS Number] which is the wrong order. The order desired is by caldate, which is correct in the subquery.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT [PS Number]
         , MAX(caldate) AS maxdate
      FROM Equip
     WHERE Company_ID=267
    GROUP
        BY [PS Number]
    ORDER
        BY maxdate DESC
    if it won't let you use maxdate in the ORDER BY, use MAX(caldate) instead
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2005
    Posts
    4

    Unhappy Selecting from subquery loses order

    Thanks for your effort. This is a complex problem, and now I believe there is not a simple solution like you suggest.

    Remember, the desired result set is JUST the [PS Number]. That is why the original example did a select DISTINCT on the subquery. The result set is used to fill a combo box with [PS Numbers] only.

    The answer lies in keeping the order that the subquery creates.

  6. #6
    Join Date
    Apr 2005
    Posts
    4

    Thumbs up Select from subquery loses order, problem solved

    My Bad, you were correct, I just removed the ", MAX(caldate) AS maxdate"
    and added the "ORDER BY max(caldate)" as you suggested. The whole subquery is unnecessary thanks to you.

    The final MS Access query is shown below:

    PN.RowSource = "SELECT [PS Number] " & _
    "FROM Equip, [Company File] " & _
    "WHERE [PS Number] is not null " & _
    " AND Equip.Company_ID=[Company File].ID AND [Company File].City = '" & cmbSystemCity.Value & "' " & _
    "Group BY [PS Number] " & _
    "ORDER BY max(caldate) DESC;"

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Rudy is almost always correct! ^^
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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