sql server new users: subquery help -- sql server new users

Re: subquery help

Jacco Schalkwijk
4/26/2005 12:00:00 AM

You are now ordering them by dealercode. The following will probably work:

select -- No need for TOP 100 percent
a1.dealercode, a1.dealername, a1.firstname,
a1.lastname,
a1.region, a1.district,a1.groupsize, a1.promotionalprogram, a1.PPPPoints,
a1.OSOPoints, a1.TotalPoints, a1.completed_modules, rank=count(*)
from dbo.rp_RegRecap a1, dbo.rp_RegRecap a2
where a1.totalpoints >= a2.totalpoints
AND a1.completed_modules >= a2.completed_modules
AND a1.promotionalprogram >= a2.promotionalprogram
AND
a1.groupsize=[groupsizevar] AND a2.groupsize=[groupsizevar]
AND a1.region=[regionvar] AND a2.region=[regionvar]
group by a1.dealercode, a1.dealername, a1.firstname, a1.lastname,
a1.region, a1.district,a1.groupsize, a1.promotionalprogram, a1.PPPPoints,
a1.OSOPoints, a1.TotalPoints, a1.completed_modules
order by a1.totalpoints desc, a1.completed_modules desc,
a1.promotionalprogram desc

[groupsizevar] is not a variable in SQL btw, that should be @groupsizevar

--
Jacco Schalkwijk
SQL Server MVP

[quoted text, click to view]

Re: subquery help

Jacco Schalkwijk
4/26/2005 12:00:00 AM

What do you order by? Can you give the complete statement?

--
Jacco Schalkwijk
SQL Server MVP

[quoted text, click to view]

subquery help

r
4/26/2005 1:05:58 AM

Following is code I've written to help sort my data on three fields, then
"rank" them based on the results.

select a1.dealercode, a1.dealername, a1.firstname, a1.lastname,
a1.region, a1.district,a1.groupsize, a1.promotionalprogram, a1.PPPPoints,
a1.OSOPoints, a1.TotalPoints, a1.completed_modules, rank=count(*)
from dbo.rp_RegRecap a1, dbo.rp_RegRecap a2
where (a1.dealercode >= a2.dealercode) AND groupsize=[groupsize_variable]
AND region=[region_variable]
group by a1.dealercode, a1.dealername, a1.firstname, a1.lastname,
a1.region, a1.district,a1.groupsize, a1.promotionalprogram, a1.PPPPoints,
a1.OSOPoints, a1.TotalPoints, a1.completed_modules

It works - BUT ... the problem is, I am passing more criteria to break down
the groups even further (by region & groupsize). I foolishly thought the
"rank" would only rank the records that were returned for my subset; it
didn't. I ended up with my subset of records, but the first one was ranked
15, and the next 43, etc. I was expecting 1,2,3, etc.

So my question is this - how do I FIRST create my subset and THEN do the
ranking based on my subset, all within the same view/query?

?!

Re: subquery help

r
4/26/2005 2:56:53 AM

I am so wiped out, I can't think straight enough to read that and do what
you need. I really need to get some rest.

Let me try to simplify.

I am trying to create a "rank" for data in a table (based on this
http://support.microsoft.com/?kbid=186133). It works on the BASE data -
the table.

Problem is, I need to filter it down further because I am showing only
portions of the data to various people. To simplify, let's say the table
contains all the stores for every region in the country. The code I'm using
successfully ranks all 100.

However, the data underlays a website. The user will select their own
region and "groupsize", and they need to see their own ranking within those
two criteria. When I further filter the "ranking" view for that user, the
results they see aren't a ranking of 1-10, but rather a ranking of where
they stand nationally.

Perhaps if I just knew how to create a subquery inside another query would
be useful.... ?? If no answers, I'll post more details & samples as
requested tomorrow.

zzzzzzzzzzzzzzz

Thanks.

[quoted text, click to view]

Re: subquery help

r
4/26/2005 7:02:31 AM

You are so awesome! Thanks for the timely help, Jacco!!

[quoted text, click to view]

Re: subquery help

r
4/26/2005 7:09:38 AM

Hm... BUT

....now if I add Order By (so they go in the correct order), the rank is set
in the original order (how they were default in the list) and though they
appear in the right order, the rank is off.

How would I sort it and THEN do the ranking?

Sorry... I thought all was well there for a minute!

[quoted text, click to view]

Re: subquery help

r
4/26/2005 8:37:51 AM

Yes,

select top 100 percent a1.dealercode, a1.dealername, a1.firstname,
a1.lastname,
a1.region, a1.district,a1.groupsize, a1.promotionalprogram, a1.PPPPoints,
a1.OSOPoints, a1.TotalPoints, a1.completed_modules, rank=count(*)
from dbo.rp_RegRecap a1, dbo.rp_RegRecap a2
where (a1.dealercode >= a2.dealercode) AND
a1.groupsize=[groupsizevar] AND a2.groupsize=[groupsizevar]
AND a1.region=[regionvar] AND a2.region=[regionvar]
group by a1.dealercode, a1.dealername, a1.firstname, a1.lastname,
a1.region, a1.district,a1.groupsize, a1.promotionalprogram, a1.PPPPoints,
a1.OSOPoints, a1.TotalPoints, a1.completed_modules
order by a1.totalpoints desc, a1.completed_modules desc,
a1.promotionalprogram desc

So in the end, it appears that the rank is set when the items are originally
calculated/brought in, and THEN the sort occurs, leaving the original rank
in tact.

So assuming "sortedorder" below is the order I WANT with the sort, and rank
is the ranking order, I might see:

sortedorder - rank
1 - 3
2 - 1
3 - 2
4 - 4

I have tried to break this into two steps, as well, but still, the rank
always remains whatever was set BEFORE the order by. ?! Very odd.

[quoted text, click to view]

Re: subquery help

r
4/26/2005 9:33:37 AM

Could cursortype and cursorlocation have anything to do with my results, you
think?

[quoted text, click to view]

Re: subquery help

r
4/26/2005 11:16:07 AM

It appears that the rank function, as I'm using it, wants to rank whatever
field we are referring to in the WHERE clause, and must rank it Ascending...

Does this help find a solution at all? I really need this! Ugh!

[quoted text, click to view]

Re: subquery help

r
4/26/2005 2:50:36 PM

Thanks, Jacco. I did try this when I realized the "where" was causing the
sort to fall on DealerCode -- but to no avail. The rank continues to
number Ascending in the original ranking, then when the order by is set, the
rank merely appears exactly reversed, and descends.

I don't know any way around it.

[quoted text, click to view]

Re: subquery help

Jacco Schalkwijk
4/27/2005 12:00:00 AM

Can you post your table structure, the query you have (make sure it compiles
and runs), some sample data and the expected result? See www.aspfaq.com/5006
for the details.

--
Jacco Schalkwijk
SQL Server MVP

[quoted text, click to view]

Re: subquery help

r
4/27/2005 7:19:42 AM

Yes, I will post them - though I can't get to it for a couple or hours yet.
Stay tuned.

[quoted text, click to view]

Re: subquery help

r
4/27/2005 12:00:26 PM

How will I be sending the information to you?

[quoted text, click to view]
that

Re: subquery help

r
4/27/2005 12:46:49 PM

Hi Jacco,

I am not very familiar with SQL and the instructions to provide you the data
confuse and concern me. The database is live, and I don't like to play with
it when I'm not certain of what I'm doing! Recreating the problem, however,
is very simple.

Following is a broken down sample of the problem I'm seeing - which is that
the "rank" is being set PRIOR to any Order By.

Data/Table would be (my table name was dbo.testingdata):

(IDcol)(Amt)
A1 80
A2 60
B3 10
C1 40
B2 20
C2 50
B1 30
A3 70

SQL (Query Analyzer requires I use "top 100 percent"):

SELECT top 100 percent rank=count(*), a1.IDcol, a1.AMT
FROM dbo.testingdata a1, dbo.testingdata a2
WHERE (a1.AMT >= a2.AMT)
group by a1.IDcol, a1.AMT
ORDER BY 3 desc

Here are result:
(rank)(IDcol)(Amt)
8 A1 80
7 A3 70
6 A2 60
5 C2 50
4 C1 40
3 B1 30
2 B2 20
1 B3 10

As you can see, I want to sort on the Amt as Desc, and so the rank is also
showing as descending. For my purposes, someone with 100% is at the top
rank (ie, "first place"), not the bottom as is in this case.

[quoted text, click to view]

Re: subquery help

Jacco Schalkwijk
4/29/2005 12:00:00 AM

For the data you have given I would write the query as:

CREATE TABLE testingdata(IDcol CHAR(2), AMT INT, PRIMARY KEY (IDCol, AMT))
GO

INSERT INTO testingdata (IDCol, AMT) VALUES( 'A1' , 80)
INSERT INTO testingdata (IDCol, AMT) VALUES( 'A2' , 60)
INSERT INTO testingdata (IDCol, AMT) VALUES( 'B3', 10)
INSERT INTO testingdata (IDCol, AMT) VALUES( 'C1' , 40)
INSERT INTO testingdata (IDCol, AMT) VALUES( 'B2' , 20)
INSERT INTO testingdata (IDCol, AMT) VALUES( 'C2' , 50)
INSERT INTO testingdata (IDCol, AMT) VALUES( 'B1' , 30)
INSERT INTO testingdata (IDCol, AMT) VALUES( 'A3' , 70)

GO
SELECT rank=count(*), a1.IDcol, a1.AMT
FROM dbo.testingdata a1
INNER JOIN dbo.testingdata a2
ON a1.AMT <= a2.AMT
GROUP BY a1.IDcol, a1.AMT
ORDER BY rank

GO

DROP TABLE testingdata

--
Jacco Schalkwijk
SQL Server MVP

[quoted text, click to view]

Re: subquery help

r
5/3/2005 9:26:01 AM

Thanks, Jacco - I'll try that.

[quoted text, click to view]