How to get the Row Number per Distinct Records?

Row Number	Name	Phone Number
1	John Doe	(555) 123-1221
1	John Doe	(555) 144-9989
2	Smith	(666) 191-1201
3	Jane Doe	(555) 188-0191
3	Jane Doe	(555) 189-0192
3	Jane Doe	(555) 190-0193

Here are the records I get back using a Grouping on "Name". I would like to assign a Row Number for each "Distinct" row. I've tried all the possible aggregate functions with no luck! Can anybody help me with this? Thanks.

Please try something like this:

=RunningValue(Fields!Name.Value & Fields!PhoneNumber.Value ,CountDistinct, Nothing)

|||Thanks. I didn't know that Expression can take multiple values!!! Now I know!

How to get the first occurrence of a record from a table?

i also have a query like this !

select DISTINCT(HospitalName),AvgTotalPatients,TotalPatient from TotalPatients ORDER BY HospitalName,AvgTotalPatients,TotalPatient;

Hospitalnames are unique and AvgTotalPatients is also unique for every hospital now what i get is all records of all hopsitals reason is because in column TotalPatient there are entered many records for every hoapital so it takes all of them, what i want that it should select distinct hospitals with their AVgTotalPatients and only first occurence of TotalPatient.

what should i do? please help urgent

It sounds like you are aggregating data in some fashion.

It might be easier to help you find a good solution if you provided the code that creates [TotalPatients], or at least a better understanding of what/how [AvgTotalPatients] and [TotalPatient] really means.

However, your thinking 'should' be somewhat like this:

Code Snippet

SELECT
HospitalName,
AvgTotalPatients = avg( TotalPatients ),
TotalPatient = min( TotalPatients )
FROM TotalPatients
GROUP BY HospitalName
ORDER BY
HospitalName

|||

Thnx Arnie. I found solution to it 2 days back and it was something like this !

select DISTINCT(TP.HospitalName),TP.AvgTotalPatients,TP.TotalPatient from TotalPatients TP where TotalPatient = ( Select TOP 1(TotalPatient ) from TotalPatients where HospitalName = TP.HospitalName) ORDER BY HospitalName,AvgTotalPatients,TotalPatient;

very complicated one but i got what i wanted

Regards

How to get the description ?

Hi,
I have a SQL that is working fine:

SELECT DISTINCT T1.ATCkod FROM ATC_tot T1
JOIN ATC_tot T2 ON T2.ATCkod = T1.ATCkod and T2.Typ_lakemedel LIKE '%' + @.Kod2 + '%'
JOIN ATC_tot T3 on T3.ATCkod = T1.ATCkod AND T3.Typ_lakemedel LIKE '%' + @.Kod3 + '%'
WHERE T1.Typ_lakemedel = @.Kod
Now I need to have a description together with the ATCkod, tablename ATC columnnameATCdesc. ATC_tot.ATCkod =ATC.ATCkod. I have tried Inner join without success. Any good suggestions ...?

Note that if you do not specify a JOIN type, INNER is the default. I prefer to specify the JOIN type for clarity.
Doesn't this work?

SELECTDISTINCT
T1.ATCkod,
ATC.ATCdesc
FROM
ATC_tot T1
INNER JOIN
ATC_tot T2 ON T2.ATCkod = T1.ATCkod and T2.Typ_lakemedel LIKE '%' + @.Kod2 + '%'
INNER JOIN
ATC_tot T3 on T3.ATCkod = T1.ATCkod AND T3.Typ_lakemedel LIKE '%' + @.Kod3 + '%'
INNER JOIN
ATCkod ATC ON T1.ATCkod = ATC.ATCkod
WHERE
T1.Typ_lakemedel = @.Kod

sql

How to get distinct columns using COALESCE

Hi guys, can you please help me to solve this problem. I have to get distinct row from offering column of xyz table.

I have to get offering1, offering2 from xyz table. But I am getting only offering1. I should not get duplicate rows from XYZ table.

SELECTDISTINCT @.Staging_Off=COALESCE(@.Staging_Off+',','')+ Offering

FROM xyz

WHERE xyz.OfferingNOTIN

(SELECTDISTINCT Offering.Offering

FROM OfferingJoin xyz

ON Offering.Offering= xyz.Offering

AND Offering.SourceSystem= @.SourceSystem

)

That's probably because your nested query (SELECT DISTINCT) is not working on the same row as your outer query. You must make sure your inner query join with a value of the outer query, otherwise they won't be related. Something like this:

SELECT DISTINCT @.Staging_Off=COALESCE(@.Staging_Off +',','')+ OfferingFROM xyzAS X1WHERE X1.OfferingNOT IN(SELECT DISTINCT O.OfferingFROM OfferingAS O, xyzAS X2WHERE O.Offering = X2.OfferingAND X2.SomeId = X1.SomeIdAND O.SourceSystem= @.SourceSystem)

Here I assume that there are some kind of unique id in xyz table that can be used

I think this query could be rewritten in a more clean manner, but I can't do it from the tip of my head. Would need source data and do some trial and erroring ;-) Good luck!