Results 1 to 4 of 4

Thread: Selecting A Limited Number of Results By a Distinct Value

  1. #1
    Join Date
    Dec 2002
    Posts
    2

    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

  2. #2
    Join Date
    Dec 2002
    Posts
    181
    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

  3. #3
    Join Date
    Dec 2002
    Posts
    2
    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.

  4. #4
    Join Date
    Dec 2002
    Posts
    181
    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
  •