It is possible if you make the number of digits in day all the same by
using something like lpad.
The secret is to tie all of the values you want together with something
like CONCAT and making sure that
the resulting string sorts in the order you want with its alphabetic
sort sequence. Then MAX gives you
the largest {most recent} value and then substring_index breaks it back
apart. The other bit of magic is
to remember that with MySQL the fields you use to GROUP BY {food.name in
this case} does not have to be
included in the SELECT. In this case name is not the same as food.name
and probably should be aliased
something like 'recent_name' to avoid confusion.
select
substring_index(max(concat(lpad(day,4,'0'),'|',name,'|',id,'|',price)),'
|',1) + 0 as day,
substring_index(substring_index(max(concat(lpad(day,4,'0'),'|',name,'|',
id,'|',price)),'|',2),'|',-1) as name,
substring_index(substring_index(max(concat(lpad(day,4,'0'),'|',name,'|',
id,'|',price)),'|',3),'|',-1) as id,
substring_index(substring_index(max(concat(lpad(day,4,'0'),'|',name,'|',
id,'|',price)),'|',4),'|',-1) as price
from food
group by food.name;
mysql> create table food (id int(11) not null primary key, name
char(20), day int(11), price decimal(10,2));
Query OK, 0 rows affected (0.07 sec)
mysql> insert into food
-> values
-> (1, 'tomatoes', 1, 25),
-> (2, 'onions', 1, 13),
-> (3, 'onions', 2, 16),
-> (4, 'tomatoes', 2, 30);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from food;
+----+----------+------+-------+
| id | name | day | price |
+----+----------+------+-------+
| 1 | tomatoes | 1 | 25.00 |
| 2 | onions | 1 | 13.00 |
| 3 | onions | 2 | 16.00 |
| 4 | tomatoes | 2 | 30.00 |
+----+----------+------+-------+
4 rows in set (0.00 sec)
mysql> select
substring_index(max(concat(lpad(day,4,'0'),'|',name,'|',id,'|',price)),'
|',1) + 0 as day,
->
substring_index(substring_index(max(concat(lpad(day,4,'0'),'|',name,'|',
id,'|',price)),'|',2),'|',-1) as name,
->
substring_index(substring_index(max(concat(lpad(day,4,'0'),'|',name,'|',
id,'|',price)),'|',3),'|',-1) as id,
->
substring_index(substring_index(max(concat(lpad(day,4,'0'),'|',name,'|',
id,'|',price)),'|',4),'|',-1) as price
-> from food
-> group by food.name;
+------+----------+------+-------+
| day | name | id | price |
+------+----------+------+-------+
| 2 | onions | 3 | 16.00 |
| 2 | tomatoes | 4 | 30.00 |
+------+----------+------+-------+
2 rows in set (0.00 sec)
Also works when foods are last updated on different days
mysql> update food set day = 3 where id = 4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select
substring_index(max(concat(lpad(day,4,'0'),'|',name,'|',id,'|',price)),'
|',1) + 0 as day,
->
substring_index(substring_index(max(concat(lpad(day,4,'0'),'|',name,'|',
id,'|',price)),'|',2),'|',-1) as name,
->
substring_index(substring_index(max(concat(lpad(day,4,'0'),'|',name,'|',
id,'|',price)),'|',3),'|',-1) as id,
->
substring_index(substring_index(max(concat(lpad(day,4,'0'),'|',name,'|',
id,'|',price)),'|',4),'|',-1) as price
-> from food
-> group by food.name;
+------+----------+------+-------+
| day | name | id | price |
+------+----------+------+-------+
| 2 | onions | 3 | 16.00 |
| 3 | tomatoes | 4 | 30.00 |
+------+----------+------+-------+
2 rows in set (0.00 sec)
> -----Original Message-----
> From: Becoming Digital [mailto:[EMAIL PROTECTED]
> Sent: Sunday, June 01, 2003 5:29 PM
> To: [EMAIL PROTECTED]
> Subject: Re: SELECTing last occurences from a table
>
>
> Do you have daily prices for a set number number of foods? If so, try
>
> SELECT * FROM food
> LIMIT n,-1;
>
> where n is the number of items. There's probably a better
> way to do it in SQL, I just don't know how. If you are using
> PHP, that's another story.
>
> Edward Dudlik
> Becoming Digital
> www.becomingdigital.com
>
>
> ----- Original Message -----
> From: "Mateusz" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Sent: Sunday, 01 June, 2003 15:38
> Subject: SELECTing last occurences from a table
>
>
> Hi!
> I couldn't find anything in archives, maybe I search for
> wrong phrase. I'm running MySQL v.3.23.49 (if it's not
> possible under this version, I may upgrade it). What I need
> to accomplish is to find only last occurence of all possible
> items from given column.
>
> Supposing:
>
> id name day price
> ---------------------------
> 1 tomatoes 1 25
> 2 onions 1 13
> 3 onions 2 16
> 4 tomatoes 2 30
>
> I want to obtain only:
>
> 3 onions 2 16 (latest price of onions -
> from day 2nd)
> 4 tomatoes 2 30 (latest price of tomatoes -
> from day 2nd)
>
> How to do that? (in as little selects as possible)
> Thanks for any help!
>
> Mateusz Uzdowski
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?> [EMAIL PROTECTED]
>
>
>
>
>
>
> --
> MySQL General
> Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?> [EMAIL PROTECTED]
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
