freshgrill
asked on
Sql join syntax
ms sql server 2008.
I have a list of item , prices, dates
item price date
a 2.00 4/1/10
a 3.00 6/1/10
b 1.50 3/1/10
b 4.00 8/1/10
When I join a table on item number (e.g. a), how did I only join on the most recent dated line?
select .....join prices on item = item and ....
so prices is join for item a on 6/1/10 only and item b 8/1/10 only.
I have a list of item , prices, dates
item price date
a 2.00 4/1/10
a 3.00 6/1/10
b 1.50 3/1/10
b 4.00 8/1/10
When I join a table on item number (e.g. a), how did I only join on the most recent dated line?
select .....join prices on item = item and ....
so prices is join for item a on 6/1/10 only and item b 8/1/10 only.
;WITH CTE AS (
SELECT
[item], [price], [date],
ROW_NUMBER() OVER (Partition By [item] ORDER BY [date] DESC) RN
FROM YOUR_TABLE_OF_DATA_NAME
)
SELECT *
FROM CTE
WHERE RN = 1
Beat me to it sventhan!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Below is the code and here is the errors:
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'select'.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ')'.
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'select'.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ')'.
select item_num,nav_uom_cost, purchase_date
(
select item_num,nav_uom_cost,purchase_date, row_number() over(partition by item_num order by purchase_date desc) rn
)
where rn =1
or try this
SELECT DISTINCT
T1.item,
T1.price
T1.date
FROM (Select
item
,price
,max(date) as date
from yourtable
group by
item,
price) T1
JOIN yourtable T2
ON T1.item = T2.item
select item_num,nav_uom_cost, purchase_date
(
select item_num,nav_uom_cost,purc
from your table
)
where rn =1
(
select item_num,nav_uom_cost,purc
from your table
)
where rn =1
ASKER
bhess1: I had to name max(date) but everything worked great.
@sventhan: Sorry, the from my table didn't get copied. But even when I tried your query with my name (and table name) I still got the same error messages.
@sventhan: Sorry, the from my table didn't get copied. But even when I tried your query with my name (and table name) I still got the same error messages.
Tried and tested:
create table #test (item varchar(20) null, price int null, [date] datetime)
go
insert into #test
select 'a', 2, '4/1/10'
union all
select 'a', 3, '6/1/10'
union all
select 'b', 1, '3/1/10'
union all
select 'b', 4, '8/1/10'
go
-- Wrap date keyword in []
;WITH CTE AS (
SELECT
[item], [price], [date],
ROW_NUMBER() OVER (Partition By [item] ORDER BY [date] DESC) RN
FROM #test
)
SELECT *
FROM CTE
WHERE RN = 1
go
drop table #test
go
No worries ;). Happy that you got what you want.
sventhan's query's issue was a missing FROM statement and alias. This would have been correct.
select
item_num,
nav_uom_cost,
purchase_date
FROM (
select
item_num,
nav_uom_
cost,purchase_date,
row_number() over(partition by item_num order by purchase_date desc) rn
from your table
) src
where src.rn =1
select
item_num,
nav_uom_cost,
purchase_date
FROM (
select
item_num,
nav_uom_
cost,purchase_date,
row_number() over(partition by item_num order by purchase_date desc) rn
from your table
) src
where src.rn =1
Totally, and it's neater. It was all over by the time I tested and submitted my solution :)
select item,price,date
(
select item,price,date, row_number() over(partition by item order by date desc) rn
)
where rn =1