×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

return unique rows only

 Forum Search FAQs Links MVPs

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

RE: return unique rows only

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

RE: return unique rows only

(OP)
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

RE: return unique rows only

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

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

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

RE: return unique rows only

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

RE: return unique rows only

You could be aiming for

CODE

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

RE: return unique rows only

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

RE: return unique rows only

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

RE: return unique rows only

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

(OP)
perfect thankyou

RE: return unique rows only

So, which solution worked for you

---- Andy

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

RE: return unique rows only

(OP)

Many thanks

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!