Need help with a Sql query. Was using over with partition, but that won't work in Sybase

Suppose I have a City which is divided into districts.

I have a database table  which is named "City". It includes the id of the district, and last names of people in the district.
As you can see in the sample table the last names occur more than once, as there are many people in the city with the same last name.

What I need is a query which will display the data broken up into the CityId, the DistrictId, and the unique last name of each person in each district. Note that the count of a last name should not occur more than once. So if the same district has 10 people with the last name of "Brown" that name should not be counted more than once. Also if the name was already counted in another district, is should not be counted again in a different district.

City Table :
CityId  |  DistrictId | FamilyName
Austin  |  1          | Brown
Austin  |  1          | Brown
Austin  |  1          | James
Austin  |  1          | Maroni
Austin  |  2          | Maroni
Austin  |  2          | Trevor
Austin  |  2          | Jones
Austin  |  3          | Brown
Austin  |  3          | Greyson
Austin  |  3          | Maroni
Austin  |  3          | Nantula

Query result :

Austin | 1 | Brown
Austin | 1 | James
Austin | 1 | Maroni
Austin | 2 | Trevor
Austin | 2 | Jones
Austin | 3 | Greyson
Austin | 3 | Nantula

I have a sql query which will return the correct result in sql server, but it will not work in Sybase, because Sybase
does not like the "over" command.

Can someone give me a sql query for Sybase which will return the correct result?

Attached is my table scripted out in sql server. Below is the sql query I have been using for sql server, which will not work for

select cityid, districtid, count(familyname) AS DistinctFamilies
from (
           row_number() over(partition by cityid, FamilyName order by districtid) as cf
        , CityId, DistrictId, FamilyName
    from City --WHERE CityId = 'Austin'
    ) sq
where cf = 1
group by cityid, districtid
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Guess this should work, but I didn't get the "count" part:
select CityID, min(DistrictID), FamilyName
from City
group by CityID, FamilyName

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lcohanDatabase AnalystCommented:
I think you could use correlated query below to replace your "...row_number() over(partition by..." in the above:

select  COUNT(DISTINCT DistrictId) FROM City c2 WHERE c2.cityid = c.cityid AND c2.FamilyName = c.FamilyName 
		AND c2.DistrictId <= c.DistrictId
	, CityId, DistrictId, FamilyName
from City c2
order by districtid

Open in new window

brgdotnetcontractorAuthor Commented:
I am working on it some more. I have part of it. I will let you know what happens. Thanks All !
brgdotnetcontractorAuthor Commented:
Thanks so much Gents, you guys are great. I was able to figure it out based upon the suggestion by Qlemo.

Here is what I did, if anyone sees a way to improve it let me know. Qlemo, the use of Min was really cool. I don't know why it works with MIN, I will have to put some more thought into that, when I am fresh.

SELECT t.DiD, COUNT(FamilyName) AS NameCount
  select CityId, min(DistrictID) AS DiD, FamilyName FROM City Group By CityId,FamilyName

) t
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Sybase Database

From novice to tech pro — start learning today.