Ranking Records

[MJSteve]

Hello all,
I have a table with some 2 millions records.

it is as such
npanxxxxxx Carrier IXC
201007???? ATT 0.0069
201007???? ITL 0.0017
201007???? LVL 0.0049
201032???? ATT 0.01717
201032???? ITL 0.0092
201032???? LVL 0.00758
201040???? ATT 0.01126
201040???? ITL 0.0039
201040???? LVL 0.0018
201200???? ATT 0.01126
201200???? BRL 0.00286
201200???? BVX 0.002
201200???? EXL 0.0036
201200???? GNP 0.0005
201200???? ITL 0.0039
201200???? LVL 0.0018
201200???? TEL 0.0039
201200???? TRA 0.0045
201200???? XOC 0.0031

What I need to do is rank each of these by NPANXXXXXX and Carrier
so the result looks like this...

npanxxxxxx Carrier IXC rank
201007???? ATT 0.0069 1
201007???? ITL 0.0017 2
201007???? LVL 0.0049 3
201032???? ATT 0.01717 1
201032???? ITL 0.0092 2
201032???? LVL 0.00758 3
201040???? ATT 0.01126 1
201040???? ITL 0.0039 2
201040???? LVL 0.0018 3
201200???? ATT 0.01126 1
201200???? BRL 0.00286 2
201200???? BVX 0.002 3
201200???? EXL 0.0036 4
201200???? GNP 0.0005 5
201200???? ITL 0.0039 6
201200???? LVL 0.0018 7
201200???? TEL 0.0039 8
201200???? TRA 0.0045 9
201200???? XOC 0.0031 10

Thanks in advance to everyone for any help offered.
Obviously given the table size, fast and efficient is what I am looking for. Then again, isnt that what everyone is looking for.

 

[SkipVought]

hi,

Do you mean that you have a query from a 2M row table, and you want the resultset of your query Order By npanxxxxxx, Carrier?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MJSteve]

hi SkipVought

The example I displayed is actually in a table named - tbl_normalised.
What I would like to do is programmatically update a rank field in the to what I show in the bottom example of what i need the resultant table to look like.

Regards
Steve

 

[PHV]

One way:

SELECT A.npanxxxxxx, A.Carrier, A.IXC, Count(*) AS rank
FROM tbl_normalised A
INNER JOIN tbl_normalised B ON A.npanxxxxxx=B.npanxxxxxx AND A.Carrier>=B.Carrier
GROUP BY A.npanxxxxxx, A.Carrier, A.IXC

Another way:

SELECT A.npanxxxxxx, A.Carrier, A.IXC
, (SELECT Count(*) FROM tbl_normalised WHERE npanxxxxxx=A.npanxxxxxx AND Carrier<=A.Carrier) AS rank
FROM tbl_normalised A

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MJSteve]

PHV
This works perfect...Even across 2 Million + records, its fairly efficient for my purposes.

Thank you for your help

 

[PHV]

its fairly efficient
Just to know, which is faster ?

 

[MJSteve]

PHV,
I have not actually checked to see which one is faster. I tried the first one and it worked PERFECTLY.

I will however try the other and see which one runs quicker.

I do appreciate the help greatly....