Select 1 Record From Multiple Records, Distinct

I have just joined this thread and hope that I will get the solution

I have two tables provider and provider location. I am joining these two tables on the provider_id. In the provider_locn table I have more then locations against any single provider id. I am giving the sample of the tables

PROVIDER Table

Provider_ID PROVIDER_NAME .....
1 ABC
2 XYZ
....
.....
PROVIDER_LOCN table

Provider_ID Provider_Locn Provider_Addr_line1 Provider_Addr_line2
1 1 abc st apt 1
1 2 xyz suite 1
1 3 123 1234
2 1 124 12
2 2 abc 1

I want to select only 1 provider_location against any provider_id

I was trying to use the distinct and group by clause, it works only if I am just selecting the provider_id and location_id, but if I try to get the location address then it selects the multiple location id's and addresses. but I want only 1 location address. I will really appriciate your help.

can you post your query please...

if you want your result to be more specific supply more statements in the where clause..

I am using the command like this, after selecting one location_id I will join this table with the provider table on the basis of the provider_id. I am using the max because I want to select only one location_id against any provider_id, no matter it is max or min.

select prvdr_id,max(locn_id)
from provider_locn
group by prvdr_id

It works on these two columns, but when I try to put locn_addr_line1 and locn_addr_line2 and put these two columns in the group by clause then it again selects more then 1 location_id against any single provider_id

select prvdr_id,max(locn_id),locn_addr_line1,locn_addr_li ne2
from es_provider_locn
group by prvdr_id,locn_addr_line1,locn_addr_line2

I dont know where I am doing wrong or how should I select these columns in the query, so your help will be appriciated please. thx

YES, it will return more than 1 location_id against any single provide_id because your provider_id contains multiple values of locn_addr_line1 & locn_addr_line2. MS SQL does not know what locn_addr_ljne1 he will pick up so MS SQL will return all the locn_addr_line1...

workout the query below to obtain single values for locn_addr_line1 & locn_addr_line2...

select prvdr_id,
max(locn_id),
locn_addr_line1 = (select... from.... where...),
locn_addr_li ne2 = (select... from.... where...)
from es_provider_locn
group by prvdr_id