return unique rows only

[Brianfree]

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

 

[Chris Miller]

You need to change the query to contain a GROUP BY OEM,position or a DISTINCT. Retrofitting a group by into an existing query may not be easy, so the simple solution is using DISTINCT.

Chriss

 

[Brianfree]

Hi, thankyou chris - ive been trying this but im still getting rows back where i dont want them.. Have you an exmaple for me to look at?

many thanks

bf

 

[Chris Miller]

In short you put DISTINCT right after SELECT, as here:

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')

[highlight #FCE94F]Select [b]DISTINCT[/b] oem,position from @test[/highlight]

While the combination 'A','X' is in the data twice, it's only selected once.[highlight #FCE94F][highlight #FCE94F][highlight #FCE94F][/highlight][/highlight][/highlight]

The same can be achieved with GROUP BY:

Select oem,position from @test GROUP BY oem, position

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

 

[Andrzejek]

There may be more that Brian needs:

[pre]
this is what i i need the data
currently get... to be returned like..

oem position oem position
ABC123 LHF ABC123 LHF[red]
ABC124 RHF
ABC124 LHF [/red]
ABC125 LHR ABC125 LHR
ABC126 RHF ABC126 RHF
ABC127 LHF ABC127 LHF[red]
ABC128 LHF
ABC128 RHF
ABC128 RHR [/red]
ABC129 LHR ABC129 LHR


[/pre]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Chris Miller]

You could be aiming for

Select oem,position from @test GROUP BY oem, position HAVING COUNT(*)=1

to remove all OEM,position combinations that occur more than once completely.

Chriss

 

[Andrzejek]

Or...

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

 

[Chris Miller]

Right, but it all depends on the overall query Brianfree already has, how to actually modify it. So without the posted query it's hard to do more than sample code showing how the clauses work essentially. There's much more to know about GROUP BY, but I can't attach an SQL course of everything related to just this subclass of queries alone just in case the solution isn't in there, sorry.

Chriss

 

[Andrzejek]

I agree, but we have only what Brian provided, and that's all what we have to work with.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Brianfree]

perfect thankyou

 

[Andrzejek]

So, which solution worked for you

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Brianfree]

Sorry, Andrzejek reply.

Many thanks