Solved

special join+ms sql server 2005

Posted on 2011-09-06
2
265 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Passing value to a stored procedure 8 116
Caste datetime 2 73
SQL Server Configuration Manager WMI Error 11 51
Using this function 4 53
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

739 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