sub quary - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

sub quary

Author

Topic

mimic
Starting Member

18 Posts

Posted - 2007-08-31 : 02:40:02

Hi!

Have a problem with sql. I have a table with products prices.
In that table they are many records for every product but with different dates in same field.
I need a quary that gives out the record for every product with max value in the date field.

Can you help?

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2007-08-31 : 02:46:20

please post the structure of the table..
select * from price a inner join
(select product, max(date) date from price
group by product) b
on a.product = b.product and a.date = b.date

bpgupta
Yak Posting Veteran

75 Posts

Posted - 2007-08-31 : 03:28:47

select product, max(date) MaxDate date from price
group by product

mimic
Starting Member

18 Posts

Posted - 2007-08-31 : 04:00:25

My table has the structure like:

int id
int product_id
float price
datetime date
ect...

every product(productid) in this table have multiple records, each with different price and date.
I need the price of every product with the latest price (the highest date).

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-31 : 04:10:51

See how easy it is when you clearly define your problem and suplpy table DDL?

SELECT Product_ID, Price
FROM (
SELECT Product_ID, Price, ROW_NUMBER() OVER (PARTITION BY Product_ID ORDER BY Date DESC) AS RecID
FROM <YourTableNameHere>
) AS d WHERE RecID = 1
ORDER BY Product_ID

E 12°55'05.25"
N 56°04'39.16"

mimic
Starting Member

18 Posts

Posted - 2007-08-31 : 07:01:32

i use sql server 2003 and i get error:
'ROW_NUMBER' is not a recognized function name.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-31 : 07:18:06

There is no such thing as SQL Server 2003. There is a Windows Server 2003.
But since you have posted in a SQL Server 2005 forum, I gave a SQL Server 2005 answer.

A SQL Server 2000 solution is provided above, by shallu1_gupta.

E 12°55'05.25"
N 56°04'39.16"

mimic
Starting Member

18 Posts

Posted - 2007-08-31 : 07:48:46

Yes, sql 2000.
My mistake.

Thank you all for the answers!

mimic
Starting Member

18 Posts

Posted - 2007-09-03 : 08:02:41

But now if i try to create view from this query

select * from price a inner join
(select product, max(date) date from price
group by product) b
on a.product = b.product and a.date = b.date

this error is shown:

Column names in each view or function must be unique. Column name 'product ' in view or function 'view_name' is specified more than once.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-03 : 08:07:06

The error message says it all!

Remove the * and put in a real column list instead.

select a.product, b.date, a.col1, a.col2, a.col3...
from price a inner join
(select product, max(date) date from price
group by product) b
on a.product = b.product and a.date = b.date

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources