Prev: Possible to select a sample rows based on a column value?
Next: Query for merge column
From: Polaris431 on 13 May 2010 07:04
Hi,

I have the following recordset:

5F453674-DA64-4BB1-8947-19493439927B 1 CamCard Beta 1
5F453674-DA64-4BB1-8947-19493439927B 2 CamCard
EB70E4BD-CC44-4AD0-A000-6DD92CD44740 3 Business Cards

Column names are as follows:
Column 1: ProductID
Column 2: VersionNumber
Column 3: ProductName

I need to write sql that will return only the highest version number
for each product. So using the sample data, only records 2 and 3 would
be returned. I can't figure out how to do this. I believe it has
something to do with grouping and possibly the Max function. Any
ideas?

Thanks a lot
Johann
From: Uri Dimant on 13 May 2010 08:13
SELECT ProductID,MAX(VersionNumber) FROM
tbl GROUP BY ProductID

"Polaris431" <johannblake(a)gmail.com> wrote in message
news:073ebc04-6628-4b87-ae42-2f60ac7fe174(a)p2g2000yqh.googlegroups.com...
> Hi,
>
> I have the following recordset:
>
> 5F453674-DA64-4BB1-8947-19493439927B 1 CamCard Beta 1
> 5F453674-DA64-4BB1-8947-19493439927B 2 CamCard
> EB70E4BD-CC44-4AD0-A000-6DD92CD44740 3 Business Cards
>
> Column names are as follows:
> Column 1: ProductID
> Column 2: VersionNumber
> Column 3: ProductName
>
> I need to write sql that will return only the highest version number
> for each product. So using the sample data, only records 2 and 3 would
> be returned. I can't figure out how to do this. I believe it has
> something to do with grouping and possibly the Max function. Any
> ideas?
>
> Thanks a lot
> Johann


From: Gert-Jan Strik on 13 May 2010 08:28
Johann,

Don't forget to thank Uri for doing your homework :-)

--
Gert-Jan


Polaris431 wrote:
>
> Hi,
>
> I have the following recordset:
>
> 5F453674-DA64-4BB1-8947-19493439927B 1 CamCard Beta 1
> 5F453674-DA64-4BB1-8947-19493439927B 2 CamCard
> EB70E4BD-CC44-4AD0-A000-6DD92CD44740 3 Business Cards
>
> Column names are as follows:
> Column 1: ProductID
> Column 2: VersionNumber
> Column 3: ProductName
>
> I need to write sql that will return only the highest version number
> for each product. So using the sample data, only records 2 and 3 would
> be returned. I can't figure out how to do this. I believe it has
> something to do with grouping and possibly the Max function. Any
> ideas?
>
> Thanks a lot
> Johann
From: Polaris431 on 13 May 2010 08:35
Thanks Uri but...

I also need to include the field ProductName in the result. The
example you gave will only work if I leave out the ProductName. The
problem is, is that the data comes from a resultset and not a table.
In all the examples I've seen, tables have been used and the name of
the table is used a second time using a join. I thought that I could
use a CTE to store my resultset and reference that in a join, but it
appears that you can only have one CTE in a stored proc.

Any more ideas?
Thanks
Johann
From: Uri Dimant on 13 May 2010 08:52
Hi
WITH cte
AS
(
SELECT ProductID,ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY
VersionNumber DESC )rn,
ProductName FROM

) SELECT ProductID,ProductName FROM cte WHERE rn=1




"Polaris431" <johannblake(a)gmail.com> wrote in message
news:5528236f-b63a-4480-8757-a842a0585319(a)k17g2000yqf.googlegroups.com...
> Thanks Uri but...
>
> I also need to include the field ProductName in the result. The
> example you gave will only work if I leave out the ProductName. The
> problem is, is that the data comes from a resultset and not a table.
> In all the examples I've seen, tables have been used and the name of
> the table is used a second time using a join. I thought that I could
> use a CTE to store my resultset and reference that in a join, but it
> appears that you can only have one CTE in a stored proc.
>
> Any more ideas?
> Thanks
> Johann


 |  Next  |  Last
Pages: 1 2 3
Prev: Possible to select a sample rows based on a column value?
Next: Query for merge column