On 6/18/07, Jeff Godfrey <[EMAIL PROTECTED]> wrote:
Not seeing this on the list 1.5 hrs after posting, I thought I'd try
again. Sorry if this is a duplicate...
Jeff
=========
Hi All,
I have a table which contains (among other things), a "name" column
and a "version" column (a software asset table). I need a query that
will group all like "names" together in a single record, and return
the latest "version" (the largest value) for each group. What I have
so far is this:
SELECT name, version
FROM asset
GROUP BY name
ORDER BY name ASC, version DESC
While the above seems to return the expected results, I'm not
convinced that I'm actually controlling the sort order, as changing
"version DESC" to "version ASC" does not return the *earliest* version
as I'd expect. I assume the record that will be returned has already
been selected at the "GROUP BY" stage and therefore I have no control
over it at the "ORDER BY" stage? I know, I need to do some more
reading... ;^)
Thanks for any input.
Jeff, how about something like
SELECT name, MAX(version) AS latest
FROM asset
GROUP BY name
--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
S&T Policy Fellow, National Academy of Sciences http://www.nas.edu/
---------------------------------------------------------------------
collaborate, communicate, compete
=====================================================================
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
