CODEWITHSUNDEEP
mysqldate
lennert_h
lennert_h

Reputation: 213

Get only latest date from database (but get all rows with that latest date)

I'm creating a small application that displays openinghours.

It works like this: I generate a list of dates, then check the database if we are open or not.

The database normal contains:

day_of_week |  open  |  close |  since
   INT(11)  |  TIME  |  TIME  |  DATE
------------|--------|--------|------------
     1      |09:00:00|17:00:00|2013-01-09
     2      |08:00:00|18:00:00|2014-01-06
...

The since-date show since what date the openinghours are used (so I can look back in the past and get the right hours, as well as in the future.)

In my list of date I check if since >= $date_in_list and only display max value:

 SELECT *,MAX(since) FROM normal WHERE day_of_week = '2' AND since <= '2014-06-03'

This works fine, unless I have 2 hours with the same since-date (e.g. when we close at noon.)

So when I have data like this:

day_of_week |  open  |  close |  since
------------|--------|--------|------------
     2      |09:00:00|12:00:00|2014-01-06
     2      |14:00:00|18:00:00|2014-01-06

I would like to get both 09:00:00 - 12:00:00 and 14:00:00 - 18:00:00, but I can't seem to find the right query.

Upvotes: 3

Views: 52

Answers (2)

mingos
mingos

Reputation: 24502

How about using a subquery? Something along these lines

SELECT *
FROM normal
WHERE day_of_week = '2'
    AND since = (
        SELECT MAX(since)
        FROM normal
        WHERE day_of_week = '2'
    )

Upvotes: 2

Sadikhasan
Sadikhasan

Reputation: 18600

Try with GROUP BY clause like this

SELECT *,
       MAX(since)
FROM normal
WHERE day_of_week = '2'
  AND since <= '2014-06-03'
GROUP BY `since`,`open`,`close`;

Upvotes: 0

Related Questions