CODEWITHSUNDEEP
mysql
Punchlinern
Punchlinern

Reputation: 754

MySQL - ORDER BY one column if duplicate or reference doesn't exist in another

I have a table that (simplified) looks like this:

+----+--------------+-----------+----------------+----------------+--------+---------+
| ID | First name   | Last name | Street Address | Postal Address | Country | Spouse |
+----+--------------+-----------+----------------+----------------+---------+--------+
| 1  | Nancy        | Fuller    | Street 1       | City 1         | USA     | 4      |
| 2  | Andrew       | Davolio   | Way 2          | Town 2         | USA     | 0      |
| 3  | Janet        | Leverling | Blvd 3         | Village 3      | USA     | 0      |
| 4  | Steven       | Buchanan  | Street 1       | City 1         | USA     | 1      |
| 5  | Anne         | Dodsworth | Street 1       | City 1         | USA     | 0      |
+----+--------------+-----------+----------------+----------------+---------+--------+

As you can see, Nancy and Steven are married (the spouse column contains the ID of spouse, if any) and live together. Anne, Nancy's sister, also live with them (don't bother commenting the inconvenience).

Now I want to print this database. I want to order by last name, BUT on the print page I have two options:

So there are four scenarios:

No grouping
They are simply ordered by last name:

Buchanan, Steven

Davolio, Andrew

Dodsworth, Anne

Fuller, Nancy

Leverling, Janet

Grouped by address When the first person of several with the same address is reached, all of them are printed (still sorted by last name in the group):

Buchanan, Steven
Dodsworth, Anne
Fuller, Nancy

Davolio, Andrew

Leverling, Janet

Grouped by spouse When the first person in a couple is reached, both are printed:

Buchanan, Steven
Fuller, Nancy

Dodsworth, Anne

Davolio, Andrew

Leverling, Janet

Grouped by address and spouse Combination of the above, spouse is prioritized (so Nancy appears before Anne since she's Steve's spouse).

Buchanan, Steven
Fuller, Nancy
Dodsworth, Anne

Davolio, Andrew

Leverling, Janet

And I really want them grouped, if that is possible, not just ordered after each other. As shown above I want space between not grouped persons, and no space between grouped persons. Is this possible using only MySQL, or do I have to use PHP as well?

(I'm using PHP 5.5.16 and MySQL 5.5.39, PDO Objects)

Upvotes: 0

Views: 86

Answers (1)

John Ruddell
John Ruddell

Reputation: 25862

in this query I put the cast statements in the select part so that you can see how the data is getting laid out. you can migrate them to the ORDER BY if you want and only pull out the firstname, lastname.

CASE: ordered by address and spouse

SELECT DISTINCT 
    CASE WHEN p1.id IS NOT NULL THEN 1 ELSE 0 END, 
    CASE WHEN p2.id IS NOT NULL THEN 1 ELSE 0 END,
    CONCAT(p.lastname, ', ', p.firstname)
FROM people p
LEFT JOIN people p1 ON p1.spouse = p.id
LEFT JOIN people p2 ON p2.streetaddress = p.streetaddress 
                   AND p.postaladdress = p2.postaladdress 
                   AND p2.id <> p.id
ORDER BY 1 DESC, 2 DESC, 3 ASC;

CASE: ordered by address

SELECT DISTINCT 
    CASE WHEN p2.id IS NOT NULL THEN 1 ELSE 0 END, 
    CONCAT(p.lastname, ', ', p.firstname)
FROM people p
LEFT JOIN people p2 
    ON p2.streetaddress = p.streetaddress 
   AND p.postaladdress = p2.postaladdress 
   AND p2.id <> p.id
ORDER BY 1 DESC, 2 ASC;

CASE: ordered by spouse

SELECT DISTINCT 
    CASE WHEN p1.id IS NOT NULL THEN 1 ELSE 0 END, 
    CONCAT(p.lastname, ', ', p.firstname)
FROM people p
LEFT JOIN people p1 ON p1.spouse = p.id
ORDER BY 1 DESC, 2 ASC;

CASE: ordered by nothing

SELECT
    CONCAT(p.lastname, ', ', p.firstname)
FROM people p;

Fiddle Demo

NOTE: if you keep the select as is with ordering then you can easily add spaces in the application layer... for instance if it pulls out a 1 then that means that the order is true for a spouse, address etc. if its a 0 then its false. so you can check for the value and if its a 0 start adding spaces

EDIT:

to do an order by name ascending but to override it with another name that is a spouse or same address is not possible in MySQL. you can use the CASE statement to check in php if there is a spouse or not and change the ordering there.

Upvotes: 2

Related Questions