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

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

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


While the combination 'A','X' is in the data twice, it's only selected once.

The same can be achieved with GROUP BY:

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

There may be more that Brian needs:

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

oem	 position	oem	 position
ABC123	 LHF		ABC123	 LHF
ABC124	 RHF			
ABC124	 LHF			
ABC125	 LHR		ABC125	 LHR
ABC126	 RHF		ABC126	 RHF
ABC127	 LHF		ABC127	 LHF
ABC128	 LHF			
ABC128	 RHF			
ABC128	 RHR			
ABC129	 LHR		ABC129	 LHR


 

---- Andy

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

You could be aiming for


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

Chriss

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

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

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

perfect thankyou

So, which solution worked for you

---- Andy

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

Sorry, Andrzejek reply.

Many thanks