some mysql help please...

egan007

This seems really straight forward and probably is - I may be looking at it the wrong way too long.


Each widget can have multiple entries.
I want to extract the newest (by date) for each

lineid widgetid widgetprice1 widgetprice2 date(unix time)

l-x-11 w-1-234 10 11 1210868108
l-x-13 w-1-234 12 13 1210868109
l-x-23 w-5-677 99 88 1210868110
l-x-34 w-5-677 97 86 1210868111
l-x-99 w-5-679 55 57 1210868112


I thought something like this would work but it doesn't....

SELECT *
FROM `widgets`
GROUP BY 'widgetid'
ORDER BY 'date' DESC


any help please.

Adam

What output are you looking to achieve?

egan007

AS above :

I want to extract the newest (by date) for each widget

Adam

Ah you want select DISTINCT then, no?

EDIT: Scratch that. I believe your table structure is really not befitting to the way you wish to store data. You should have each unique widget in one table and the data pertaining to the dates in another table. That would make this sql far simpler, and is better practice. If you don't exercise these best practices from the start, you'll only end up in sticky situations like this again and again...

egan007

There is actually another table for describing the widgets.

Can you suggest something on that basis?

Mad_Max

Would a limit help you?

E.G.
SELECT *
FROM `widgets`
GROUP BY 'widgetid'
ORDER BY 'date' DESC LIMIT 1

What is the current sql outputting for you.

FSL

Select widgetid,max(date) from widgets group by widgetid

Adam

My head is fried at the moment but this should work for you:

SELECT lineid, widgetid, widgetprice1, widgetprice2, MAX(date) FROM widgets GROUP BY widgetid ORDER BY date

Regarding your table structure, what you need to do is read up on normalisation.

egan007

Thanks for the replies

You would think these queries should work but i am getting some strande output.

SELECT lineid, widgetid, widgetprice1, widgetprice2, MAX(date) FROM widgets GROUP BY widgetid ORDER BY date

I get a mix....

From the table above these two widget id's

l-x-11 w-1-234 10 11 1210868108
l-x-13 w-1-234 12 13 1210868109

This query gives

l-x-11 w-1-234 10 11 1210868109

i.e all the data in the first record for widget w-1-234 with the max date - which is the second record

egan007

Ok I think I know what to do

2 tables.

One for widget history and one for current widgets....

Thanks for the input everyone

seamus

egan007 wrote: »

You would think these queries should work but i am getting some strande output.

SELECT lineid, widgetid, widgetprice1, widgetprice2, MAX(date) FROM widgets GROUP BY widgetid ORDER BY date

I asked the *exact* same question before here and bonkey gave me the answer.

Becuase MySQL doesn't insist that all selected columns are in the GROUP BY statement or in another aggregate function, it tends to get the MAX() column and then for the rest of the columns, retrieve the first data found.

Obviously if you're just looking for the last date for a single widget, it's
SELECT * FROM widget where widgetid = 1 ORDER BY date DESC LIMIT 0,1

However, if you're looking for a list of all widgets and their most recent updates, you'll need to be creative (whether or not you're using 2 tables).

First off, think about how you'd get just the most recent date for a widget:

select 
	widgetid, 
	MAX(date) as lastdate 
from 
	widget 
GROUP BY 
	widgetid

This will give you results like this.
w-1-234|1210868109
w-5-677|1210868111
w-5-679|1210868112
Now, imagine that this resultset is actually a separate table in your database called "last_update". You could use this to get the most recent items, using the below query:

SELECT 
	w.* 
FROM 
	widget w, last_update lu
WHERE 
	w.widget_id = lu.widget_id 
	AND w.date = lu.lastdate

But since "last_update" is actually a query, and not a table, all you need to do is insert the original query into the main query. Subqueries rock. This looks long-winded, but should do what you want

SELECT 
	w.* 
FROM 
	widget w, (select 
			widgetid, 
			MAX(date) as lastdate 
		from 
			widget 
		GROUP BY 
			widgetid
		) lu
WHERE 
	w.widget_id = lu.widget_id 
	AND w.date = lu.lastdate

To the best of my knowledge, the only way to avoid having to use a subquery is to maintain a separate column in your "widget" table, which has the ID of the last update for this widget. The potential for data corruption exists (i.e. if you insert an item to the update table, but then the "widget" table doesn't get updated). You need to weigh up this potential against any speed concerns and the size of the database.

Adam

Aye, an excellent response from Seamus there, I knew a sub-query could do it as well, and that the result set from the query FSL and I gave would be a bit unusual, but to be honest, that's above my sql knowledge! :pac:

egan007

Many thanks, that indeed returns the right result so thanks for taking the time to post.
I decided in the end to have a historic table and current table.
This actually sped up the page as the current table is only a subset of records.

FishD

I would have thought that the Select * makes the Group by clause reduntant, because the Unixtime field shows all records to be unique? Group by will only work if the recordset output contains non-unique rows