-
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
Last edited by saiko; 12-17-2002 at 10:21 AM.
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|