DISTINCT statement with Order by clause

SQL> desc test;
Name       Type         Nullable Default Comments
---------- ------------ -------- ------- --------
LAST_NAME  VARCHAR2(40) Y                        
FIRST_NAME VARCHAR2(40) Y                        
ACCOUNT_ID NUMBER       Y                        

SQL>
SQL> select last_name
  2        ,first_name
  3        ,account_id
  4    from test
  5   order by upper(last_name),upper(first_name)
  6  ;

LAST_NAME                                FIRST_NAME                               ACCOUNT_ID
---------------------------------------- ---------------------------------------- ----------
Chantemsin                               HELEN                                       4523765
GONZALES                                 TIFFANY                                     4523771
GRIFFITTS                                MIMI                                        4523766
Hill                                     Dennis                                      4523770
JONES                                    WAYNE                                       4523772
Lindom                                   ARNOLD                                      4523764
MCCLAIN                                  Janet                                       4523779
MURRAY-BRESLOW                           Spencer                                     4523769
Sharick                                  Ernest                                      4523776
Slaughter                                MARK                                        4523767
Walker-Trumble                           DANIEL                                      4523778

11 rows selected

SQL>
SQL> select last_name
  2        ,first_name
  3        ,account_id
  4        ,count(account_id) over() member_cnt
  5  from (
  6  select last_name
  7        ,first_name
  8        ,account_id
  9    from test
 10   order by upper(last_name),upper(first_name)
 11   );

LAST_NAME                                FIRST_NAME                               ACCOUNT_ID MEMBER_CNT
---------------------------------------- ---------------------------------------- ---------- ----------
Chantemsin                               HELEN                                       4523765         11
GONZALES                                 TIFFANY                                     4523771         11
GRIFFITTS                                MIMI                                        4523766         11
Hill                                     Dennis                                      4523770         11
JONES                                    WAYNE                                       4523772         11
Lindom                                   ARNOLD                                      4523764         11
MCCLAIN                                  Janet                                       4523779         11
MURRAY-BRESLOW                           Spencer                                     4523769         11
Sharick                                  Ernest                                      4523776         11
Slaughter                                MARK                                        4523767         11
Walker-Trumble                           DANIEL                                      4523778         11

11 rows selected

SQL>
SQL> select last_name
  2        ,first_name
  3        ,account_id
  4        ,count(distinct account_id) over() member_cnt
  5  from (
  6  select last_name
  7        ,first_name
  8        ,account_id
  9    from test
 10   order by upper(last_name),upper(first_name)
 11   );

LAST_NAME                                FIRST_NAME                               ACCOUNT_ID MEMBER_CNT
---------------------------------------- ---------------------------------------- ---------- ----------
Lindom                                   ARNOLD                                      4523764         11
Chantemsin                               HELEN                                       4523765         11
GRIFFITTS                                MIMI                                        4523766         11
Slaughter                                MARK                                        4523767         11
MURRAY-BRESLOW                           Spencer                                     4523769         11
Hill                                     Dennis                                      4523770         11
GONZALES                                 TIFFANY                                     4523771         11
JONES                                    WAYNE                                       4523772         11
Sharick                                  Ernest                                      4523776         11
Walker-Trumble                           DANIEL                                      4523778         11
MCCLAIN                                  Janet                                       4523779         11

11 rows selected

SQL>
SQL> select *
  2    from (select last_name,
  3                 first_name,
  4                 account_id,
  5                 rownum row_number_seq,
  6                 count(distinct account_id) over() member_cnt
  7            from (select last_name, first_name, account_id
  8                    from test
  9                   order by upper(last_name), upper(first_name)))
 10   order by row_number_seq;

LAST_NAME                                FIRST_NAME                               ACCOUNT_ID ROW_NUMBER_SEQ MEMBER_CNT
---------------------------------------- ---------------------------------------- ---------- -------------- ----------
Chantemsin                               HELEN                                       4523765              1         11
GONZALES                                 TIFFANY                                     4523771              2         11
GRIFFITTS                                MIMI                                        4523766              3         11
Hill                                     Dennis                                      4523770              4         11
JONES                                    WAYNE                                       4523772              5         11
Lindom                                   ARNOLD                                      4523764              6         11
MCCLAIN                                  Janet                                       4523779              7         11
MURRAY-BRESLOW                           Spencer                                     4523769              8         11
Sharick                                  Ernest                                      4523776              9         11
Slaughter                                MARK                                        4523767             10         11
Walker-Trumble                           DANIEL                                      4523778             11         11

11 rows selected

SQL>

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

转载于:http://blog.itpub.net/45259/viewspace-629775/