select query -- get latest version

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

select query -- get latest version

Author

Topic

catwongqjai
Starting Member

2 Posts

Posted - 2005-09-05 : 15:31:17

Hello,

I am going to get a recordset having only latest version for each product_id of the following "prices" table:

product_id price version
--------------------------------
1 100.00 12-Dec-05
1 110.00 15-Mar-05
2 200.00 11-May-05
2 100.00 12-Jun-05
3 130.00 11-Aug-05
3 100.00 22-Apr-05

What I expected to get is:
product_id price version
--------------------------------
1 100.00 12-Dec-05
2 100.00 12-Jun-05
3 130.00 11-Aug-05

I try this:
SELECT sorted.product_id, First(sorted.price) AS FirstOfprice, First(sorted.version) AS FirstOfversion
FROM [SELECT prices.product_id, prices.price, prices.version
FROM prices
ORDER BY prices.version DESC]. AS sorted
GROUP BY sorted.product_id;

However, the subquery sorting function does not seem to work. I always get the "first" record (in the "prices") for each product_id.

Could anybody suggest the suitable query for this?

Cat

triocchu
Starting Member

3 Posts

Posted - 2005-09-05 : 16:24:05

Try this:

SELECT prices.product_id, prices.price, prices.version
FROM prices INNER JOIN [SELECT prices.product_id, Max(prices.version) AS latest
FROM prices
GROUP BY prices.product_id]. AS tempcur ON (prices.version=tempcur.latest) AND (prices.product_id=tempcur.product_id);

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-06 : 00:33:34

Try these also

Select Distinct product_id, price, (select top 1 version from yourTable where product_id=T.product_id)
from yourTable T

or

Select product_id, Price,version from yourTable where version in (select min(version) from yourTable group by product_id)

Madhivanan

Failing to plan is Planning to fail

catwongqjai
Starting Member

2 Posts

Posted - 2005-09-07 : 13:07:35

quote:
Originally posted by madhivanan

Try these also

Select Distinct product_id, price, (select top 1 version from yourTable where product_id=T.product_id)
from yourTable T

or

Select product_id, Price,version from yourTable where version in (select min(version) from yourTable group by product_id)

Madhivanan

Failing to plan is Planning to fail

The second suggested one is working fine (after replacing "min" by "max") and much more simple.

Thanks a lots!!

Cat

triocchu
Starting Member

3 Posts

Posted - 2005-09-09 : 16:50:39

quote:
Originally posted by catwongqjai

quote:
Originally posted by madhivanan

Try these also

Select Distinct product_id, price, (select top 1 version from yourTable where product_id=T.product_id)
from yourTable T

or

Select product_id, Price,version from yourTable where version in (select min(version) from yourTable group by product_id)

Madhivanan

Failing to plan is Planning to fail

The second suggested one is working fine (after replacing "min" by "max") and much more simple.

Thanks a lots!!

Cat

The second query won't give what you expect. criterion for "version" may cause multiple entries for a single product_id.

For example, if the query is applied to the following table:

product_id price version
--------------------------------
1 100.00 12-Dec-05
1 110.00 15-Mar-05
2 200.00 11-May-05
2 100.00 12-Jun-05
1 999.00 12-Jun-05

The result is :

product_id price version
--------------------------------
1 100.00 12-Dec-05
2 100.00 12-Jun-05
1 999.00 12-Jun-05

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-10 : 00:29:46

No The second query is grouped based on Product_id so you will get correct answer and not the one you provided

Madhivanan

Failing to plan is Planning to fail

triocchu
Starting Member

3 Posts

Posted - 2005-09-10 : 05:22:43

quote:
Originally posted by madhivanan

No The second query is grouped based on Product_id so you will get correct answer and not the one you provided

Madhivanan

Failing to plan is Planning to fail

Let's follow the sample table

The "max(version)"s obtained from subquery (of the second query) are
12-Dec-05 and 12-Jun-05, with which, while applying the criterion "WHERE version in ...", the "version" is being compared and nothing to do with product_id again. So two entries for product_id=1 will appear.

I did try that with MS Access 2003 and got the result as I mentioned previously.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-10 : 06:04:12

Well. In that case this will work correctly

Declare @t table(product_id int, price float, version datetime)
insert into @t values(1,'100.00','12-Dec-05')
insert into @t values(1,'110.00','15-Mar-05')
insert into @t values(2,'200.00','11-May-05')
insert into @t values(2,'100.00','12-Jun-05')
insert into @t values(1,'999.00','12-Jun-05')

Select distinct T2.*, T1.Price 
	from @t T1 inner join (Select product_id, max(version) as Version from @t group by product_id) T2
on T1.product_id=T2.product_id and T1.Version=T2.Version

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources