CODEWITHSUNDEEP
mysqlselectmax
david916
david916

Reputation: 31

Select the most recent Date, but smaller than today's date in a record list

grade_id
grade_name
price
update_date.

There are several records for a given Grade, with diferent date & price...:

grade_id   grade_name  price  update_date (y-m-d)
1           A          8$      2011-02-01
1           A          10$     2011-03-01
1           A          20$     2011-04-01
2           B          10$     2011-02-01
2           B          20$     2011-03-01
2           B          30$     2011-04-01

How can i get the last updated price (but in the past) with a select queries.. to get:

1           A          10$     2011-03-01
2           B          20$     2011-03-01

as a result...(because most recent price, with a past date..

thx david

Upvotes: 3

Views: 2654

Answers (5)

Ronnis
Ronnis

Reputation: 12843

Not the fastest solution for a large nr of records, but a readable one. 

select *
  from table t1
 where update_date = 
        (select max(update_date)
           from table t2
          where t2.grade_id = t1.grade_id
            and t2.update_date < current_date);

A primary key on (grade_id, update_date) on a InnoDB table helps.

Upvotes: 2

user667618
user667618

Reputation: 

root@natasha:test> CREATE TABLE t (grade_id INT UNSIGNED NOT NULL, grade_name CHAR(1) NOT NULL, price CHAR(3) NOT NULL, update_date DATE);
Query OK, 0 rows affected (0.10 sec)

root@natasha:test> INSERT INTO t VALUES (1, 'A', '8$', '2011-02-01'), (1, 'A', '10$', '2011-03-01'), (1, 'A', '20$', '2011-04-01'), (2, 'B', '10$', '2011-02-01'), (2, 'B', '20$', '2011-03-01'), (2, 'B', '30$', '2011-04-01');
Query OK, 6 rows affected (0.13 sec)
Records: 6  Duplicates: 0  Warnings: 0

root@natasha:test> SELECT * FROM t;
+----------+------------+-------+-------------+
| grade_id | grade_name | price | update_date |
+----------+------------+-------+-------------+
|        1 | A          | 8$    | 2011-02-01  |
|        1 | A          | 10$   | 2011-03-01  |
|        1 | A          | 20$   | 2011-04-01  |
|        2 | B          | 10$   | 2011-02-01  |
|        2 | B          | 20$   | 2011-03-01  |
|        2 | B          | 30$   | 2011-04-01  |
+----------+------------+-------+-------------+
6 rows in set (0.00 sec)

root@natasha:test> SELECT * FROM (SELECT * FROM t WHERE update_date < DATE(NOW()) ORDER BY update_date DESC) AS `t` GROUP BY grade_id;
+----------+------------+-------+-------------+
| grade_id | grade_name | price | update_date |
+----------+------------+-------+-------------+
|        1 | A          | 10$   | 2011-03-01  |
|        2 | B          | 20$   | 2011-03-01  |
+----------+------------+-------+-------------+
2 rows in set (0.00 sec)

Upvotes: 1

mechanical_meat
mechanical_meat

Reputation: 169444

  SELECT t1.grade_id, t1.grade_name, t1.price, t1.update_date
    FROM my_tbl t1
         LEFT JOIN my_tbl t2 on t2.grade_id = t1.grade_id
     AND t2.update_date > t1.update_date
     AND t2.update_date < CURRENT_DATE
   WHERE t1.update_date < CURRENT_DATE
     AND t2.grade_id IS NULL
ORDER BY t1.grade_name

Upvotes: 4

Nicola Cossu
Nicola Cossu

Reputation: 56397

select t1.* from table as t1
inner join (
select grade_id,max(update_date) as update_date
from table where update_date < curdate() group by grade_id ) as t2
on t1.grade_id = t2.grade_id and t1.update_date = t2.update_date

Add an index to the table on (grade_id,update_date)

Upvotes: 0

Codler
Codler

Reputation: 11256

SELECT MAX(update_date) FROM table WHERE DATE(update_date) != DATE(NOW());

Upvotes: -2

Related Questions