How to sort table names only if they have the same value in a numeric field?

itsLOKO 0
Viewed 26

I have made the following table with SQL:

CREATE TABLE facts (
    id          INTEGER PRIMARY KEY,
    dates       DATE DEFAULT CURRENT_DATE NOT NULL,
    amount      NUMERIC( 7, 2) NOT NULL,
    client      CHAR(40) NOT NULL
);

INSERT INTO facts VALUES    ( 1, DATE '10-9-2017', 500, 'Mark'),
                ( 2, DATE '11-9-2017', 170, 'Joseph'),
                ( 3, DATE '20-9-2017', 500, 'Louis'),
                ( 4, DATE '25-9-2017', 30, 'Joseph'),
                ( 5, DATE '25-10-2017', 40, 'Anne');

I am trying to get through a select the names of the rows that have a date prior to 9-15-2017 or their amount is greater than 400. To do this, I use the following:

SELECT client 
FROM facts 
WHERE dates<'15-9-2017' 
   OR amount>400 
ORDER BY amount DESC;

My problem is that there are two equal amounts and I want to sort the names ascendingly only of the ones with the equal amount, so what I want to get at the end is: "Louis, Mark, Joseph" in that order and I don't know how to use the ORDER BY to sort only the ones with the same amount.

sql postgresql
1 Answers

That's exactly how an ORDER BY clause with two expressions is sorted:

ORDER BY amount DESC, client
Laurenz Albe0
Related