----- 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.
Andrew,
Indeed, the above does seem to do exactly what I need. I guess the
missing link (in my experimentation), was adding ID to the GROUP BY
step. Without that, the returned ID and Version weren't guaranteed to
come from the same original record.
It's always so easy when you see it done... Thanks for your
assistance.
Jeff
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
