Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

return unique rows only

Status
Not open for further replies.
Brianfree

Brianfree

Programmer
Feb 6, 2008
220
GB
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
 
Sort by date Sort by votes
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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')

[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:
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
So, which solution worked for you [ponder]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

SashaBuilder3
  • Locked
  • Question
select a single row based on conditions in multiple rows 1
Replies
3
Views
876
SashaBuilder3
SashaBuilder3
peac3
  • Locked
  • Question
Consolidate multiple rows into single row 1
Replies
4
Views
1K
Chris Miller
Chris Miller
peac3
  • Locked
  • Question
split one row into multiple rows... help pls 2
Replies
12
Views
1K
Chris Miller
Chris Miller
ahmedsa2018
  • Locked
  • Question
How to export 20 milion rows to only one file ?
Replies
2
Views
324
JeffModen
JeffModen
briangriffin
  • Locked
  • Question
Calculating differences every two rows
Replies
2
Views
530
kjv1611
kjv1611

Part and Inventory Search

Sponsor

Back
Top