September 14, 2005 at 4:24 pm
Folks,
I'm looking to modify the SQL below to develop a temp table with a row count. At the moment it assigned a counter for every record in the 'sales' table regardless of any filter in the where clause. Can anyone help me get the counter to only include those records as specified by the where clause. You see the row counter starts at 4 because there are 3 records where order <'6871'
I have used the 'pubs' database from SQL server
use pubs
select a1.ord_num,count(a2.ord_num)as counter
from(select sales.ord_num,count(sales.ord_num)as jcount from sales group by sales.ord_num)a1,
(select sales.ord_num,count(sales.ord_num)as jcount from sales group by sales.ord_num)a2
where a1.ord_num>=a2.ord_num and a1.ord_num >'6871'
group by a1.ord_num, a1.jcount
this would really dig me out of a hole, so thanks in advance
regards
James
September 14, 2005 at 4:44 pm
Without answering your question directly
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q186133
Ranking from sql (Numbering results) has a cost, IMHO the cycles are better spent on the presentation layer.
But I understand there are circumstances where it can't be done.
September 14, 2005 at 4:54 pm
The issue with using the SQL at the link you posted is that I cannot make any exclusions in the 'Where Clause'. For instance, when I try to exclude records where the last name is 'Dull', the record is still incluced in the rank and the count misses a number
select rank=count(*), a1.au_lname, a1.au_fname
from authors a1, authors a2
where a1.au_lname + a1.au_fname >= a2.au_lname + a2.au_fname and a1.au_lname <> 'Dull'
group by a1.au_lname, a1.au_fname
order by 1
1 Bennet Abraham
2 Blotchet-Halls Reginald
3 Carson Cheryl
4 DeFrance Michel
5 del Castillo Innes
7 Greene Morningstar
8 Green Marjorie
9 Gringlesby Burt
10 Hunter Sheryl
11 Karsen Livia
12 Locksley Charlene
13 MacFeather Stearns
14 McBadden Heather
15 O'Leary Michael
16 Panteley Sylvia
17 Ringer Albert
18 Ringer Anne
19 Smith Meander
20 Straight Dean
21 Stringer Dirk
22 White Johnson
23 Yokomoto Akiko
September 14, 2005 at 4:54 pm
The issue with using the SQL at the link you posted is that I cannot make any exclusions in the 'Where Clause'. For instance, when I try to exclude records where the last name is 'Dull', the record is still incluced in the rank and the count misses a number
select rank=count(*), a1.au_lname, a1.au_fname
from authors a1, authors a2
where a1.au_lname + a1.au_fname >= a2.au_lname + a2.au_fname and a1.au_lname <> 'Dull'
group by a1.au_lname, a1.au_fname
order by 1
1 Bennet Abraham
2 Blotchet-Halls Reginald
3 Carson Cheryl
4 DeFrance Michel
5 del Castillo Innes
7 Greene Morningstar
8 Green Marjorie
9 Gringlesby Burt
10 Hunter Sheryl
11 Karsen Livia
12 Locksley Charlene
13 MacFeather Stearns
14 McBadden Heather
15 O'Leary Michael
16 Panteley Sylvia
17 Ringer Albert
18 Ringer Anne
19 Smith Meander
20 Straight Dean
21 Stringer Dirk
22 White Johnson
23 Yokomoto Akiko
September 14, 2005 at 5:27 pm
Sure you can, what you do to a1.au_lname you have to do to a2.au_lname
select rank=count(*), a1.au_lname, a1.au_fname
from authors a1, authors a2
where a1.au_lname + a1.au_fname >= a2.au_lname + a2.au_fname
and a1.au_lname <> 'Dull'
and a2.au_lname <> 'Dull'
group by a1.au_lname, a1.au_fname
order by 1
1 Bennet Abraham
2 Blotchet-Halls Reginald
3 Carson Cheryl
4 DeFrance Michel
5 del Castillo Innes
6 Greene Morningstar
7 Green Marjorie
8 Gringlesby Burt
9 Hunter Sheryl
10 Karsen Livia
11 Locksley Charlene
12 MacFeather Stearns
13 McBadden Heather
14 O'Leary Michael
15 Panteley Sylvia
16 Ringer Albert
17 Ringer Anne
18 Smith Meander
19 Straight Dean
20 Stringer Dirk
21 White Johnson
22 Yokomoto Akiko
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply