August 22, 2003 at 7:40 am
I am having difficulty with the select distinct command.
I need to return the whole row, not just the unique column as I need to recreate the deduplicated table.
And select * from ralph where exists(select distinct hometel from ralph) just returns all the rows, not the unique ones.
select distinct hometel from ralph does produce the uniques but just outputs the column, I need the whole row.
Any thoughts appreciated.
Ralph
August 22, 2003 at 7:55 am
It is quite logical that it does not work. How should SQLServer know which row you want to display for each unique hometel?
You could try the following...
SELECT hometel, MIN(field1), MIN(field2)
FROM ralph
GROUP BY hometel
August 22, 2003 at 7:58 am
This may or may not do what you're looking for, but it's a start.
Philip
Assuming tale XXX with rows R1, R2, R3... Rn, and
Assuming R1 is the "key" columnm, where some but not all values in the column are duplicated
1) Assuming you want all rows where R1 is unique, use:
SELECT *
from XXX
where R1 in (select R1
from XXX
group by R1
having count(*) = 1)
2) Assuming you want all rows where R1 is not unique, use:
SELECT *
from XXX
where R1 in (select R1
from XXX
group by R1
having count(*) > 1)
August 22, 2003 at 8:18 am
It almost sounds like you just want
SELECT DISTINCT * FROM ralph
Could you please explain more?
Guarddata-
August 22, 2003 at 8:21 am
Thanks Philip and NPeeters, I was missing the obvious, its been a long day / life.
Your select code works fine, thanks Philip.
Regards
Ralph
August 27, 2003 at 1:38 am
Just a word of WARNING on the query below:
SELECT *
from XXX
where R1 in (select R1
from XXX
group by R1
having count(*) = 1)
It is NOT the same as DISTINCT!!!!
It will ONLY return the rows that are unique EXCLUDING any duplicate pairs so unless you deal with the duplicates to produce a single entry, you will loose these records!!!
Nikki Pratt
Development DBA
Nikki Pratt
Development DBA
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply