SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Other SQL Server 2008 Topics
 Only show one record per id value
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ismailm
Starting Member

United Kingdom
9 Posts

Posted - 10/16/2012 :  07:19:01  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Germany
8766 Posts

Posted - 10/16/2012 :  07:36:08  Show Profile  Visit webfred's Homepage  Reply with Quote
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.
Go to Top of Page

ismailm
Starting Member

United Kingdom
9 Posts

Posted - 10/16/2012 :  07:39:27  Show Profile  Reply with Quote
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!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/16/2012 :  09:43:17  Show Profile  Reply with Quote
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/


Edited by - visakh16 on 10/16/2012 09:43:40
Go to Top of Page

ismailm
Starting Member

United Kingdom
9 Posts

Posted - 10/16/2012 :  10:29:04  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/16/2012 :  23:43:56  Show Profile  Reply with Quote
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/


Edited by - visakh16 on 10/16/2012 23:44:22
Go to Top of Page

ismailm
Starting Member

United Kingdom
9 Posts

Posted - 10/18/2012 :  12:16:25  Show Profile  Reply with Quote
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!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000