Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Sql join syntax

Posted on 2011-05-04
12
Medium Priority
?
280 Views
Last Modified: 2012-05-11
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.

0
Comment
Question by:freshgrill
  • 4
  • 4
  • 2
  • +1
12 Comments
 
LVL 18

Expert Comment

by:sventhan
ID: 35693306
This SQL will give you the recent items...

select item,price,date
(
select item,price,date, row_number() over(partition by item order by date desc) rn
)
where rn =1


0
 
LVL 11

Expert Comment

by:brutaldev
ID: 35693336
;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

Open in new window

0
 
LVL 11

Expert Comment

by:brutaldev
ID: 35693340
Beat me to it sventhan!
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 32

Accepted Solution

by:
Brendt Hess earned 2000 total points
ID: 35693341
My preferred query:

SELECT *
FROM items i
INNER JOIN item_prices ip
    ON i.itemID = ip.itemID
INNER JOIN (
    Select item,
        max(date)
    FROM item_prices
    GROUP BY item
    ) filter
    ON i.item = filter.item
    AND ip.date = filter.date
0
 

Author Comment

by:freshgrill
ID: 35693349
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 ')'.

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

Open in new window

0
 
LVL 18

Expert Comment

by:sventhan
ID: 35693384
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

Open in new window

0
 
LVL 18

Expert Comment

by:sventhan
ID: 35693392
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
from your table
)
where rn =1
0
 

Author Closing Comment

by:freshgrill
ID: 35693426
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.
0
 
LVL 11

Expert Comment

by:brutaldev
ID: 35693437
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

Open in new window

0
 
LVL 18

Expert Comment

by:sventhan
ID: 35693441
No worries ;). Happy that you got what you want.
0
 
LVL 32

Expert Comment

by:Brendt Hess
ID: 35693452
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
0
 
LVL 11

Expert Comment

by:brutaldev
ID: 35693489
Totally, and it's neater. It was all over by the time I tested and submitted my solution :)
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question