asked on

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
Sybase.

select cityid, districtid, count(familyname) AS DistinctFamilies
from (
select
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
;
CityTable.txt

ASKER CERTIFIED SOLUTION

Qlemo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

lcohan

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

brgdotnet

ASKER

I am working on it some more. I have part of it. I will let you know what happens. Thanks All !

brgdotnet

ASKER

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
FROM
(
select CityId, min(DistrictID) AS DiD, FamilyName FROM City Group By CityId,FamilyName

) t
GROUP BY t.DiD