 | |
#21
 
| |||
| |||
|
Re: Selct DISTINCT -
01-29-2008
, 03:38 PM
|
So the problem I have is that there is some duplicate data in TABLE1 and TABLE2, but TABLE1 data always has more data available then in TABLE2, so that is how I know if there is a duplicate to take the first row. |
table (or in result set returned without order by clause). Either you
introduce ordering criteria and then utilize analytical functions to
eliminate *duplicates* or you take the approach - any one of them is as
good as another and remove all *duplicates* except a random one,
utilizing method you was shown by David.
Best regards
Maxim
#22
| |||
| |||
|
|
Let me expand a little on what I am actually doing, I provided a small example in the original post as I thought there might be a simple way to select the first distinct row based on one column. Without specifics, here is how my query is structured: SELECT DISTINCT(ID), NAME, SSN FROM ( * * *SELECT DISTINCT(ID), NAME, SSN FROM TABLE1 * * *UNION ALL * * *SELECT DISTINCT(ID), NAME, SSN FROM TABLE2 ) So the problem I have is that there is some duplicate data in TABLE1 and TABLE2, but TABLE1 data always has more data available then in TABLE2, so that is how I know if there is a duplicate to take the first row. |
SELECT id, name, ssn FROM Table1
UNION
SELECT id, name, ssn FROM Table2
WHERE NOT EXISTS
(SELECT id FROM Table1 WHERE id = Table2.id);
--
David Portas
#23
| |||
| |||
|
|
Let me expand a little on what I am actually doing, I provided a small example in the original post as I thought there might be a simple way to select the first distinct row based on one column. Without specifics, here is how my query is structured: SELECT DISTINCT(ID), NAME, SSN FROM ( * * *SELECT DISTINCT(ID), NAME, SSN FROM TABLE1 * * *UNION ALL * * *SELECT DISTINCT(ID), NAME, SSN FROM TABLE2 ) So the problem I have is that there is some duplicate data in TABLE1 and TABLE2, but TABLE1 data always has more data available then in TABLE2, so that is how I know if there is a duplicate to take the first row. |
SELECT id, name, ssn FROM Table1
UNION
SELECT id, name, ssn FROM Table2
WHERE NOT EXISTS
(SELECT id FROM Table1 WHERE id = Table2.id);
--
David Portas
#24
| |||
| |||
|
|
Let me expand a little on what I am actually doing, I provided a small example in the original post as I thought there might be a simple way to select the first distinct row based on one column. Without specifics, here is how my query is structured: SELECT DISTINCT(ID), NAME, SSN FROM ( * * *SELECT DISTINCT(ID), NAME, SSN FROM TABLE1 * * *UNION ALL * * *SELECT DISTINCT(ID), NAME, SSN FROM TABLE2 ) So the problem I have is that there is some duplicate data in TABLE1 and TABLE2, but TABLE1 data always has more data available then in TABLE2, so that is how I know if there is a duplicate to take the first row. |
SELECT id, name, ssn FROM Table1
UNION
SELECT id, name, ssn FROM Table2
WHERE NOT EXISTS
(SELECT id FROM Table1 WHERE id = Table2.id);
--
David Portas
#25
| |||
| |||
|
|
Let me expand a little on what I am actually doing, I provided a small example in the original post as I thought there might be a simple way to select the first distinct row based on one column. Without specifics, here is how my query is structured: SELECT DISTINCT(ID), NAME, SSN FROM ( * * *SELECT DISTINCT(ID), NAME, SSN FROM TABLE1 * * *UNION ALL * * *SELECT DISTINCT(ID), NAME, SSN FROM TABLE2 ) So the problem I have is that there is some duplicate data in TABLE1 and TABLE2, but TABLE1 data always has more data available then in TABLE2, so that is how I know if there is a duplicate to take the first row. |
SELECT id, name, ssn FROM Table1
UNION
SELECT id, name, ssn FROM Table2
WHERE NOT EXISTS
(SELECT id FROM Table1 WHERE id = Table2.id);
--
David Portas
 |
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.