Simple RANK

[ahchu]

I'm not sure if I've missed something, should this return rank 1 and 2 for '0001'? :confused:

If not, how can I fix this? I want to see the rankings for each group of COL1.

THANKS!


DEVEL3 SQL>select * from xxx;

COL1 COL2
---------- --------------------
0001 test1
0001 test2
0002 test3

1 select col1, col2,
2 rank() over (partition by col1 order by col1)
3* from xxx
DEVEL3 SQL>/

COL1 COL2 RANK()OVER(PARTITIONBYCOL1ORDERBYCOL1)
---------- -------------------- --------------------------------------
0001 test1 1
0001 test2 1
0002 test3 1

[cjard]

erm, youre not providing anything rankable!

if you look at the partition, its grouping on the contents of col1, and ordering by col1. hence they both go in at pole position..


you have to order by a different column:


select track, team, lap, rank() over(partition by track order by lap) as ranking
from(
select 'silverstone' as track, 'ferrari' as team, '1:59' as lap from dual union all
select 'silverstone' as track, 'mclaren' as team, '2:00' as lap from dual union all
select 'monaco' as track, 'mclaren' as team, '2:32' as lap from dual union all
select 'silverstone' as track, 'mclaren' as team, '2:08' as lap from dual union all
select 'monaco' as track, 'mclaren' as team, '2:10' as lap from dual union all
select 'monaco' as track, 'ferrari' as team, '2:47' as lap from dual
)


i ask the question "what team was in first place on each track"

team isnt part of the rank, its the result of the rank = 1, so we decide "im going to give first place to the guys with the lowest lap. so i order by lap, break it up by track, and ask the team that got that laptime)

run the query and see what i mean.

you have to decide "who gets first place?" in your data, this might mean "i was alphabetically if test2 is > test1 then he gets a better rank.. so you must order by the test2 > test 1, i.e. col2 desc

[tamilselvan]

I do not know what your requirement.
Try this:


1 select a.*,
2 rank() over (order by col1 ) rn
3* from xxx a
SQL>

COL1 COL2 RN
---- ---------- ----------
0001 test1 1
0001 test2 1
0002 test3 3

OR


SQL> select a.*,
2 dense_rank() over (order by col1) rn
3 from xxx a;

COL1 COL2 RN
---- ---------- ----------
0001 test1 1
0001 test2 1
0002 test3 2



Tamil

[ahchu]

Thanks cjard.
I guess I got confused about how to use RANK properly.
Your example and explanation gave a better picture.

I modified mine and it worked.

Thanks to Tamil also.

The only other problem I have is with Pro*C.
It doesn't seem to like the line:

Syntax error at line 39285, column 44, file load_scroll.pc:
Error at line 39285, column 44 in file load_scroll.pc
P.QUEDSC, P.SUBQUE, RANK() OVER (PARTITION BY s.sitnam ORDER BY p.prt
que)
...........................................1
PCC-S-02201, Encountered the symbol "(" when expecting one of the following:

, into, from,

Does Pro*C have some restrictions about how to use functions like RANK or keywords like OVER/PARTITION BY?

[cjard]

does it compile with sqlplus?

if so, get a better oracle app. i use Golden32 from Benthic Software. Oracle have a new free one called Oracle SQL Developer

[ahchu]

Yes it works with sqlplus.
May it's the compiler issue.
I'm using c89 on UNIX

[rajb]

I'm also facing the same problem.

Do anyone have got the solution... Please post it... :confused:

[hrishy]

Hi

Pro C Complier does not understand the analytical functions of oracle.

You should either create a view in oracle database and select from that view in your pro *C Code

or

You should construct the query as a string in pro C and then use
EXECUTE IMMEDIATE :query_str


regards
Hrishy