Results 1 to 10 of 10

Thread: Select Top

  1. #1
    Join Date
    Oct 2002
    Location
    Houston, TX
    Posts
    34

    Unanswered: Select Top

    OK, this sounds really stupid!!

    When I use the Select Top 10 command in a SQL statement, how do I display the results?

    ex: SQL="Select top 10 zipcode From datatbl"

    How do I display all 10 zipcodes?

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    What context are you trying to run this sql statement - in vb code or as an access query ?

  3. #3
    Join Date
    Oct 2002
    Location
    Houston, TX
    Posts
    34
    This is a Access DB.

    Actually my question was misleading.

    I want to display the number of results from a SELECT TOP 10


    sql = "SELECT TOP 10 zip FROM datatbl"

    I not only want to know what the zip codes were but how many of them. I guess what I am trying to do is return the count of the top 10.

    I think I need something like this:

    sql = "SELECT COUNT(zip) AS qtyzip FROM(SELECT TOP 10 zip FROM datatbl)"

    But this does not work. How would I write some thing like this? I want the top 10 zip codes and how many of them there are. (nice grammar)

    Last edited by rob7765; 01-08-03 at 10:00.

  4. #4
    Join Date
    Feb 2002
    Posts
    2,232
    It really depends on what you are basing your top 10 on. The following is an example using northwind for the top 10 customers based on number of orders:

    select top 10 customerid, count(*) from orders group by customerid order by count(*) desc

  5. #5
    Join Date
    Oct 2002
    Location
    Houston, TX
    Posts
    34
    I want the 10 zip codes from the database with the most people in the zipcode and I want to know how many are in each of those 10 zipcodes.

    I have realized that I cannot use the top 10 command.

    Anyone know how to do this?

  6. #6
    Join Date
    Feb 2002
    Posts
    2,232
    You can do something like this:

    select top 10 zipcode, count(*) from table group by zipcode order by count(*) desc

  7. #7
    Join Date
    Oct 2002
    Location
    Houston, TX
    Posts
    34
    Originally posted by rnealejr
    You can do something like this:

    select top 10 zipcode, count(*) from table group by zipcode order by count(*) desc

    What does my output look like?
    This is what I have currently:
    <table border="1" width="300"><tr><td align = "center">
    <%
    DO WHILE NOT recordset.EOF
    %>
    <tr>
    <td><% = recordset("zip") %></td>
    <td>?????????????? I want to show the number of records for each zipcode here???????????????</td>
    </tr>
    <%
    recordset.MoveNext
    Loop
    %>
    Last edited by rob7765; 01-08-03 at 14:53.

  8. #8
    Join Date
    Feb 2002
    Posts
    2,232
    You can either use the ordinal value so:

    recordset(0) = zip
    recordset(1) = count

    or the name the field in the query and use the name - ordinal reference is much faster for ado than a name reference.

  9. #9
    Join Date
    Oct 2002
    Location
    Houston, TX
    Posts
    34
    Originally posted by rnealejr
    You can either use the ordinal value so:

    recordset(0) = zip
    recordset(1) = count

    or the name the field in the query and use the name - ordinal reference is much faster for ado than a name reference.

    Thank you very much!!! I had to reverse the line to read:

    count = recordset(1)



    <% = count %>

  10. #10
    Join Date
    Feb 2002
    Posts
    2,232
    You are welcome. I have to be cautious when I use an equal sign in a literal context and not a programming context. I meant that the ordinal 0 refers to zipcode and the ordinal 1 refers to the count - anyway, you were able to obtain my meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •