Only show one record per id value

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Other SQL Server 2008 Topics

Only show one record per id value

Author

Topic

ismailm
Starting Member

13 Posts

Posted - 2012-10-16 : 07:19:01

Hi guys,

I have a table 'client'.

When I query (for example):
select clientID, clientName from dba.client where country='GB' and clientID in ('1111', '2222', '3333', '4444')

I am getting a result like:

clientID clientname
1111 customer1 a
1111 customer1 b
1111 customer1 c
2222 customer2 a
2222 customer2 b
2222 customer2 c
3333 customer3 a
3333 customer3 b
3333 customer3 c
4444 customer4 a
4444 customer4 b
4444 customer4 c

What I want is for each distinct clientID to only get one record/one clientName (maybe the first row returned for each clientID).

Please help.

Many thanks,

Ismail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-10-16 : 07:36:08

select
clientID,
min(clientName) as clientName
from dba.client
where country='GB'
and clientID in ('1111', '2222', '3333', '4444')
group by clientID

Too old to Rock'n'Roll too young to die.

ismailm
Starting Member

13 Posts

Posted - 2012-10-16 : 07:39:27

quote:
Originally posted by webfred

select
clientID,
min(clientName) as clientName
from dba.client
where country='GB'
and clientID in ('1111', '2222', '3333', '4444')
group by clientID

Too old to Rock'n'Roll too young to die.

Thank you very much, exactly what I was looking for!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-16 : 09:43:17

if you've other columns also to be selected, a better approach would be


SELECT ClientID,ClientName, other columns...
FROM
(
select *,
row_number() over (partition by ClientID ORDER BY ClientName) AS Seq
from dba.client 
where country='GB' 
and clientID in ('1111', '2222', '3333', '4444')

)t
WHERE Seq=1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ismailm
Starting Member

13 Posts

Posted - 2012-10-16 : 10:29:04

quote:
Originally posted by visakh16

if you've other columns also to be selected, a better approach would be

SELECT ClientID,ClientName, other columns...
FROM
(
select *,
row_number() over (partition by ClientID ORDER BY ClientName) AS Seq
from dba.client
where country='GB'
and clientID in ('1111', '2222', '3333', '4444')

)t
WHERE Seq=1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Excellent! Thank you for your help.

Another one, somewhat related.

I want to only show one record but at the moment I am getting a result like 'Customer1 a', 'Customer2 a', 'Customer3 a' etc so I would like only for the first bit of the customer's name i.e. 'Customer1', 'Customer2'...

Is there a way to show only the first word? or first two words (in case a customer has a name with two words)?

Thanks guys, really appreciate your help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-16 : 23:43:56

if words are always separated by single space character you could do like below to get first word


SELECT ClientID,
LEFT(ClientName,CASE WHEN CHARINDEX(' ',ClientName)>0 THEN CHARINDEX(' ',ClientName)-1 ELSE LEN(ClientName) END), 
other columns...
FROM
(
select *,
row_number() over (partition by ClientID ORDER BY ClientName) AS Seq
from dba.client 
where country='GB' 
and clientID in ('1111', '2222', '3333', '4444')

)t
WHERE Seq=1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ismailm
Starting Member

13 Posts

Posted - 2012-10-18 : 12:16:25

quote:
Originally posted by visakh16

if words are always separated by single space character you could do like below to get first word

SELECT ClientID,
LEFT(ClientName,CASE WHEN CHARINDEX(' ',ClientName)>0 THEN CHARINDEX(' ',ClientName)-1 ELSE LEN(ClientName) END),
other columns...
FROM
(
select *,
row_number() over (partition by ClientID ORDER BY ClientName) AS Seq
from dba.client
where country='GB'
and clientID in ('1111', '2222', '3333', '4444')

)t
WHERE Seq=1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thank you very much, works great!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources