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

brgdotnet
brgdotnet used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
"Batchelor", Developer and EE Topic Advisor
Top Expert 2015
Commented:
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

lcohanDatabase Analyst

Commented:
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

brgdotnetcontractor

Author

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

Author

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

) t
GROUP BY t.DiD

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial