I did that in query analyzer using temp tables. I didn't actually create physical tables to do this.
create table #tmp_cat ( cat_num integer, category varchar(15)) create table #tmp_question ( cat_num integer, question varchar(50)) I've used MS SQL Server for so long now, I can't remember off the top of my head if that's specific or not. In MS SQL Server if you run the above statements without the # sign, the tables are a physical part of the database. You can close your connection to the DB, open it back up the next day and the tables are still there. With the # sign in front once you close that connection, the tables are gone. You have to re-create them each time. Chris --- Adaryl Wakefield <[EMAIL PROTECTED]> wrote: > Hmmm! There are some things in there I have never > seen before. Whats with > the # sign before the table names? > Is that something DBMS specific? > A. > ----- Original Message ----- > From: "Chris Stallo" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Friday, July 19, 2002 7:44 AM > Subject: Re: [KCFusion] SQL brain teaser > > > > I set up two tables. #tmp_cat contains a > cat_number > > and category description. #tmp_question contains > a > > cat_number and the question. cat_number is the > > relationship between the two. > > > > The thing that helps is the TOP command in SQL. > It > > returns the TOP N rows back. > > > > select category, > > count(q.question), > > 'question' = (select top 1 question from > > #tmp_question Q2 > > where Q2.cat_num = T.cat_num > > AND left( Q2.question, 1) = 'A') > > from #tmp_cat T > > join #tmp_question Q on Q.cat_num = T.cat_num > > where left( question, 1) = 'A' > > group by T.cat_num, category > > > > With the combination of the TOP and the Group By > it > > will return the data like this. > > > > category num_ques sample question > > Advice 2 Always do this > > General 3 All people are not > cool > > Specific 1 Apples are red > > > > Chris > > > > --- Adaryl Wakefield <[EMAIL PROTECTED]> wrote: > > > (is this thing on?) > > > So I ran accross a problem the other day, solved > it, > > > but it is not the most > > > elegant solution I think. If anybody sees > something > > > cleaner right off I'd > > > much apprciate a reply. If not I would not > attempt > > > this while you are at > > > work. > > > > > > The problem: We have knowlege base that is > > > organized such that questions > > > will be grouped under categories. I needed to > yank > > > back all categories that > > > start with a certain letter, count the questions > in > > > those categories and > > > display the categories, the the count of the > > > questions and one and only one > > > question from each category. The last part was > the > > > tough one. Long story > > > short, I stumbled accross the "group" attribute > of > > > the cfoutput tag which is > > > great but im still getting far more tuples then > I > > > actually need (every > > > single question comes back but only one gets > > > displayed). Here is the query i > > > developed: > > > > > > SELECT table1.Cat_num, table1.Category, > > > table2.question, > > > > > > (SELECT Count(table2.question_ID) as > theNumQuestions > > > FROM table2 > > > WHERE table1.Cat_num = table2.Cat_num) as > > > numQuestions, > > > > > > FROM table1, table2 > > > WHERE table1.Category LIKE 'A' AND > table1.Cat_num *= > > > table2.Cat_num > > > ORDER BY table1.Category > > > > > > Adaryl > > > > > > > > > > > > > > > > > > ______________________________________________________________________ > > > The KCFusion.org list and website is hosted by > > > Humankind Systems, Inc. > > > List Archives........ > > > http://www.mail-archive.com/cf-list@kcfusion.org > > > Questions, Comments or Glowing Praise.. > > > mailto:[EMAIL PROTECTED] > > > To Subscribe.................... > > > mailto:[EMAIL PROTECTED] > > > To Unsubscribe................ > > > mailto:[EMAIL PROTECTED] > > > > > > > > > __________________________________________________ > > Do You Yahoo!? > > Yahoo! Autos - Get free new car price quotes > > http://autos.yahoo.com > > > > > > > ______________________________________________________________________ > > The KCFusion.org list and website is hosted by > Humankind Systems, Inc. > > List Archives........ > http://www.mail-archive.com/cf-list@kcfusion.org > > Questions, Comments or Glowing Praise.. > mailto:[EMAIL PROTECTED] > > To Subscribe.................... > mailto:[EMAIL PROTECTED] > > To Unsubscribe................ > mailto:[EMAIL PROTECTED] > > > > > > ______________________________________________________________________ > The KCFusion.org list and website is hosted by > Humankind Systems, Inc. > List Archives........ > http://www.mail-archive.com/cf-list@kcfusion.org > Questions, Comments or Glowing Praise.. > mailto:[EMAIL PROTECTED] > To Subscribe.................... > mailto:[EMAIL PROTECTED] > To Unsubscribe................ > mailto:[EMAIL PROTECTED] > __________________________________________________ Do You Yahoo!? Yahoo! Autos - Get free new car price quotes http://autos.yahoo.com ______________________________________________________________________ The KCFusion.org list and website is hosted by Humankind Systems, Inc. List Archives........ http://www.mail-archive.com/cf-list@kcfusion.org Questions, Comments or Glowing Praise.. mailto:[EMAIL PROTECTED] To Subscribe.................... mailto:[EMAIL PROTECTED] To Unsubscribe................ mailto:[EMAIL PROTECTED]
