| RavenBlog |
|
| Comments on Thursday 21 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? [22:51] |
| James |
| "an hilarious" What are you, English? |
| Nameless |
| 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'. |
| RavenBlack |
| Tsk, that was me. Lost my name-cookie while fixing Firefox. |
| SpasmodicMonk |
| 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 FROM table GROUP BY groupid; That will work but it's hideously inefficient. You're better of using nested queries to be honest. |
| SpasmodicMonk |
| 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. |
| Buffer0verflow |
| Have you checked into creating a "view"? Perhaps you could use a view rather than using a GROUP BY clause. |
| RavenBlack |
| 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). |