Join - 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

General SQL Server Forums

New to SQL Server Programming

Join

Author

Topic

cardullo4321
Starting Member

40 Posts

Posted - 2011-09-02 : 21:33:09

I have two tables. In Table B, I need to select the most recent by Lookup# in Table A as a derived column. I tried the top one but it does not always pull Year 2011.

Table A

Lookup# Year Rate
21008 2011 .08
21008 2010 .07
41222 2010 .09
41222 2011 .10

Table B

Lookup# Description
21008 Apple
41222 Banana

Final Results Needed

Lookup# Description Rate
21008 Apple .08
41222 Banana .10

Gregory Cardullo

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-02 : 21:39:17

[code]SELECT b.[Lookup#],b.Description,a.Rate
FROM TableB b
INNER JOIN TableA a
ON a.[Lookup#] = b.[Lookup#]
INNER JOIN (SELECT [Lookup#],MAX(Year) AS Latest
FROM tableA
GROUP BY [Lookup#])a1
ON a1.[Lookup#] = a.[Lookup#]
AND a1.Latest = a.Year
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-02 : 21:42:38

if in sql 2005 or above use


SELECT b.[Lookup#],b.Description,a.Rate
FROM TableB b
INNER JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY [Lookup#] ORDER BY [Year] DESC) AS Seq,* FROM TableA) a
ON a.[Lookup#] = b.[Lookup#]
AND Seq=1



SELECT b.[Lookup#],b.Description,a.Rate
FROM TableB b
CROSS APPLY (SELECT TOP 1 * 
             FROM TableA
             WHERE [Lookup#] = b.[Lookup#]
             ORDER BY [Year] DESC) a

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cardullo4321
Starting Member

40 Posts

Posted - 2011-09-03 : 12:25:49

I am getting confused with all the A and Tables A. Can you help me with this information below?

I have two tables called Mapping and WW_Report. I am trying to lookup the most recent rate in the Mapping table and match it to the WW_Report so I can calculate the expense. The mapping table may not have a UBEN that has been used previously so I am okay if NULL is pulled.

WW_Report
UBEN Revenue
21008, 10
21009, 25
21010, 40

Mapping
UBEN Title Year Split
21008, Bear, 2010, .5
21008, Dog, 2011, .7
21009, Cat, 2010, .9
21009, Tiger,2011, .8

Final Results
UBEN Revenue Rate
21008, 10,.7
21009, 25,.8
21010, 40, NULL

Gregory Cardullo

Gregory Cardullo

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-03 : 12:40:24

[code]
SELECT b.UBEN,b.Revenue,a.Split
FROM WW_Report b
LEFT JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY UBEN ORDER BY [Year] DESC) AS Seq,* FROM mapping) a
ON a.UBEN = b.UBEN
AND Seq=1

SELECT b.UBEN,b.Revenue,a.Rate
FROM WW_Report b
OUTER APPLY (SELECT TOP 1 *
FROM Mapping
WHERE UBEN = b.UBEN
ORDER BY [Year] DESC) a
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cardullo4321
Starting Member

40 Posts

Posted - 2011-09-03 : 13:04:23

Thanks alot. I was wondering if I want to lookup more columns from the Mapping table such as Provider, Studio, MinRate, would I have to add it to both select statements? Is this query creating some derived table and then doing a lookup off the derived? Do you have a link or any information on how I can understand how this works. I been using Top 1 to handle most of my lookups.

Gregory Cardullo

cardullo4321
Starting Member

40 Posts

Posted - 2011-09-03 : 13:09:23

I see that you provided two ways of handling the query and not two select statements. So nevermind the first question.

Gregory Cardullo

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-03 : 13:18:09

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources