Query with Distincts ! - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Query with Distincts !

Author

Topic

sqlilliterate
Starting Member

40 Posts

Posted - 2008-01-06 : 22:23:04

Is there a way in SQL 2005, so that i get the results using distinct in each of the columns in my select query.

Select distinct Col1, distinct Col2, distinct Col3...
from Table

I know, the above syntax is wrong. But this is my requirement :(
Help me with a query structure to achieve it.

Thanks...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-06 : 22:49:56

[code]
select distinct Col1, Col2, Col3
from Table
[/code]

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-06 : 23:07:42

You wont get distinct values of each columns by this method. It will only return the distinct values of the combination (Col1, Col2, Col3). To get individual distinct values you need to do strip out values out of table then join them together.

DECLARE @DistCol1 TABLE
(
ID int IDENTITY(1,1),
Col1 <datatype>
)

DECLARE @DistCol2 TABLE
(
ID int IDENTITY(1,1),
Col2 <datatype>
)

DECLARE @DistCol3 TABLE
(
ID int IDENTITY(1,1),
Col3 <datatype>
)

INSERT INTO @DistCol1 (Col1)
SELECT DISTINCT Col1
FROM YourTable

INSERT INTO @DistCol2 (Col2)
SELECT DISTINCT Col2
FROM YourTable

INSERT INTO @DistCol3 (Col3)
SELECT DISTINCT Col3
FROM YourTable

SELECT t1.Col1,t2.Col2,t3.Col3
FROM @DistCol1 t1
LEFT OUTER JOIN @DistCol2 t2
ON t2.ID=t1.ID
LEFT OUTER JOIN @DistCol3 t3
ON t3.ID=t1.ID

I cant really understand the need for this requirement though.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-07 : 01:35:00

quote:
Originally posted by sqlilliterate

Is there a way in SQL 2005, so that i get the results using distinct in each of the columns in my select query.

Select distinct Col1, distinct Col2, distinct Col3...
from Table

I know, the above syntax is wrong. But this is my requirement :(
Help me with a query structure to achieve it.

Thanks...

Post some sample data with expected result

Madhivanan

Failing to plan is Planning to fail

sqlilliterate
Starting Member

40 Posts

Posted - 2008-01-07 : 02:48:32

Here's some data...


Col1	Col2	Col3
----------------------
A	1	AB
B	1	AC
C	0	AB
A	1	AC
A	0	BC	
A	0	BC
A	1	AB
B	0	AC
C	1	AB
C	0	AC
B	1	BC
B	0	BC
A	1	BC
C	0	AC
D	1	AB

Expected Result ...
i.e. Distinct of each column n sorting is not required.


Col1	Col2	Col3
----------------------
A	0	AB
B	1	AC
C	NULL    BC
D	NULL	NULL

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-07 : 02:58:28

[code]DECLARE @sample TABLE
(
Col1 CHAR(1),
Col2 int,
Col3 CHAR(2)
)
INSERT INTO @sample
SELECT 'A', 1, 'AB' UNION ALL
SELECT 'B', 1, 'AC' UNION ALL
SELECT 'C', 0, 'AB' UNION ALL
SELECT 'A', 1, 'AC' UNION ALL
SELECT 'A', 0, 'BC' UNION ALL
SELECT 'A', 0, 'BC' UNION ALL
SELECT 'A', 1, 'AB' UNION ALL
SELECT 'B', 0, 'AC' UNION ALL
SELECT 'C', 1, 'AB' UNION ALL
SELECT 'C', 0, 'AC' UNION ALL
SELECT 'B', 1, 'BC' UNION ALL
SELECT 'B', 0, 'BC' UNION ALL
SELECT 'A', 1, 'BC' UNION ALL
SELECT 'C', 0, 'AC' UNION ALL
SELECT 'D', 1, 'AB'

SELECT Col1, Col2, Col3
FROM (
SELECT row_no = row_number() OVER (ORDER BY Col1),
Col1
FROM @sample
GROUP BY Col1
) c1
FULL OUTER JOIN
(
SELECT row_no = row_number() OVER (ORDER BY Col2),
Col2
FROM @sample
GROUP BY Col2
) c2 ON c1.row_no = c2.row_no
FULL OUTER JOIN
(
SELECT row_no = row_number() OVER (ORDER BY Col3),
Col3
FROM @sample
GROUP BY Col3
) c3 ON c1.row_no = c3.row_no
/*
Col1 Col2 Col3
---- ----------- ----
A 0 AB
B 1 AC
C NULL BC
D NULL NULL

(4 row(s) affected)
*/[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources