Results 1 to 3 of 3

Thread: Order By Case

  1. #1
    Join Date
    Oct 2002
    Posts
    9

    Question Unanswered: Order By Case

    I would like to order a select by more then one criteria depending on the data retrieved. If I am not mistaken I need to use a case statement but am having some trouble with it.
    (fictional select but you get the idea) In this case I would like the transactions with a status of ‘C’ and amount grater then 1000 to be listed first and alphabetically by last name. If the transaction status is ‘C’ but the amount is less then 1000, I would like these listed below the first group alphabetically by last name.
    This is what I think that I need…will this work?

    SELECT
    a.first_name, a.last_name, t.amount
    FROM
    address a, transaction t
    WHERE
    t.status=’C’
    ORDER BY
    CASE
    WHEN t.amount > 1000
    THEN t.amount, a.last_name
    ELSE a.last_name
    END


    I am getting “Inconsistent Data Type” after the ELSE expression is read???

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Order By Case

    Try this:

    SELECT
    a.first_name, a.last_name, t.amount
    FROM
    address a, transaction t
    WHERE
    t.status=’C’
    ORDER BY
    CASE
    WHEN t.amount > 1000
    THEN 1
    ELSE 2
    END,
    a.last_name

  3. #3
    Join Date
    Oct 2002
    Posts
    9

    Talking Re: Order By Case

    That was it. 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
  •