----- Original Message -----
From: "Gerry Snyder" <[EMAIL PROTECTED]>
To: <sqlite-users@sqlite.org>
Sent: Sunday, July 01, 2007 4:40 PM
Subject: Re: [sqlite] SQL query assistance...
Jeff Godfrey 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.
It would seem that something like:
select ID, Name, max(Version),count(*) from table group by Name
should work.
Thanks Gerry,
Indeed, I have tried exactly that. The problem is that max(Version)
just returns the maximum version for the group, right? So, the
returned version doesn't necessarily "belong" to the ID that's
returned, correct? The result should be exact copies of the original
records that had the highest version for each group, along with a
count for each group. The above query seems to mix the "highest
version" with whatever record was chosen to represent the group. I
need to ensure that the group record *is* the record with the highest
version...
Thanks,
Jeff
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
