February 2, 2010 at 11:21 am
I am probably just missing something fundamental, but I need a way to get two pass style results in a single pass in 2000.
I have a view with several million rows. A simplified short version looks like this:
IDWOSC
1 11
212
313
4115
5112
6215
722
823
9247
1031
1141
1242
1343
1444
15515
1662
1763
I need to calculate how many of the unique WO's have an SC of 1-3. But I only want it indicated one time. So the output I need should look like:
IDWOSCWant
1111
2120
3130
41150
51120
62150
7221
8230
92470
10311
11411
12420
13430
14440
155150
16621
17630
Using a case statement I'm getting:
select w.*, case when w.sc in(1,2,3) then 1 else 0 end as Got
from wo_test w
IDWOSCGot
1111
2121
3131
41150
51120
62150
7221
8231
92470
10311
11411
12421
13431
14440
155150
16621
17631
This isn't working because I'm getting multiple hits for a given WO, where I only want one if a WO has any of the requisite SC's. I could do this with OVER in 2005+, but I can't figure how to do it in 2000.
Any ideas?
Dave
February 2, 2010 at 11:54 am
The ways I know of to do this in SQL 2000 are either a correlated sub-query that has a max or min function in it to match it up to only one outer row, or to use a cursor. You'd have to test to find out which will work more efficiently on your table.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply