top 25 for different categories

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

top 25 for different categories

Author

Topic

cidr
Posting Yak Master

207 Posts

Posted - 2010-01-15 : 05:15:51

Hi there,

I'm not sure if this is possible.

The report I'm trying to develop will display info on the amount of calls logged by fashion stores to fix repairs. There are several areas and each area has several stores.

I want to set up a query that will have two main columns. One will display top 25 stores logging calls. The other will display top 25 areas logging calls. There will obviously be different groupings here. The store calls will group by store and the area calls by area.

However, I want to find a way to have a query that will show both top 25 and have two columns; One with stores, and one with area.

Does anyone know if this is possible.

I'd be grateful for some help

Many thanks

Kristen
Test

22859 Posts

Posted - 2010-01-15 : 05:25:39

FULL OUTER JOIN ??


SELECT [Rank], StoreID, AreaID
FROM
(
    SELECT TOP 25 [Rank], StoreID
    FROM MyTable
    ORDER BY NumberOfCallsDescending
) AS A
FULL OUTER JOIN
(
    SELECT TOP 25 [Rank], AreaID
    FROM MyOtherTable
    ORDER BY NumberOfCallsDescending
) AS B
    ON B.Rank = A.Rank

Its going to need a RANK though, and that's going to take some messing around in SQL 2000 ...

I think I'd put the two queries into a TEMP table with IDENTITY column, and use that for the Rank.

Kristen
Test

22859 Posts

Posted - 2010-01-15 : 05:26:15

Actually, once you've got RANK a straight JOIN will do - provided there are always 25 from BOTH queries.

cidr
Posting Yak Master

207 Posts

Posted - 2010-01-15 : 06:04:22

Thanks for that folks, that's food for thought.

incidentally, there wont Necessarily always be 25, it just depends on date factors etc.

Cheers :)

cidr
Posting Yak Master

207 Posts

Posted - 2010-01-15 : 06:34:46

Hi, I actually tried to create a temp table, however, I'm receiving the error
**
An explicit value for the identity column in table '#tmp' can only be specified when a column list is used and IDENTITY_INSERT is ON
**

I've not specified a value for the Rank identity column. To test, I created the temp table with the IDENTITY column and two others, and tried to do a INSERT INTO SELECT... with only two columns. Still, I get this error.

Any suggestions?

Cheers

cidr
Posting Yak Master

207 Posts

Posted - 2010-01-15 : 06:40:12

Hi, actually foget that last message, I forgot to add the columns with INSERT INTO #tmp

:)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-15 : 07:07:25

quote:
Originally posted by Kristen

FULL OUTER JOIN ??

SELECT [Rank], StoreID, AreaID
FROM
(
SELECT TOP 25 [Rank], StoreID
FROM MyTable
ORDER BY NumberOfCallsDescending
) AS A
FULL OUTER JOIN
(
SELECT TOP 25 [Rank], AreaID
FROM MyOtherTable
ORDER BY NumberOfCallsDescending
) AS B
ON B.Rank = A.Rank

Its going to need a RANK though, and that's going to take some messing around in SQL 2000 ...

I think I'd put the two queries into a TEMP table with IDENTITY column, and use that for the Rank.

I would also use that approach than FULL OUTER JOIN
Also if OP wants to show data in front end application, it would be much easiler to have two resultsets and combine them

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-01-15 : 07:26:39

"Also if OP wants to show data in front end application, it would be much easiler to have two resultsets and combine them"

Bah! Nightmare to generate an HTML table with two adjacent columns from two consecutive recordsets (i.e. getting the second resultset into an adjacent column)

But there again, proper HTML coders [I'm not one!], would fix that by using decent CSS and converting the recordsets to HTML in the order they were received

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-15 : 07:32:53

How is it difficult?

Provided that two resultsets have same number of rows, it would be

While not recordsset1.eof
.
.
<..... recordsset1("my_col") ...... recordsset2("my_col") ..>
.
.
recordsset1.movenext
recordsset2.movenext

Wend

Isn't it?

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-01-15 : 08:42:20

Interesting. How are you getting recordset2 (before you have finished consuming recordset1 - and assuming recordset1 is larger than whatever the normal buffering allows)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-15 : 08:54:26

quote:
Originally posted by Kristen

Interesting. How are you getting recordset2 (before you have finished consuming recordset1 - and assuming recordset1 is larger than whatever the normal buffering allows)

It would be declaring two dummy recordsets and assign data to them

Declare recordsset1 as new ADODB.recordset
Declare recordsset2 as new ADODB.recordset
.
.
.
--Assign data to Main_recordset

set recordsset1 = Con.execute("my_proc_result1")
set recordsset2 = Con.execute("my_proc_result2")

.
.
.

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-15 : 08:56:01

<<
assuming recordset1 is larger than whatever the normal buffering allows
>>

Did you mean returning millions of rows?

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-01-15 : 09:13:41

"Did you mean returning millions of rows?"

Yeah.

My expeiernce is probably old-hat in this regard.

We used to be able to get a recordset, and start walking the rows.

We could do NextRecordSet (into a different variable) and still alk the rows from the first recordset, but my memory is that was because RecodSet1 was buffered. If it had lots of records we could not retrieve more data than was in the buffer [when we issued the NextRecordSet)

Ah ... Bull! You are calling two separate SProcs, right? That's cheating!!

Kristen
Test

22859 Posts

Posted - 2010-01-15 : 09:23:13

I still think you've got a problem with two separate queries through one connection.

Never had a problem with Oracle or Watcom / Sybase SQL Anywhere, and that came as a big shock when we widened our support from those to include MS SQL - version 6.5 back in 1995 or somesuch.

Don't tell me Microsoft have fixed that already?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-18 : 02:01:20

<<
Ah ... Bull! You are calling two separate SProcs, right? That's cheating!!
>>
<<
Don't tell me Microsoft have fixed that already?
>>

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources