CODEWITHSUNDEEP
mysqlgroup-bymultiple-conditions
Edvaaart
Edvaaart

Reputation: 122

MySQL query using group by with multiple conditions

EDIT: I have removed the confusing remark at the end and added an additional column pl_id which I thought irrelevant (due to my apparent lack of understanding the group by workings). When trying the suggestions posted, I ran into a problem where introducing the MAX/Group By would result in

  | name  | license_plate | pl_id | start_date |
  | aaron | AA-AA-22      |     3 | 2016-1-1   |

Where i expected:

  | name  | license_plate | pl_id | start_date |
  | aaron | AA-AA-22      |     4 | 2016-1-1   |

So somehow the max date for license plate AA-AA-22, which is 2016-1-1, appears in the record with pl_id = 3, why does the result return a record with the date from pl_id 4? END EDIT

I have a table with columns:

I need a query that returns exactly one record for each license plate with the maximum start_date or where start_date is empty. But empty start_date should take precedence above maximum start_date.

Each license plate is guaranteed to have at most one entry with start_date = null but can have multiple entries with valid 'start_date'-s.

So for example, when executed on the following table:

  | name  | license_plate | pl_id | start_date |
  | aaron | AA-AA-11      |     1 | 2015-1-1   |
  | aaron | AA-AA-11      |     2 | null       |
  | aaron | AA-AA-22      |     3 | 2015-1-1   |
  | aaron | AA-AA-22      |     4 | 2016-1-1   |
  | bill  | BB-BB-11      |     5 | 2015-1-1   |
  | bill  | BB-BB-11      |     6 | null       |
  | bill  | BB-BB-22      |     7 | 2015-1-1   |
  | clark | CC-CC-11      |     8 | 2015-1-1   |
  | clark | CC-CC-11      |     9 | 2016-1-1   |
  | dave  | DD-DD-11      |    10 | 2014-1-1   |
  | dave  | DD-DD-11      |    11 | 2015-1-1   |
  | dave  | DD-DD-11      |    12 | 2016-1-1   |
  | eddy  | EE-EE-11      |    13 | null       |

The query should return:

  | name  | license_plate | pl_id | start_date |
  | aaron | AA-AA-11      |     2 | null       |
  | aaron | AA-AA-22      |     4 | 2016-1-1   |
  | bill  | BB-BB-11      |     6 | null       |
  | bill  | BB-BB-22      |     7 | 2015-1-1   |
  | clark | CC-CC-11      |     9 | 2016-1-1   |
  | dave  | DD-DD-11      |    12 | 2016-1-1   |
  | eddy  | EE-EE-11      |    13 | null       |

Upvotes: 0

Views: 1997

Answers (4)

user4141004
user4141004

Reputation: 

SELECT name, license_plate, IF(max(IF(start_date IS NULL, '2099-01-01',  start_date)) = '2099-01-01', null, max(start_date)) 
FROM table
GROUP BY name, license_plate;

You can change '2099-01-01' to some bigger number if needed.

Tested on your dataset.

Upvotes: 5

Emil Heraña
Emil Heraña

Reputation: 82

you can use the Group By

SELECT  distinct * FROM [tablename] where start_date is null or start_date group by 
license_number order by name,start_date

the "start_date is null" is for you to display first if there is a null in the start_date in the group of license_plate and the "or start_date" is for you to display the other dates that is not null.

it will produce the same result:

| name  | license_plate | start_date |
| aaron | AA-AA-11      | null       | 
| aaron | AA-AA-22      | 2016-1-1   |
| bill  | BB-BB-11      | null       |
| bill  | BB-BB-22      | 2015-1-1   |
| clark | CC-CC-11      | 2016-1-1   |
| dave  | DD-DD-11      | 2016-1-1   |
| eddy  | EE-EE-11      | null       |

Here is a capture of the result:

enter image description here Here is a capture of the result

Upvotes: 0

kyeiti
kyeiti

Reputation: 376

I'm confused, from your example you seem to want to take null priority over any real dates - I just don't see how that relates to group by multiple conditions... Maybe this answer can help you?

SELECT
    `name`,
    license_plate,
    CASE
        WHEN MAX(start_date IS NULL) = 0 THEN MAX(start_date)
    END AS start_date
FROM
    plates
GROUP BY
    `name`,
    license_plate

Output:

+-------+---------------+------------+
| name  | license_plate | start_date |
+-------+---------------+------------+
| aaron | AA-AA-11      | NULL       |
| aaron | AA-AA-22      | 2016-01-01 |
| bill  | BB-BB-11      | NULL       |
| bill  | BB-BB-22      | 2015-01-01 |
| clark | CC-CC-11      | 2016-01-01 |
| dave  | DD-DD-11      | 2016-01-01 |
| eddy  | EE-EE-11      | NULL       |
+-------+---------------+------------+
7 rows in set

Upvotes: 4

Wajih
Wajih

Reputation: 4383

You need 2 queries, One fo NULL values and another for MAX(start_date)

SELECT name, license_plate, MAX(start_date) FROM test.tab
group by name, license_plate having count(start_date) = count(name)

union 
select name, license_plate, start_date From test.tab
Where start_date is null

in this condition count(start_date) = count(name) you will discover records which have null values because null values does not computed in count sentence.

And the another select will return records with start_date = null.

You can add where condition to the second query if your database has more than one record has null value for the same license_plate

Upvotes: 1

Related Questions