SQL query help

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

SQL query help

jgodfrey
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


-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: SQL query help

Mr. Puneet Kishor
On 6/18/07, Jeff Godfrey <[hidden email]> 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 [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: SQL query help

jgodfrey

----- Original Message -----
From: "P Kishor" <[hidden email]>
To: <[hidden email]>
Sent: Monday, June 18, 2007 2:55 PM
Subject: Re: [sqlite] SQL query help


> On 6/18/07, Jeff Godfrey <[hidden email]> wrote:

>> 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

> Jeff, how about something like
>
> SELECT name, MAX(version) AS latest
> FROM asset
> GROUP BY name

Thanks Puneet - that's just what I needed.

Side note - the timing on the list seems to be whacky right now (at
least for me), so some of my responses seem to be coming out of order.
Sorry if that causes some confusion...

Thanks again.

Jeff


-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------