Return 1 Record from 3 joined tables in SQL

Question

i have this query

select DISTINCT outcome.outcome_id as N'id', SName as N'dir', Summry as N'summry', notes as N'note',category as N'cat', outcome_date as N'date' from outcome
inner join Incomeuser on outcome.outcome_id=Incomeuser.outcome_id
inner join Senders on Incomeuser.Senders_id=Senders.Senders_id 

it's Return 3 Records with the same id All I want is the first Record for each unique ID :) like i have this records :

• 106
• 106
• 106
• 260
• 270
• 260

it should return 106 - 260 - 270, any help ?!

Answer 1

Don't use strings for column aliases. Especially when this is not needed.

There is no such thing as a "first" record in a SQL table or result set. I assume you intend the earliest outcome_date.

You can use top (1) and row_number():

select top (1) o.outcome_id as id, SName as dir, Summry as summry, notes as note, category as cat, outcome_date as date
from outcome o inner join
     Incomeuser iu
     on o.outcome_id = iu.outcome_id inner join
     Senders s
     on iu.Senders_id = s.Senders_id 
order by row_number() over (partition by o.outcome_id order by outcome_date) ;