|Comments on Friday 22 October 2004:|
|An SQL query - ho ho, usually that would mean "a query written in SQL" but I am using it to mean "a query about SQL", thus it is an hilarious pun. Laugh! Laugh now!|
Anyway, the question - is it possible (and if so, how) to get one entire table row of your choice from a group selected in a GROUP BY clause? By which I mean something like
SELECT name,MAX(value) FROM table GROUP BY groupid;But such that the selected 'name' is the name from the same table row as the selected 'value'. It seems like something for which there should be a function, but I can't find one. Obviously it could be done with two queries or one nested query, but I don't want to do that. Anyone familiar with an answer? [02:51]
What are you, English?
|No no, it's a special 'an' just for that one context. It only goes with hilarious, and, I think, possibly even only goes with hilarious pun. And the hilarious pun has to be both excessively explained and not hilarious, or it doesn't get an 'an'.|
|Tsk, that was me. Lost my name-cookie while fixing Firefox.|
|SELECT SUBSTRING( MAX( CONCAT(LPAD(value,6,'0'),name) ), 7) AS name, 0.00+LEFT( MAX( CONCAT(LPAD(value,6,'0'),name) ), 6) AS value|
GROUP BY groupid;
That will work but it's hideously inefficient. You're better of using nested queries to be honest.
|You'll want to change the 7 to be the length of 'name' field and the 6 to be 'name-1' in the 'name' section, unless my head has gone all screwy.|
And I wish Zone Alarm would stop turning my web address into bloody asterisks.
|Have you checked into creating a "view"? Perhaps you could use a view rather than using a GROUP BY clause.|
|A view wouldn't be of interest really - the point was I wanted a single query for efficiency's sake rather than simplicity's, and a view wouldn't be any more efficient (if it could even do what I want).|