I have a table student and I want to sort three columns in it: first_name, last_name, street_address. I'm expecting a result like this:
| first_name | last_name | street_address |
A A 100 Carroll St
B B 200 Carroll St
C C 300 Carroll St
This is my SQL query:
SELECT first_name, last_name, street_address
FROM student
ORDER BY first_name, last_name, street_address;
But this query sorts like this:
| first_name | last_name | street_address |
A C (not sorted) 300 Carroll St (not sorted)
B B (not sorted) 100 Carroll St (not sorted)
C A (not sorted) 200 Carroll St (not sorted)
This query doesn't work as supposed to. It doesn't sort all columns in ascending order, it only sorts the first column which appears after ORDER BY. As mentioned above, the query only sorts first_name column. If I change the places of columns after ORDER BY, it only sorts first mentioned column after "ORDER_BY".
I have searched Google and many forums and couldn't find anything related to this problem.
Oracle version:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0
PL/SQL Release 12.2.0.1.0 - Production 0
CORE 12.2.0.1.0 Production 0
TNS for 64-bit Windows: Version 12.2.0.1.0 - Production 0
NLSRTL Version 12.2.0.1.0 - Production 0
I will appreciate your help.
Here is some sample input data, which I hope will make the following explanation clearer:
id | first_name | last_name | street_address
10 | Albert | Camus | 300 Carroll St
20 | Caroline | Aherne | 200 Carroll St
30 | Bertoldt | Brecht | 500 Carroll St
40 | Albert | Dumbledore | 400 Carroll St
50 | Bertoldt | Brecht | 100 Carroll St
Here we have five records, which tell us useful things, like Albert Camus lives at 300 Carroll Street and Caroline Ahern lives at 200 Carroll St. The important thing to understand about a SQL query is that it retrieves records; when we use ORDER BY it sorts the records, not the individual columns.
So when we sort these records ordering by first_name, last_name, street_address we get this result:
id | first_name | last_name | street_address
10 | Albert | Camus | 300 Carroll St
40 | Albert | Dumbledore | 400 Carroll St
50 | Bertoldt | Brecht | 100 Carroll St
30 | Bertoldt | Brecht | 500 Carroll St
20 | Caroline | Aherne | 200 Carroll St
The result set is the table's records sorted into first_name order; when two records have the same value first_name the sort uses last_name to break the tie; when both first_name and last_name are the same the sort uses street_address. This is exactly what we would expect. The records remain intact.
What would it mean to sort the columns independently? There is no such person as Albert Aherne living at 100 Carroll Street. So how would SQL make one up?
If you still quite can't get the way this works, notice that I added a surrogate primary key to the table. The ID column uniquely identifies each record. So ID = 30 identifies the Bertoldt Brecht who lives at 500 Carroll St. Supposing ORDER BY worked the way you expect: what would the value of ID be for Albert Aherne living at 100 Carroll Street?
According to the normalization forms, in a table, all records must be related to the primary key and it means you can't sort multiple columns then why we need sorting multiple columns?
Normalisation has nothing to do with it. We might want to order by columns of a compound key or even by non-key attributes. In such circumstances we often need to order by multiple columns.
For instance, I shuffle a deck of cards. Now I ask you to take five cards and put them in ascending order, aces low. Easy enough. Except that you drew the seven of spades and the seven of hearts. Which comes first? Spades is higher than hearts, so seven of hearts then seven of spades. But both before nine of clubs.