----- Original Message -----
From: "Dan Kennedy" <[EMAIL PROTECTED]>
To: <sqlite-users@sqlite.org>
Sent: Monday, July 02, 2007 4:17 AM
Subject: Re: [sqlite] SQL query assistance...
On Sun, 2007-07-01 at 16:31 -0500, 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.
How about:
SELECT tbl.id, grp.name, grp.c, grp.v FROM
(SELECT name, count(*) AS c, max(version) AS v
FROM tbl GROUP BY name
) AS grp, tbl
WHERE grp.name = tbl.name AND grp.version = tbl.version;
I think the question only makes sense if the combination of
name and version are unique in the table.
Dan,
Thanks for that. Yes, each "name/version" combo are unique in the
table. Except for a few minor naming mismatches, the above provided
query works perfectly.
Thanks again.
Jeff
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
