CODEWITHSUNDEEP
mysqlsql-order-by
Krishan Aggarwal
Krishan Aggarwal

Reputation: 75

How to implement mysql order by in following situation

There are 2 tables - Person(personName, areaId, birthDate) and Area(areaId, areaCode). Now I want a list of persons with their areaCode in a manner such that the all the persons from an areaCode are listed together and the areaCode with the oldest person should be first areaCode to appear. How do I achieve this through mysql query?

I have tried using ORDER BY with multiple columns in vain.

SELECT P.personName, A.areaCode, P.birthDate
FROM Person P
JOIN Area A ON P.areaId = A.areaId
ORDER BY P.birthDate, A.areaCode

Suppose there are Area1 and Area2. Area2 has the oldest and newest born person. Therefore, Area2 records should appear first.

Update: I managed to solve this, but is there any way I can shorten this code.

SELECT * 
FROM (SELECT DISTINCT
    A.areaCode,
    MIN(P.birthDate)
FROM Area A
JOIN Person P on P.areaId = A.areaId
GROUP BY A.areaCode
ORDER BY P.birthDate) T1
JOIN (SELECT DISTINCT
    P.personName,
    A.areaCode,
    P.birthDate
FROM Area A
JOIN Person P on P.areaId = A.areaId
ORDER BY P.createdTimestamp) T2 ON T1.barcode = T2.binId;

Upvotes: 2

Views: 20

Answers (1)

O. Jones
O. Jones

Reputation: 108651

You start with a subquery to get the earliest birthdate in each area code.

                       SELECT A.areaId, MIN(birthDate) earliestBirthDate
                         FROM Area A 
                         JOIN Person P ON A.areaId = p.areaId
                        GROUP BY A.areaId

You can try this subquery to convince yourself it yields exactly one row per areaId, showing the needed earliest birth date.

Then you JOIN the subquery to your detail query

SELECT P.personName, A.areaCode, P.birthDate
FROM Person P
JOIN Area A ON P.areaId = A.areaId
JOIN (
                       SELECT A.areaId, MIN(birthDate) earliestBirthDate
                         FROM Area A 
                         JOIN Person P ON A.areaId = p.areaId
                        GROUP BY A.areaId
     ) EARLY ON P.areaId = EARLY.areaID 
ORDER BY EARLY.earliestBirthDate, P.birthDate, A.areaCode

The trick is to use a subquery to make a virtual table so you can order on a column from that table.

Pro tip: If you find yourself using SELECT DISTINCT to retrieve detail data from tables, it's a caution flag. You may be doing something wrong.

Upvotes: 2

Related Questions