mysql query using MAX

Question

I hope someone can help me. I've looked at other threads but I was not able to find the right answer.

I have a mysql database with the following data:

id, company, datefrom, dateto, rate, point1, point2

1, DHL, 2014-03-01, 2014-03-31, $1000, Paris, New York

2, MRW, 2014-03-01, 2014-03-31, $1300, Paris, New York

3, EXP, 2014-03-01, 2014-03-31, $1000, Paris, New York

4, DHL, 2014-03-06, 2014-03-31, $900, Paris, New York

5, FED, 2014-03-01, 2014-03-31, $1200, Paris, New York

I need to do a query where, based on the date, it will display only those rates valid on that date onwards. For example if today is 2014-03-06, I need the result to be :

2, MRW, 2014-03-01, 2014-03-31, $1300, Paris, New York

3, EXP, 2014-03-01, 2014-03-31, $1000, Paris, New York

4, DHL, 2014-03-06, 2014-03-31, $900, Paris, New York

5, FED, 2014-03-01, 2014-03-31, $1200, Paris, New York

As you can see, record 1 is not displayed as record 4 replaces record 1 as of 2014-03-06

I have tried the following query, which worked:

SELECT id, company, MAX(datefrom), dateto, rate, point1, point2 FROM mydatabase
WHERE datefrom<='2014-03-06' and datefrom>='2014-03-06'
AND point1='Paris' AND point2='New York' GROUP BY company, point1, point2

This query works great EXCEPT that it selects all companies but for DHL, it selects all fields from record 1 BUT the datefrom from record 4 !

What am I doing wrong?

This question is asked every single day in SO (and often correctky answered), so have a look around. — Strawberry
– Strawberry, Commented Mar 6, 2014 at 12:12
What you're doing wrong is - mixing grouping columns with non-group in selection. It is not valid in normal SQL. MySQL, however, allows such behavior, but free to choose any row in that case. You should select only grouping rows as a subquery and then join result with original table — Alma Do
– Alma Do, Commented Mar 6, 2014 at 12:14
It is perfectly fine - record number 4 have the latest date, as MAX will take the maximum value from the whole table. So you query will put in each row the same value. — Rolice
– Rolice, Commented Mar 7, 2014 at 8:52

Bohemian · Accepted Answer · 2014-03-06 12:15:28Z

1

Make use of mysql's non-standard grouping support:

SELECT * FROM (
  SELECT * FROM mydatabase
  WHERE '2014-03-06' BETWEEN datefrom AND dateto
  AND point1 = 'Paris'
  AND point2 = 'New York'
  ORDER BY datefrom DESC) x
GROUP BY company

This works by first ordering matching rows by latest-starting range first in the inner query, then by grouping by not all non-aggregate columns, mysql selects the first row for each unique value of company, the first row being the one you want (having previously been ordered).

Note also the reworking of the condition to use BETWEEN which is more elegant.

answered Mar 6, 2014 at 12:15

Bohemian♦

427k103 gold badges604 silver badges750 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user1135218 Over a year ago

Wonderful, thanks Bohemian, your code works great. Just what I was after. Just a question, what does the x do ? (at the end of DESC) x )

Bohemian Over a year ago

the x is an alias for the inner query, and an alias is required to be specified when selecting from the results of an inner query, even if the alias isn't referred to. You can use any name you like, but I like using x because it's short and mysterious

valex · Accepted Answer · 2014-03-06 12:22:01Z

0

You need to add condition of not existing record with datefrom after current record datefrom.

select * from mydatabase t1

WHERE '2014-03-06' between datefrom and dateto
AND NOT EXISTS 
(
  select 1 from mydatabase
  where company=t1.company
    AND point1 = t1.point1
    AND point2 = t1.point2
    AND '2014-03-06' between datefrom and dateto
    AND datefrom>t1.datefrom
)

SQLFiddle demo

answered Mar 6, 2014 at 12:22

valex

24.2k7 gold badges47 silver badges62 bronze badges

Collectives™ on Stack Overflow

mysql query using MAX

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related