CODEWITHSUNDEEP
mysqljoingroup-bysql-order-bygreatest-n-per-group
Zbynek
Zbynek

Reputation: 5945

MySQL - JOIN, GROUP BY with ORDER of grouping

I have two tables:

CREATE TABLE Person {
    ID INT PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    Surname VARCHAR(50) NOT NULL
}

and 

CREATE TABLE Address {
    ID INT PRIMARY KEY,
    ID_Person INT NOT NULL,
    Street VARCHAR(50),
    HouseNumber VARCHAR(15),
    City VARCHAR(75),
    Zipcode VARCHAR(10),
    CountryCode CHAR(2),
    IsPrimary TINYINT(1) DEFAULT 0
}

Each person can have muptiple addresses, but at most one can be primary (IsPrimary = 1).

I want to get list of persons with one address. If person has primary address, it should be provided, if not, it does not matter, which address is retrieved.

I have this query:

SELECT
    p.Name,
    p.Surname,
    a.Street,
    a.Housenumber,
    a.City,
    a.Zipcode
FROM
    Person AS p        
LEFT JOIN (select * from Address ORDER BY IsPrimary DESC) AS a ON p.ID = a.ID_Person
GROUP BY p.ID

but that does not provide results that I expect. I expected first row of joined table to be retrieved when performing GROUP BY, but that is not the case.

Similar question was asked here but the solution is rather difficult in my situation.

Upvotes: 0

Views: 60

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562230

In MySQL 8.0, this is best done as a ranking query.

WITH PersonAddress AS (
  SELECT 
    p.Name,
    p.Surname,
    a.Street,
    a.Housenumber,
    a.City,
    a.Zipcode,
    ROW_NUMBER() OVER (PARTITION BY p.ID ORDER BY a.IsPrimary DESC) AS rn
  FROM Person AS p
  LEFT OUTER JOIN Address AS a ON p.ID = a.ID_Person
)
SELECT * FROM PersonAddress WHERE rn = 1;

Before MySQL 8.0, windowing functions are not available. The workaround is to use session variables:

SELECT 
  t.Name,
  t.Surname,
  t.Street,
  t.Housenumber,
  t.City,
  t.Zipcode
FROM (
  SELECT 
    p.Name,
    p.Surname,
    a.Street,
    a.Housenumber,
    a.City,
    a.Zipcode,
    IF(p.ID = @pid, @rn:=@rn+1, @rn:=1) AS rn,
    @pid := p.ID
  FROM (SELECT @pid:=0, @rn:=1) AS _init
  CROSS JOIN Person AS p
  LEFT OUTER JOIN Address AS a ON p.ID = a.ID_Person
  ORDER BY p.ID, a.IsPrimary DESC
) AS t
WHERE t.rn = 1;

Upvotes: 1

Norbert
Norbert

Reputation: 6084

ORDER BY in a sub query usually only slows down you query. You will have to order the result instead:

SELECT
    p.Name,
    p.Surname,
    a.Street,
    a.Housenumber,
    a.City,
    a.Zipcode
FROM
    Person AS p        
LEFT JOIN Address AS a ON p.ID = a.ID_Person
GROUP BY p.ID ORDER BY a.isPrimary

This query has a second problem: It is not ANSI compliant, so it only works in MySQL when MySQL is not running ANSI compliant.

Assume this: You have 1 p.ID with two rows in the table Address. There are no GROUP functions applied to Address.City, how does the database know which City to display? It does not, so you see a random one. To prevent this apply functions to all columns which are not in the group by (or place the columns in the group by).

Upvotes: 1

Related Questions