I want to only pull one record from many multiples

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

I want to only pull one record from many multiples

Author

Topic

Matt23
Starting Member

5 Posts

Posted - 2010-07-27 : 13:37:28

I am doing a left join but the table I am joining has duplicate entries sometimes. I cannot delete these entries so I only want to pull one when there is a duplicate for example......

Table A
Ticket# | QTY
1 | 2
2 | 4
3 | 5

Table B
Ticket# | Time
1 | 5:00
2 | 5:00
2 | 5:00
3 | 6:00

When I do a left join it should only be 3 rows and the total qty should be 11. Right now it is pulling the ticket 2 twice so the QTY is 13.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-07-27 : 13:42:07

Something like this.

declare @a table (Ticket# int, QTY int)
insert @a
select 1 , 2
union all select 2 , 4 
union all select 3 , 5

declare @b table (Ticket# int, [Time] varchar(10))
insert @b
select 1 , '5:00'
union all select 2 , '5:00'
union all select 2 , '5:00'
union all select 3 , '6:00'

select sum(a.QTY)
from @a a
left join (select Ticket#,MAX([Time]) as [Time] from @b GROUP BY Ticket#) b on a.Ticket# = b.Ticket#

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources