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

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Bob Barrows [MVP] (reb01501_at_NOyahoo.SPAMcom)
Date: 08/19/04 

Date: Thu, 19 Aug 2004 18:09:35 -0400

Dan Nash wrote:
> 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.

SELECT TOP 100 * FROM Questions ORDER BY question_id DESC

>
> 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.
>

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"

Relevant Pages

  • Re: eVC4 Installation
    ... The Microsoft Support page clearly shows that eVC 4 ... Microsoft Product Support Services. ... So if you run into the Setup error about offset 00009392, ... > to watch the registry keys. ...
    (microsoft.public.pocketpc.developer)
  • Re: Startup
    ... to me> 'Large Keys/Small Keys' means just that... ... If> there is no documentation indicating that you get more keys and a bit> different operation, ... It certainly gives you the option of searching for text strings within files of any sort. ... I'd be surprised if you don't have a bob in your address book, but if you don't, try a name you know is in there. ...
    (microsoft.public.pocketpc)
  • RE: Trust problems with Server 2003
    ... We have setup windows 2003 server on Compaq DL- ... Bob, once again thanks for replying, we are also looking ... > trust using that method. ...
    (microsoft.public.windows.server.migration)
  • Re: xterm question
    ... how it could be used to setup key bindings and would probably be still ... these mappings don't seem to carry over into xterm. ... because I do my best NOT to use any keys that live beyond my stubby ... To UNSUBSCRIBE, email to debian-user-REQUEST@xxxxxxxxxxxxxxxx ...
    (Debian-User)
  • Re: Smaller key keyboards?
    ... called locater keys, and on the macs of old, they were located on ... down at the keyboard) on any other keyboard known to man would ... And they were on the Mac's d and k keys before PC makers put them on the ... After Apple relocated them to the f and j keys, BOB, I ...
    (comp.sys.mac.system)