Display top 1 for each group in sql server

"top 1" by which order?
I assume CLIENT_ROLE_MANAGER_EMAIL

SELECT   t.CLIENT_ID
, t.CLIENT_ROLE_CODE
, t.CLIENT_ROLE_DESCRIPTION
, t.CLIENT_ROLE_MANAGER_EMAIL
, t.CLIENT_ROLE_MANAGER_NAME
, t.CLIENT_ROLE_MANAGER_GPN
, t.COUNTRY_CODE
, t.LAST_UPDATE_DATE
, t.MANAGING_COUNTRY_CODE
FROM  GFIS_CLIENT_ROLE t
WHERE t.client_role_code = 'cap' 
and t.country_code = 'usa' 
and t.CLIENT_ID IN (60106555      ,
60473293      ,
60106612      ,
60246330      ,
60012325 ) 
AND t.CLIENT_ROLE_MANAGER_EMAIL = ( select top 1 i.CLIENT_ROLE_MANAGER_EMAIL FROM  GFIS_CLIENT_ROLE i WHERE i.client_id = t.client_id )
order by t.client_id
 , t.CLIENT_ROLE_MANAGER_EMAIL

Select allOpen in new window

whatever output we get for the first query...i want the top 1 for each distinct client_id for eg..for the above query i get



60012325      CAP      Manag Country Account Partner      a@b.com
60012325      CAP      Manag Country Account Partner      c@g.com
60012325      CAP      Manag Country Account Partner      f@y.com
60106555      CAP      Manag Country Account Partner      g@g.com
60106612      CAP      Manag Country Account Partner      k@g.com


i want this to be the output


60012325      CAP      Manag Country Account Partner      a@b.com

60106555      CAP      Manag Country Account Partner      g@g.com

60106612      CAP      Manag Country Account Partner      k@g.com

top 1 row for each distinct client id

>i want the top 1 for each distinct client_id
I repeat: top 1 based on what "order"?

first client_id   then client_role_manager_email

I don't think TOP makes much sense in this instance.... what you have is a GROUP BY situation:

Try this:

SELECT   g.CLIENT_ID, g.CLIENT_ROLE_CODE, g.CLIENT_ROLE_DESCRIPTION, g.CLIENT_ROLE_MANAGER_EMAIL, g.CLIENT_ROLE_MANAGER_NAME, g.CLIENT_ROLE_MANAGER_GPN, g.COUNTRY_CODE, g.LAST_UPDATE_DATE, g.MANAGING_COUNTRY_CODE
 
FROM GFIS_CLIENT_ROLE g
 
-- subquery to find only the "first" (minimum) email for each CLIENT_ID
INNER JOIN
(SELECT CLIENT_ID, MIN(CLIENT_ROLE_MANAGER_EMAIL) z1
FROM  GFIS_CLIENT_ROLE
GROUP BY CLIENT_ID) FIRST_EMAIL 
      ON g.CLIENT_ID = FIRST_EMAIL.CLIENT_ID
      AND g.CLIENT_ROLE_MANAGER_EMAIL = FIRST_EMAIL.z1
 
WHERE     (g.client_role_code = 'cap' and g.country_code = 'usa' and g.CLIENT_ID IN (60106555      ,
60473293      ,
60106612      ,
60246330      ,
60012325.....      ) order by g.client_id

Select allOpen in new window

>I don't think TOP makes much sense in this instance..
actually, your code will do the same results as mine :)

> actually, your code will do the same results as mine :)
Sorry -- I didn't phrase that well... What I meant was that I find my code to be more intuitive. I would imagine that you likely feel the opposite!
Hopefully "angireddy" will find one or the other to his/her liking.