Removing duplication using distinct

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

Removing duplication using distinct

Author

Topic

sparrow37
Posting Yak Master

148 Posts

Posted - 2009-06-30 : 07:47:30

Hi all,

I have following query which gives duplicate results. I want to remove duplication.

SELECT
l.Pi_name,
l.Pi_LocationBoardId
FROM ((dbo.Pi_LocationBoard l INNER JOIN dbo.Pi_VanpoolStopsBoard sb
ON l.Pi_LocationBoardId = sb.pi_locationid) INNER JOIN dbo.Pi_VanpoolMembership vpm
ON sb.Pi_VanpoolStopsBoardId = vpm.pi_boardstopid )
inner join pi_vanpool vp
on sb.pi_vanpoolid = vp.pi_vanpoolid
WHERE sb.pi_vanpoolid in (
SELECT
vp.Pi_VanpoolId
-- ,vp.Pi_name
FROM dbo.Pi_Vanpool vp INNER JOIN dbo.Pi_VanpoolMembership vpm
ON vp.Pi_VanpoolId = vpm.pi_vanpoolid
WHERE vpm.pi_memberid = @contactId
)

This is the resultset, which i get. Please note duplication in second column. I want only one record selected from 'LEMAY & RIVERSIDE' with max of id ( in first colum)

3157A45E-B397-DC11-AB74-0019B9CD4E0A Park & Ride - I-25 & Harmony
413269E9-AB62-41EE-9F36-0850F953A2B9 LEMAY & RIVERSIDE
413269E9-AB62-41EE-9F36-0850F953A2B9 LEMAY & RIVERSIDE
413269E9-AB62-41EE-9F36-0850F953A2B9 LEMAY & RIVERSIDE
413269E9-AB62-41EE-9F36-0850F953A2B9 LEMAY & RIVERSIDE
AE2C4E9D-0488-4EEC-B4D2-BD88DE21BA59 I-25 & HWY 34 (TARGET)
C15986F0-8F20-4070-82B9-B811D463F8E3 3851 Jefferson Drive
3A131133-5236-4D80-BDE5-04BE9091753B I-25 & HIGHWAY 34

Please help me on this.

Regards,
Asif Hameed

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-06-30 : 07:50:12

use row_number() function

sparrow37
Posting Yak Master

148 Posts

Posted - 2009-06-30 : 07:52:05

how ?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-06-30 : 07:57:24

Try this rather than ROW_NUMBER()


SELECT 
l.Pi_name,
MAX(l.Pi_LocationBoardId)
FROM ((dbo.Pi_LocationBoard l INNER JOIN dbo.Pi_VanpoolStopsBoard sb
ON l.Pi_LocationBoardId = sb.pi_locationid) INNER JOIN dbo.Pi_VanpoolMembership vpm
ON sb.Pi_VanpoolStopsBoardId = vpm.pi_boardstopid )
inner join pi_vanpool vp 
on sb.pi_vanpoolid = vp.pi_vanpoolid
WHERE sb.pi_vanpoolid in ( 
SELECT 
vp.Pi_VanpoolId
--	,vp.Pi_name
FROM dbo.Pi_Vanpool vp INNER JOIN dbo.Pi_VanpoolMembership vpm
ON vp.Pi_VanpoolId = vpm.pi_vanpoolid
WHERE vpm.pi_memberid = @contactId 
)
GROUP BY
l.Pi_name

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

sparrow37
Posting Yak Master

148 Posts

Posted - 2009-06-30 : 07:57:33

I have tried using below query :

SELECT l.Pi_name,
MIN(cast(l.Pi_LocationBoardId as varchar(100)))
FROM dbo.Pi_LocationBoard l INNER JOIN dbo.Pi_VanpoolStopsBoard sb
ON l.Pi_LocationBoardId = sb.pi_locationid INNER JOIN dbo.Pi_VanpoolMembership vpm
ON sb.Pi_VanpoolStopsBoardId = vpm.pi_boardstopid
inner join pi_vanpool vp
on sb.pi_vanpoolid = vp.pi_vanpoolid
WHERE sb.pi_vanpoolid in (
SELECT
vp.Pi_VanpoolId
-- ,vp.Pi_name
FROM dbo.Pi_Vanpool vp INNER JOIN dbo.Pi_VanpoolMembership vpm
ON vp.Pi_VanpoolId = vpm.pi_vanpoolid
WHERE vpm.pi_memberid = @contactId
)
group by l.Pi_name

It worked but is it ok ?

sparrow37
Posting Yak Master

148 Posts

Posted - 2009-06-30 : 07:59:14

max without cast is throwing error. because is uniqueidentifier. and it says MIN/ Max cant be applied to uniqueidentifier.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-06-30 : 08:03:00

[code]
select pi_name,Pi_LocationBoardId
from
(SELECT ROW_NUMBER()OVER(PARTITION BY l.Pi_LocationBoardId ORDER BY l.pi_name)AS rid,
l.Pi_name,
l.Pi_LocationBoardId
FROM ((dbo.Pi_LocationBoard l INNER JOIN dbo.Pi_VanpoolStopsBoard sb
ON l.Pi_LocationBoardId = sb.pi_locationid) INNER JOIN dbo.Pi_VanpoolMembership vpm
ON sb.Pi_VanpoolStopsBoardId = vpm.pi_boardstopid )
inner join pi_vanpool vp
on sb.pi_vanpoolid = vp.pi_vanpoolid
WHERE sb.pi_vanpoolid in (
SELECT
vp.Pi_VanpoolId
-- ,vp.Pi_name
FROM dbo.Pi_Vanpool vp INNER JOIN dbo.Pi_VanpoolMembership vpm
ON vp.Pi_VanpoolId = vpm.pi_vanpoolid
WHERE vpm.pi_memberid = @contactId
)s
where rid = 1
[/code]

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-06-30 : 08:04:56

If it works for you then great.

This is how you would use ROW_NUMBER if you want to compare


SELECT
	r.[name]
	, r.[locationID]
FROM
	(
		SELECT
			ROW_NUMBER() OVER (PARTITION BY l.[PI_Name] ORDER BY l.[PI_LocationBoardID] DESC) AS [pos],
			l.Pi_name AS [name],
			l.Pi_LocationBoardId AS [locationID]
		FROM
			(
				(
				dbo.Pi_LocationBoard l
				INNER JOIN dbo.Pi_VanpoolStopsBoard sb ON l.Pi_LocationBoardId = sb.pi_locationid
				)
				INNER JOIN dbo.Pi_VanpoolMembership vpm ON sb.Pi_VanpoolStopsBoardId = vpm.pi_boardstopid
			)
			inner join pi_vanpool vp on sb.pi_vanpoolid = vp.pi_vanpoolid
		WHERE
			sb.pi_vanpoolid in ( 
				SELECT 
					vp.Pi_VanpoolId
					--	,vp.Pi_name
				FROM
					dbo.Pi_Vanpool vp
					INNER JOIN dbo.Pi_VanpoolMembership vpm ON vp.Pi_VanpoolId = vpm.pi_vanpoolid
				WHERE
					vpm.pi_memberid = @contactId 
			)
	)
	r
WHERE
	r.[pos] = 1

I can't remember if you can order guid's or not. You might have to CAST anyway.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-06-30 : 08:06:45

Hi bklr.

I think OP wanted the highest ID partitioned by Name. Not the other way around.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

sparrow37
Posting Yak Master

148 Posts

Posted - 2009-06-30 : 08:10:52

yes I need highest id by name.

sparrow37
Posting Yak Master

148 Posts

Posted - 2009-06-30 : 08:24:28

which query is final ?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-06-30 : 09:03:06

try them both they are v similar and neither can do harm.

BLKR's partitions by ID and returns the lowest name per id.

Mine partitions by name and returns the highest ID per name.

I think the one I posted follows the rules that you wanted.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

sparrow37
Posting Yak Master

148 Posts

Posted - 2009-06-30 : 09:12:43

ok thanks alot both of you. I am using urs.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources