Selecting A Limited Number of Results By a Distinct Value

Hopefully someone can help with this.

To get to the heart of the problem I'll use a greatly simplfied example.
Let's say I have a table with two fields: city|member

There are, of course, many members in each city. What I'd lke to do is a *single* SELECT statement that pulls only 5 or less members for all DISTINCT cities in the table.

Thanks
Saiko

Saiko,
If I am understanding your request, this should do what you want:


select city, member from citytable where member in (
select top 5 member from citytable t2
where citytable.city = t2.city order by member)

Jeff

Thanks. That might be a solution but I neglected to mention that I'm using MySQL which currently desn't take subqueries.

Even so I am learning something from your reply. Is citytable.t2 an automatically created temp table?
Saiko

Saiko,
Citytable t2 is the city table given an alias so it can be compared in the subquery against the same table in the outer query. This is known as a correlated subquery in SQL Server. Short of some kind of subquery, I don't know that you can pull this one off using a single select statment. I also am not aware that mySql supports the TOP keyword, which is specific to SQL Server.

You might try handling it in your application as opposed to your sql query. Here is some pseudo code:

open.recordset (a distinct list of cities)
while not recordset eof
open.nextrecordset (a list of all members for that city in order)
for i = 1 to 5
display a member
next
wend


Jeff