Solved

special join+ms sql server 2005

Posted on 2011-09-06
2
260 Views
Last Modified: 2012-08-14
hi all i have two tables as follow:

Table A:
A_ID  A_Name OrdeNo
1        JAD              1
2        Samir           2

Table B:
B_ID  B_Name OrderNo  Amount  Date
1       jean         1                 20      10/10/2010
1       Khalil        1                 30      10/10/2011  

I want to join the two tables to get as out put:
A_ID    A_Name    Date

but the problem is the join between the two tables is basic according to OrderNo of Max(Amount).

Please advice.

Regards,
0
Comment
Question by:MKItani
2 Comments
 
LVL 42

Accepted Solution

by:
dqmq earned 250 total points
ID: 36486879
Select A_ID, A_Name, B.Date
from A inner join
(
Select *, row_number() over(partition by OrderNo order by Amount desc) as seq
  from B
) as C on A.OrderNo = C.OrderNo
WHERE C.seq = 1
0
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 250 total points
ID: 36487076

like this, use the row_number() windowing function to order the rows by descending amount per
orderno

select a_id,a_name,b.date
from (
select a_id,a_name,b.date
  ,row_number() over (partition by a.orderno order by b.amount desc) as rn
from tablea as a
lefy outer join tableb as b
on a.orderno=b.orderno
) as x
where rn=1
order by 1

Open in new window

0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

743 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now