SQL query help

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

SQL query help

jgodfrey
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
Reply | Threaded
Open this post in threaded view
|

Re: SQL query help

Igor Tandetnik
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.

select name, max(version)
from asset
group by name;

Igor Tandetnik

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

Reply | Threaded
Open this post in threaded view
|

Re: Re: SQL query help

jgodfrey

----- Original Message -----
From: "Igor Tandetnik" <[hidden email]>
To: "SQLite" <[hidden email]>
Sent: Monday, June 18, 2007 1:17 PM
Subject: [sqlite] Re: SQL query help


> 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.
>
> select name, max(version)
> from asset
> group by name;
>
> Igor Tandetnik

Igor,

Perfect - thank you.  It's so simple once you see it done... ;^)

Jeff


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

Reply | Threaded
Open this post in threaded view
|

3.4.0 make

ken-33
In reply to this post by Igor Tandetnik

 make distclean
           does not seem to clear off the sqlite3.c no tsrc directories.
 
 
 My current build script:
 
 configure
 make distclean
 configure --enable-threadsafe --disable-tcl --enable-tempstore
 make sqlite3.c
 make
 
 
 How does one now build libraries static and shared based upon the amalgamated source?
 
 Thanks for any help.
 
 
Reply | Threaded
Open this post in threaded view
|

Re: 3.4.0 make

ken-33
DRH
 Maybe the following suggestion for the Makefile...
 
  Allow two targets,
 
  1. the default  "make all" which generates the .a and .so from individual .o
  2. a seperate target:   make amalgamated  which would build the tsrc and sqlite3.c
                        Then create the .a, .so an sqlite3 (exe) from the amalgamated sources.
 
  Regards and Thanks for a great product,
  Ken
 
 

Ken <[hidden email]> wrote:
 make distclean
           does not seem to clear off the sqlite3.c no tsrc directories.
 
 
 My current build script:
 
 configure
 make distclean
 configure --enable-threadsafe --disable-tcl --enable-tempstore
 make sqlite3.c
 make
 
 
 How does one now build libraries static and shared based upon the amalgamated source?
 
 Thanks for any help.