----- Original Message -----
From: "Andrew Finkenstadt" <[EMAIL PROTECTED]>
To: <sqlite-users@sqlite.org>
Sent: Sunday, July 01, 2007 4:45 PM
Subject: Re: [sqlite] SQL query assistance...
On 7/1/07, Jeff Godfrey <[EMAIL PROTECTED]> wrote:
Hi All,
Given the following sample data...
ID Name Version
--- ----- -------
1 name1 0.9
2 name1 1.0
3 name2 1.2
4 name3 1.0
5 name3 1.7
6 name3 1.5
I need to create a query that will group the data together by Name,
but
for each group will return the record with the highest version
number and a
count of the items in the group. So, with the above data, I'd
expect this
as output:
ID Name Version Count
--- ----- ------- -----
2 name1 1.0 2
3 name2 1.2 1
5 name3 1.7 3
Thanks for any assistance.
select ID, Name, max(version), count(*) Count
from table_name
group by ID, Name
order by ID, Name
... that looks to meet your criteria.
Hmmm... Looking closer, this doesn't seem to be right. Sorry for my
earlier report - I didn't look at the data close enough. Since ID is
always unique, won't GROUP BY ID effectively eliminate any possible
grouping? Sorry, still learning here...
Thanks,
Jeff
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
