Get first row in an Outer Join

Question

Post reply

Get first row in an Outer Join

Bryan P. Clauss

SSC Eights!

Points: 959
More actions
August 3, 2005 at 9:33 am

#68238
I am selecting data using a Left Outer Join. I only want the first row that the Join returns.
Is there a way to add to the Join a "Top 1" clause?
My SQL:
```
Select File_ID,
       Branch_ID,
       Other_Columns
From   Main_Table
         Left Outer Join Branches
             On  Main_Table.Column = Branches.Column
```
This will return 2 or more rows with the same File_ID. I only want File_ID once. (The Branch_ID is not as important, but I would like to get the first one.)
DISTINCT will not work here, since File_ID and each Branch_ID will be unique.
Thanks,
Bryan Clauss

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply

sushila SSC-Dedicated Points: 35293 More actions · Answer 1

Bryan...as they say...the proof of the pudding is in the eating....

have you tried .....

Select top 1 File_ID,

Branch_ID,

Other_Columns

From Main_Table

Left Outer Join Branches

On Main_Table.Column = Branches.Column

**ASCII stupid question, get a stupid ANSI !!!**

Bryan P. Clauss SSC Eights! Points: 959 More actions · Answer 2

I have not. I thought that Top 1 will only return 1 row.

This is my dataset that is returned:

File_ID

Branch_ID

Data

Data

hghghg

rtrtr

sdfsdf

sdfsd

hghghg

rtrtr

sdfsdf

sdfsd

hghghg

rtrtr

sdfsdf

sdfsd

I would like to only get back 1 row per File_ID.

sushila SSC-Dedicated Points: 35293 More actions · Answer 3

aah - you need a "group by"...thought you did want only one row...give me some time (unless someone else gets to this first)...will be back!

**ASCII stupid question, get a stupid ANSI !!!**

Farrell Keough SSCoach Points: 17414 More actions · Answer 4

This should work. If your "Other_Columns" come from Branches, you will probably need to add those to the sub-select which joined upon.

SELECT [File_ID],

B.Branch_ID,

Other_Columns

FROM Main_Table

LEFT OUTER JOIN( SELECT MAX( Branch_ID) AS Branch_ID, [Column]

FROM Branches GROUP BY [Column]) B ON( Main_Table.[Column] = B.[Column])

I wasn't born stupid - I had to study.

sushila SSC-Dedicated Points: 35293 More actions · Answer 5

since I don't see the ddls of the 2 tables I'm just guessing here...but hopefully you can tweak this and get it working....

Select A.File_ID,

A.Branch_ID,

A.Other_Columns

From Main_Table A

Left Outer Join

(select max(Branch_ID) from Branches group by File_ID) B

On A.Branch_ID = B.Branch_ID

**ASCII stupid question, get a stupid ANSI !!!**

sushila SSC-Dedicated Points: 35293 More actions · Answer 6

...really must learn to type faster...maybe we should all take lessons from remi...

**ASCII stupid question, get a stupid ANSI !!!**

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

There's no lesson to give... think fast and type faster .

sushila SSC-Dedicated Points: 35293 More actions · Answer 8

hi remi...definitely can't think as fast as you...but as for "type faster"...you're typing too fast these days...noticed a slip-up this morning in one of your responses that (obviously) came from typing too fast and not (obviously) because you didn't know what you were doing....

**ASCII stupid question, get a stupid ANSI !!!**

Bryan P. Clauss SSC Eights! Points: 959 More actions · Answer 9

All right, you guys. I feel like a "Newbie" now.

I should have thought of the "sub-Query". Guess I need more caffiene.

Thanks for the help!

sushila SSC-Dedicated Points: 35293 More actions · Answer 10

Just remember Bryan - henceforth if tanking up on caffeine doesn't do the trick - SSC/remi (used interchangeably here) always will!

**ASCII stupid question, get a stupid ANSI !!!**

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 11

Ninja's_RGR'us

SSC Guru

Points: 294069

August 3, 2005 at 11:40 am

#579264

What post?

Bryan P. Clauss SSC Eights! Points: 959 More actions · Answer 12

Just remember Bryan - henceforth if tanking up on caffeine doesn't do the trick - SSC/remi (used interchangeably here) always will!

SSC/remi -> I understand Remi (He has posted to this topic...), but SSC? Is that sushila?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 13

Not enough caffeine yet .

http://qa.sqlservercentral.com

sushila SSC-Dedicated Points: 35293 More actions · Answer 14

No Bryan - I would never elevate myself to that altitude (compare myself to remi - that would be arrogance on my part indeed!)

...stands for SQLSERVERCENTRAL.COM!

remi - it was something to do with old quoteid and new quoteid where you left out one part of it - don't remember the forum now!

**ASCII stupid question, get a stupid ANSI !!!**