CODEWITHSUNDEEP
mysqlmax
user1135218
user1135218

Reputation: 403

mysql query using MAX

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?

Upvotes: 2

Views: 51

Answers (2)

valex
valex

Reputation: 24134

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

Upvotes: 0

Bohemian
Bohemian

Reputation: 424953

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.

Upvotes: 1

Related Questions