SQL query assistance...

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

SQL query assistance...

jgodfrey
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.



Jeff
Reply | Threaded
Open this post in threaded view
|

Re: SQL query assistance...

Gerry Snyder-4
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. What hav you tried, and what went wrong?

Gerry

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

Reply | Threaded
Open this post in threaded view
|

Re: SQL query assistance...

Andrew Finkenstadt
In reply to this post by jgodfrey
On 7/1/07, Jeff Godfrey <[hidden email]> 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.
Reply | Threaded
Open this post in threaded view
|

Re: SQL query assistance...

jgodfrey
In reply to this post by Gerry Snyder-4

----- Original Message -----
From: "Gerry Snyder" <[hidden email]>
To: <[hidden email]>
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 [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: SQL query assistance...

jgodfrey
In reply to this post by Andrew Finkenstadt

----- Original Message -----
From: "Andrew Finkenstadt" <[hidden email]>
To: <[hidden email]>
Sent: Sunday, July 01, 2007 4:45 PM
Subject: Re: [sqlite] SQL query assistance...


> On 7/1/07, Jeff Godfrey <[hidden email]> 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 [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: SQL query assistance...

jgodfrey
In reply to this post by Andrew Finkenstadt

----- Original Message -----
From: "Andrew Finkenstadt" <[hidden email]>
To: <[hidden email]>
Sent: Sunday, July 01, 2007 4:45 PM
Subject: Re: [sqlite] SQL query assistance...


> On 7/1/07, Jeff Godfrey <[hidden email]> 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 [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: SQL query assistance...

jgodfrey
In reply to this post by jgodfrey

----- Original Message -----
From: "Jeff Godfrey" <[hidden email]>
> Thanks Gerry,
>
> Indeed, I have tried exactly that.

Hmmm... This seems to be a thread for eating crow... ;^)

Hi Gerry,

My actual query is a bit more complex than the sample I posted.  I
thought my posted sample was a valid representation of what I'm
actually trying to do (and I still do), though I had trouble with the
"ID" and "Version" in the result set being constructed from differing
records.  I've added a table containing exactly the data I showed in
my example, and indeed, the query you posted does seem to work exactly
as I requested.

So, I seem to have failed in my efforts to accurately "simplify" the
problem.  Now, I need to figure out what's different between my real
situation and the one I proposed here.

Thanks again for you assistance.

Jeff


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

Reply | Threaded
Open this post in threaded view
|

Re: SQL query assistance...

Dan Kennedy-4
In reply to this post by jgodfrey
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.




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

Reply | Threaded
Open this post in threaded view
|

Re: SQL query assistance...

jgodfrey

----- Original Message -----
From: "Dan Kennedy" <[hidden email]>
To: <[hidden email]>
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 [hidden email]
-----------------------------------------------------------------------------