Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2004
    Posts
    139

    Unanswered: SELECT DISTINCT w/ORDER BY

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

    SELECT DISTINCT
    pdm.Account,
    pdm.Customer
    FROM
    dbo.Demands pdm LEFT OUTER JOIN
    dbo.Tickets rct ON pdm.Account = rct.Account
    WHERE
    pdm.Code IN (66, 51)
    ORDER BY
    pdm.TransactionDate DESC

    Is there any way to make the ORDER BY work in this case?
    Thanks,
    Bill

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by beeyule
    I get the "ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

    SELECT DISTINCT
    pdm.Account,
    pdm.Customer
    FROM
    dbo.Demands pdm LEFT OUTER JOIN
    dbo.Tickets rct ON pdm.Account = rct.Account
    WHERE
    pdm.Code IN (66, 51)
    ORDER BY
    pdm.TransactionDate DESC

    Is there any way to make the ORDER BY work in this case?
    I THINK this might be what you are trying to do:
    Code:
    SELECT
      t.Account,
      t.Customer,
      t.LastTransaction
    FROM
        (SELECT
          pdm.Account,
          pdm.Customer,
          Max(pdm.TransactionDate) as LastTransaction
         FROM
    	dbo.Demands pdm LEFT OUTER JOIN
                 dbo.Tickets rct ON pdm.Account = rct.Account
         WHERE     
    	pdm.Code IN (66, 51) ) t
    ORDER BY
      t.LastTransaction DESC
    Have you hugged your backup today?

Posting Permissions

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