RavenBlog
Black:  ravenblack.net | me | games | books | email | wishlist | rss
Blogs:  Angryblog | As Above | MonkyBlog | Nightshade | Journals
Blimey:  SomethingAwful | Advice
Archives: Last 4 Days | June2001 | July2001 | August2001 | September2001 | October2001 | November2001 | December2001 | January2002 | February2002 | March2002 | April2002 | May2002 | June2002 | July2002 | August2002 | September2002 | October2002 | November2002 | December2002 | January2003 | February2003 | March2003 | April2003 | May2003 | June2003 | July2003 | August2003 | September2003 | October2003 | November2003 | December2003 | January2004 | February2004 | March2004 | April2004 | May2004 | June2004 | July2004 | August2004 | September2004 | October2004 | November2004 | December2004 | January2005 | February2005 | March2005 | April2005 | May2005 | June2005 | July2005 | August2005 | September2005 | October2005 | November2005 | January2006 | February2006 | March2006 | April2006 | May2006 | June2006 | July2006 | August2006 | September2006 | October2006 | November2006 | December2006 | January2007 | February2007 | March2007 | April2007 | May2007 | June2007 | July2007 | August2007 | September2007 | October2007 | November2007 | December2007 | January2008 | February2008 | March2008 | April2008 | May2008 | June2008 | July2008 | August2008 | September2008 | October2008 | November2008 | December2008 | January2009 | March2009 | April2009 | May2009 | July2009 | August2009 | September2009 | February2010 | March2010 | June2010 | July2010 | August2010 | September2010 | October2010 | November2010 | December2010 | February2011 | March2011 | April2011 | May2011 | June2011 | July2011 | August2011 | September2011 | October2011 | December2011 | March2012 | April2012 | May2012 | September2012 | December2012 | March2013 | April2013 | May2013 | June2013


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? [18: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).
Add Comment:
Name:Comment: (max. 2048 characters)
Email:
Show Email: (if no website)
Website:
No HTML tags allowed.
(Antispam) What is 45 + 49?
Archives: Last 4 Days | June2001 | July2001 | August2001 | September2001 | October2001 | November2001 | December2001 | January2002 | February2002 | March2002 | April2002 | May2002 | June2002 | July2002 | August2002 | September2002 | October2002 | November2002 | December2002 | January2003 | February2003 | March2003 | April2003 | May2003 | June2003 | July2003 | August2003 | September2003 | October2003 | November2003 | December2003 | January2004 | February2004 | March2004 | April2004 | May2004 | June2004 | July2004 | August2004 | September2004 | October2004 | November2004 | December2004 | January2005 | February2005 | March2005 | April2005 | May2005 | June2005 | July2005 | August2005 | September2005 | October2005 | November2005 | January2006 | February2006 | March2006 | April2006 | May2006 | June2006 | July2006 | August2006 | September2006 | October2006 | November2006 | December2006 | January2007 | February2007 | March2007 | April2007 | May2007 | June2007 | July2007 | August2007 | September2007 | October2007 | November2007 | December2007 | January2008 | February2008 | March2008 | April2008 | May2008 | June2008 | July2008 | August2008 | September2008 | October2008 | November2008 | December2008 | January2009 | March2009 | April2009 | May2009 | July2009 | August2009 | September2009 | February2010 | March2010 | June2010 | July2010 | August2010 | September2010 | October2010 | November2010 | December2010 | February2011 | March2011 | April2011 | May2011 | June2011 | July2011 | August2011 | September2011 | October2011 | December2011 | March2012 | April2012 | May2012 | September2012 | December2012 | March2013 | April2013 | May2013 | June2013