CODEWITHSUNDEEP
mysqlmax
Chamarejc
Chamarejc

Reputation: 25

Mysql Use of MAX in a subquery

Why can't I use that subquery which returns a group error ?

SELECT hs.dateFin, hs.codeAdherent, hs.codeArticle 
FROM hs 
WHERE hs.codeFamilleArticle IN ('CNI', 'COT', 'ABO', 'ABOW',
                                'CNIW', 'O&T', 'EPH', 'TAX') 
AND codeAdherent != 0 
AND MAX(hs.dateFin) BETWEEN '2017-01-01' 
                        AND '2017-12-31'
GROUP BY hs.codeAdherent

The same data exists for 2018-01-01 and 2018-12-31 but I only want to get the ones that end in 2017. Here under a sample of table which contains 140000 raws (not all columns are showed).

Here

codeAdherent A has data for 2018, 2017, 2016.

codeAdherent B has data for2018, 2017

codeAdherent C only for 2017.

If I do a select on 2017 I get all three codeAdherent, then the MAX BETWEEN will exclude A and B... But that's doesn't work

Upvotes: 1

Views: 81

Answers (3)

Salman Arshad
Salman Arshad

Reputation: 272396

You can use NOT EXISTS to check if no record exists for 2018:

SELECT dateFin, codeAdherent, codeArticle
FROM hs AS t
WHERE codeFamilleArticle IN ('CNI', 'COT', 'ABO', 'ABOW', 'CNIW', 'O&T', 'EPH', 'TAX')
AND codeAdherent != 0
-- filter 2017 rows
AND dateFin >= '2017-01-01'
AND dateFin <  '2018-01-01'
-- filter rows where 2018 data does not exist
AND NOT EXISTS (
    SELECT 1
    FROM hs
    WHERE codeAdherent = t.codeAdherent
    AND dateFin >= '2018-01-01'
)

Upvotes: 1

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28864

You cannot use aggregate functions like Max() inside Where clause. You can simply modify your where condition to include dates in year 2017 only, and then determine Max() date after group by.

SELECT MAX(hs.dateFin), hs.codeAdherent, hs.codeArticle 
FROM hs 
WHERE hs.codeFamilleArticle IN ('CNI', 'COT', 'ABO', 'ABOW',
                                'CNIW', 'O&T', 'EPH', 'TAX') 
AND hs.codeAdherent != 0 
WHERE hs.dateFin BETWEEN '2017-01-01' 
                     AND '2017-12-31'
GROUP BY hs.codeAdherent, hs.codeArticle

Upvotes: 0

Dave
Dave

Reputation: 3413

You can do it like this:

HAVING YEAR(MAX(hs.dateFin)) = 2017

Upvotes: 0

Related Questions