return unique rows only
return unique rows only
(OP)
hello, i have data that is returned on mssql but i need to remove the rows where there are multiple positions
this is what i currently get...
oem, position
ABC123, LHF
ABC124, RHF
ABC124, LHF
ABC125, LHR
ABC126, RHF
ABC127, LHF
ABC128, LHF
ABC128, RHF
ABC128, RHR
ABC129, LHR
i need the data to be returned like..
oem, position
ABC123, LHF
ABC125, LHR
ABC126, RHF
ABC127, LHF
ABC129, LHR
please can you help with example?
regards,
BF
this is what i currently get...
oem, position
ABC123, LHF
ABC124, RHF
ABC124, LHF
ABC125, LHR
ABC126, RHF
ABC127, LHF
ABC128, LHF
ABC128, RHF
ABC128, RHR
ABC129, LHR
i need the data to be returned like..
oem, position
ABC123, LHF
ABC125, LHR
ABC126, RHF
ABC127, LHF
ABC129, LHR
please can you help with example?
regards,
BF
RE: return unique rows only
Chriss
RE: return unique rows only
many thanks
bf
RE: return unique rows only
CODE
Declare @test as table(id integer, oem nchar(1), position nchar(1)) Insert into @test values (1,'A','X'),(2,'B','Y'),(3,'C','Z'),(4,'A','X') Select DISTINCT oem,position from @test
While the combination 'A','X' is in the data twice, it's only selected once.
The same can be achieved with GROUP BY:
CODE
If you have more columns please state so, each of the solutions has further implications on when and how it works, I can't explain them all to you at once. But most cases are covered by adding DISTINCT always is for the whole row and thus if you would SELECT * or add the id you would get 'A','X' double, as it's still distinctly different records by ids 1 and 4.
Also in GROUP BY queries, any additional columns you don't group by cannot be in the result, you can only have further columns with aggregates.
Chriss
RE: return unique rows only
---- Andy
"Hmm...they have the internet on computers now"--Homer Simpson
RE: return unique rows only
CODE
to remove all OEM,position combinations that occur more than once completely.
Chriss
RE: return unique rows only
SELECT OEM, POSITION
FROM MyTable
WHERE OEM IN (
select OEM
FROM MyTable
HAVING COUNT(OEM) = 1
GROUP BY OEM)
'HAVING COUNT(*)=1' will not work, you count both: OEM and POSITION together
---- Andy
"Hmm...they have the internet on computers now"--Homer Simpson
RE: return unique rows only
Chriss
RE: return unique rows only
---- Andy
"Hmm...they have the internet on computers now"--Homer Simpson
RE: return unique rows only
RE: return unique rows only
---- Andy
"Hmm...they have the internet on computers now"--Homer Simpson
RE: return unique rows only
Many thanks