distinct, union - SQL Server Forums

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

distinct, union

Author

Topic

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-12-12 : 05:30:48

Dear All,
please let me know which combination is better one.

here in our application, there is union as well as distinct.

now i'm replacing the the union and distinct with the suggested replacement by you all.

1) using union and removing distinct.
2) using distinct and union all.
now my doubt is ..is there any other possibility for these?
actually union is also not suggested generally.

please help me in this regard.

Vinod
Even you learn 1%, Learn it with 100% confidence.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-12 : 05:57:31

It depends on the nature of the two SELECTs, and on how many records are involved.
Sometimes UNION is preferred and sometimes DISTINCT is preferred.

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-12 : 06:03:16

If you want to get distinct values then use UNION otherwise use UNION ALL

Madhivanan

Failing to plan is Planning to fail

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-12-12 : 06:21:39

in one perticular procedure, union and distinct are there.
can i simply remove both and put union all?

or

simply remove distinct? and keep union as it is?

which one is correct?
and there are only ten records

Vinod
Even you learn 1%, Learn it with 100% confidence.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-12 : 06:26:04

can you post the code you used?

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-12 : 06:28:13

Ten records as final resultset?

How many UNIONs are there? How many records are returned by each one one SELECT statement?

E 12°55'05.25"
N 56°04'39.16"

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-12-12 : 07:09:53

the code is like this

SELECT @brID = @brID + ';' + BRANCH FROM (SELECT DISTINCT(BRANCH), WBS FROM
(SELECT DISTINCT col010 [BRANCH] FROM TABLE44 -- OPENING BALANCE
UNION
SELECT DISTINCT col003 [BRANCH] FROM TABLE66 --PERIOD LEDGER
UNION
SELECT DISTINCT col003 [BRANCH] FROM TABLE67 --PERIOD SUBLEDGER
UNION
SELECT DISTINCT col002 [BRANCH] FROM TABLE64 --LEDGER
UNION
SELECT DISTINCT col002 [BRANCH] FROM TABLE65) B
INNER JOIN VORG ON B.BRANCH = VORG.ORG_ID
AND WBS LIKE (SELECT WBS FROM VORG WHERE ORG_ID = @OrgID )+'%') A

Vinod
Even you learn 1%, Learn it with 100% confidence.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-12 : 07:38:41

I think you dont need distinct as you used union

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-12 : 07:40:04

You are quite sloppy with table prefixing, so before doing anything more I need to verify that this rewrite
returns the same records as before

SELECT		@brID = @brID + ';' + BRANCH
FROM		(
			SELECT DISTINCT	b.BRANCH,
					vorg.WBS
			FROM		(
						SELECT DISTINCT col010 [BRANCH] FROM TABLE44 -- OPENING BALANCE 
						UNION 
						SELECT DISTINCT col003 [BRANCH] FROM TABLE66 --PERIOD LEDGER 
						UNION 
						SELECT DISTINCT col003 [BRANCH] FROM TABLE67 --PERIOD SUBLEDGER 
						UNION 
						SELECT DISTINCT col002 [BRANCH] FROM TABLE64 --LEDGER 
						UNION 
						SELECT DISTINCT col002 [BRANCH] FROM TABLE65
					) as B 
			INNER JOIN	VORG ON B.BRANCH = VORG.ORG_ID 
			WHERE		vorg.WBS LIKE (SELECT c.WBS FROM VORG as c WHERE c.ORG_ID = @OrgID) + '%'
		) as A

E 12°55'05.25"
N 56°04'39.16"

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-12-12 : 23:06:02

i'm hiding the naming convention of our code. that's why i'm giving table44...like that
and any way both are looking same(peso code and mine)...can i remove distinct? as union is doing the same thing

Vinod
Even you learn 1%, Learn it with 100% confidence.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-13 : 00:54:18

quote:
Originally posted by sunsanvin

i'm hiding the naming convention of our code. that's why i'm giving table44...like that
and any way both are looking same(peso code and mine)...can i remove distinct? as union is doing the same thing

Vinod
Even you learn 1%, Learn it with 100% confidence.

Did you read my previous reply?

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-13 : 04:27:43

[code]DECLARE @Pattern VARCHAR(200)

SELECT @Pattern = WBS + '%'
FROM VORG
WHERE ORG_ID = @OrgID

SELECT @brID = @brID + ';' + a.Branch
FROM (
SELECT DISTINCT b.Branch,
v.WBS
FROM (
SELECT Col010 AS Branch FROM Table44
UNION ALL
SELECT Col003 FROM Table66
UNION ALL
SELECT Col003 FROM Table67
UNION ALL
SELECT Col002 FROM Table64
UNION ALL
SELECT Col002 FROM Table65
) AS b
INNER JOIN VORG AS v ON v.ORG_ID = b.Branch
WHERE v.WBS LIKE @Pattern
) AS a[/code]

E 12°55'05.25"
N 56°04'39.16"

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-12-13 : 05:54:47

YEA...
sorry madhi.....

Vinod
Even you learn 1%, Learn it with 100% confidence.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources