How I met the right function:)

The other day I was asked about tips on Oracle developing. So Today I am going to tell you the story about how I met the right function.

Here we go...

Table math contains 3 columns class_id, student_id and score. And user wants to find out the No.1 student(with the highest score) of each class.

There are several ways to do this. And the most convenient one in Oracle is by analystic funtions.

At first, I thought row_number would be the right function:

SQL> select * from math;

CLASS_ID             STUDENT_ID                SCORE
-------------------- -------------------- ----------
1                    1                            35
1                    2                            50
1                    3                            80
2                    1                            89
2                    2                            78
2                    3                            45
3                    1                            74
3                    2                            88

8 rows selected.

SQL> select class_id, student_id, score from
  2  (select class_id,student_id, score, row_number() over (partition by class_id order by score desc) id from math) t
  3  where t.id < 2;

CLASS_ID             STUDENT_ID                SCORE
-------------------- -------------------- ----------
1                    3                            80
2                    1                            89
3                    2                            88

SQL>

At the first glance, seems we've got the correct result. But...if 2 students in the same class got the same points...

SQL> insert into math values ('3','3',88);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from math order by class_id;

CLASS_ID             STUDENT_ID                SCORE
-------------------- -------------------- ----------
1                    3                            80
1                    1                            35
1                    2                            50
2                    1                            89
2                    3                            45
2                    2                            78
3                    1                            74
3                    2                            88
3                    3                            88

SQL> select class_id, student_id, score from
  2  (select class_id,student_id, score, row_number() over (partition by class_id order by score desc) id from math) t
  3  where t.id < 2;

CLASS_ID             STUDENT_ID                SCORE
-------------------- -------------------- ----------
1                    3                            80
2                    1                            89
3                    3                            88

SQL>

Oh God... The function has not yet come out...

Conitue searching...

Finally, at a corner in the darkness, a shimmering light brights my heart...

SQL> select class_id, student_id, score from
  2  (select class_id, student_id, score, rank() over (partition by class_id order by score desc) rank from math)t
  3  where t.rank=1;

CLASS_ID             STUDENT_ID                SCORE
-------------------- -------------------- ----------
1                    3                            80
2                    1                            89
3                    3                            88
3                    2                            88

SQL>

Happy ending~~~

Hi kids, that's how i met the right function.

Ok, that's all for today, see you next time:)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9765498/viewspace-672423/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9765498/viewspace-672423/