Professional Web Applications Themes

SELECT COUNT, but only on the last 100 entries - ASP Database

Dan Nash wrote:  The answer (as always) depends on your database type and version. Please let us know this information upfront. Bob Barrows -- Microsoft MVP -- ASP/ASP.NET Please reply to the newsgroup. The email account listed in my From header is my spam trap, so I don't check it very often. You will get a quicker response by posting to the newsgroup....

  1. #1

    Default Re: SELECT COUNT, but only on the last 100 entries

    Dan Nash wrote: 
    The answer (as always) depends on your database type and version. Please let
    us know this information upfront.

    Bob Barrows
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.


    Bob Guest

  2. #2

    Default Re: SELECT COUNT, but only on the last 100 entries

    Access 2k3 on the devlopment machine, but it will be SQL Server on launch
    system. I'm using ordinary ASP to construct the Query, not using stored
    proceedures or anything.

    HTH


    Dan

    "Bob Barrows [MVP]" wrote:
     
    > The answer (as always) depends on your database type and version. Please let
    > us know this information upfront.
    >
    > Bob Barrows
    > --
    > Microsoft MVP -- ASP/ASP.NET
    > Please reply to the newsgroup. The email account listed in my From
    > header is my spam trap, so I don't check it very often. You will get a
    > quicker response by posting to the newsgroup.
    >
    >
    >[/ref]
    Dan Guest

  3. #3

    Default Re: SELECT COUNT, but only on the last 100 entries

    OK, you need to tell us about your table structure, including any keys. And
    tell us how you define the "last 100 entries". Is there a date/time field to
    order the entries by? You need to understand that in a relational database,
    the order in which rows are inserted may not correspond to the order in
    which the rows are stored. You have to use an ORDER BY clause to guarantee
    the order in which the records will be retrieved by a sql statement.
    Therefore, we need to know the field or fields which can be used to sort the
    data so that the "last 100 entries" can be identified.

    Bob Barrows

    Dan Nash wrote: 
    >> The answer (as always) depends on your database type and version.
    >> Please let us know this information upfront.
    >>
    >> Bob Barrows
    >> --
    >> Microsoft MVP -- ASP/ASP.NET
    >> Please reply to the newsgroup. The email account listed in my From
    >> header is my spam trap, so I don't check it very often. You will get
    >> a quicker response by posting to the newsgroup.[/ref][/ref]

    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.


    Bob Guest

  4. #4

    Default Re: SELECT COUNT, but only on the last 100 entries

    Bob,

    Ok, I'll try and give you some more info.

    I have 3 tables, each setup with keys etc.

    Questions - this holds the list of questions (subject, question) with a
    question_id field (key). It also has a subcategory_id field, which relates
    to...

    SubCategories - info about sub categories (subcategory name) with a
    subcategory_id field (key). It also has a category_id field, which relates
    to...

    ...you guessed it, Categories - info about categories (category name) with a
    category_id (key).

    So basically, a Category can have a number of SubCategories, which can have
    a number of Questions. I've got all the relationships setup in Access, so I
    can click the + buttons and drill down and see the data.

    Right, from ASP, I want to be able to get the last 100 Questions (SELECT *
    FROM Questions ORDER BY question_id DESC). Simple enough.

    But what I really want it to do is give me a descending list of the
    Categories, based on how many Questions are in it. SO basically... Category
    1.. 23 questions in 5 subcategories, Category 2, 20 questions in 7
    subcategories, Category 3.. 19 questions in 3 subcategories, and so on.

    Does that make sense?

    I've almost got it working in ASP using ordinary SELECT queries, arrays and
    bubble sort, but I'm sure SQL can do it better and faster, and I *think* it
    has something to do with SELECT COUNT, but I'm unsure of how that works, or
    if that's even right.

    Any help would be appreciated, this is the last part of a large project!
    (how come we always get stuck on the last bit? :)

    Cheers


    Dan

    "Bob Barrows [MVP]" wrote:
     [/ref]
    >
    > --
    > Microsoft MVP -- ASP/ASP.NET
    > Please reply to the newsgroup. The email account listed in my From
    > header is my spam trap, so I don't check it very often. You will get a
    > quicker response by posting to the newsgroup.
    >
    >
    >[/ref]
    Dan Guest

  5. #5

    Default Re: SELECT COUNT, but only on the last 100 entries

    Dan Nash wrote: 

    SELECT TOP 100 * FROM Questions ORDER BY question_id DESC
     

    First of all, forget about using SELECT * in production code. It's OK for
    quick testing, but for production, you want to be explicit.

    This should work in both SQL Server and Access (untested):

    SELECT c.[category name], Count(*) As CategoryCount
    FROM (
    (SELECT TOP 100 subcategory_id FROM Questions
    ORDER BY question_id DESC) As q INNER JOIN SubCategories s
    ON q.subcategory_id = s.subcategory_id)
    INNER JOIN Categories c ON s.category_id = c.category_id
    GROUP BY c.[category name]
    ORDER BY CategoryCount DESC


    Bob Barrows
    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"


    Bob Guest

  6. #6

    Default Re: SELECT COUNT, but only on the last 100 entries

    Bob,

    Thanks for that. Just the SQL statement has shown me how to do a few things
    so that's cool!

    I've got a few problems though, whenever I try and run the statement I get
    the error "Too few parameters. Expected 2." So, because the Count, InnerJoin
    stuff is new to me, I thought I'd split it up a bit...

    "SELECT TOP 100 questions.subcategory_id FROM questions ORDER BY
    questions.question_id DESC" works fine

    "SELECT subcategories.[subcategory_name], Count(*) As SubCatCount
    FROM((SELECT TOP 100 questions.subcategory_id FROM questions ORDER BY
    questions.question_id DESC) AS q INNER JOIN subcategories s ON
    q.[subcategory_id] = s.[subcategory_id]) ORDER BY
    subcategory.[subcategory_id] DESC", which I assume will give me a list of the
    last 100 questions and their subcategories, I get the same error "Too few
    parameters. Expected 2."

    I've had a look at INNER JOIN in the help, and had a go at doing a simple
    one, and I get the same error yet again. The code from that was...

    "SELECT subcategies.subcategory_name FROM subcategories INNER JOIN questions
    q ON q.subcategory_id = subcategories.subcategory_id ORDER BY
    subcategory.subcategory_name DESC"

    Er.. help appreciated!

    Cheers


    Dan



    "Bob Barrows [MVP]" wrote:
     
    >
    > SELECT TOP 100 * FROM Questions ORDER BY question_id DESC

    >
    > First of all, forget about using SELECT * in production code. It's OK for
    > quick testing, but for production, you want to be explicit.
    >
    > This should work in both SQL Server and Access (untested):
    >
    > SELECT c.[category name], Count(*) As CategoryCount
    > FROM (
    > (SELECT TOP 100 subcategory_id FROM Questions
    > ORDER BY question_id DESC) As q INNER JOIN SubCategories s
    > ON q.subcategory_id = s.subcategory_id)
    > INNER JOIN Categories c ON s.category_id = c.category_id
    > GROUP BY c.[category name]
    > ORDER BY CategoryCount DESC
    >
    >
    > Bob Barrows
    > --
    > Microsoft MVP - ASP/ASP.NET
    > Please reply to the newsgroup. This email account is my spam trap so I
    > don't check it very often. If you must reply off-line, then remove the
    > "NO SPAM"
    >
    >
    >[/ref]
    Dan Guest

  7. #7

    Default Re: SELECT COUNT, but only on the last 100 entries

    Dan Nash wrote: 

    That means the statement includes field or table names (2) that are either
    misspelled or don't exist.

     

     

    Again, non-existing or messpelled fiels names. The error has nothing to do
    with the joins.
     

    I can't help without creating your database tables exactly. If the above
    explanations don't help, export the three tables to a new database, zip it
    up, and send it to my email address.

    Bob Barrows
    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"


    Bob Guest

Similar Threads

  1. select last 4 entries
    By johnbennett in forum Coldfusion - Getting Started
    Replies: 2
    Last Post: July 11th, 07:01 PM
  2. getting count less 0 entries
    By ranger in forum Coldfusion Database Access
    Replies: 5
    Last Post: March 15th, 09:05 AM
  3. [mysql] SELECT COUNT(*) question
    By Floris van den Berg in forum PHP Development
    Replies: 6
    Last Post: September 20th, 10:36 AM
  4. How can I count field entries?
    By Fred in forum FileMaker
    Replies: 2
    Last Post: September 9th, 09:25 PM
  5. a select within a count ??
    By andrewst in forum Oracle Server
    Replies: 3
    Last Post: July 1st, 01:33 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139