join ordered distinct query - 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

join ordered distinct query

Author

Topic

maetlqs
Starting Member

1 Post

Posted - 2009-04-07 : 15:26:41

I have two tables and I would like to display the distinct elements of a column from each one, ordered independently, i.e.

Table1
INDEX ID1
1 A
2 B
3 B
4 C
5 D

Table2
INDEX ID2
1 A
2 A
3 B
4 F

I've tried this query:

SELECT DISTINCT A.ID1, B.ID2
FROM TABLE1 AS A
FULL OUTER JOIN
(SELECT DISTINCT ID2
FROM TABLE2
ORDER BY ID2) AS B ON A.ID1 = B.ID2

which gives me:

NULL F
A B
B A
C NULL
D NULL

when I want

A A
B B
C F
D NULL

Any help much appreciated

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-07 : 15:37:44

[code]DECLARE @Table1 TABLE
(
idx INT,
ID1 CHAR(1)
)

INSERT @Table1
SELECT 1, 'A' UNION ALL
SELECT 2, 'B' UNION ALL
SELECT 3, 'B' UNION ALL
SELECT 4, 'C' UNION ALL
SELECT 5, 'D'

DECLARE @Table2 TABLE
(
idx INT,
ID2 CHAR(1)
)

INSERT @Table2
SELECT 1, 'A' UNION ALL
SELECT 2, 'A' UNION ALL
SELECT 3, 'B' UNION ALL
SELECT 4, 'F'

SELECT t1.ID1,
t2.ID2
FROM (
SELECT ID1,
MAX(idx) AS idx
FROM @Table1
GROUP BY ID1
) AS t1
FULL JOIN (
SELECT ID2,
MIN(idx) AS idx
FROM @Table2
GROUP BY ID2
) AS t2 ON t2.idx = t1.idx[/code]

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources