| max values [message #59822] |
Sat, 27 December 2003 23:35  |
M. Khaled
Messages: 67
Registered: April 2002
|
Member |
|
|
Anybody please give me a query to find maximum 10 values from a table not creating views
thanks in advance
|
|
|
|
| Re: max values [message #59823 is a reply to message #59822] |
Sun, 28 December 2003 08:15  |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
thiru@9.2.0:SQL>create table t as select ename,sal from scott.emp;
Table created.
thiru@9.2.0:SQL>select * from ( select * from t order by sal desc) where rownum <= 10;
ENAME SAL
---------- ----------
King 31059
Scott 18675
Ford 18675
Jones 18520
Blake 17746
Clark 15270
Victor 11000
SMITH 1700
Turner 1600
Miller 1400
10 rows selected.
thiru@9.2.0:SQL>select ename,sal from ( select ename,sal,RANK() OVER (ORDER BY sal DESC) SAL_RANK
2 from T ) where SAL_RANK <= 10;
ENAME SAL
---------- ----------
King 31059
Scott 18675
Ford 18675
Jones 18520
Blake 17746
Clark 15270
Victor 11000
SMITH 1700
Turner 1600
Miller 1400
10 rows selected.
thiru@9.2.0:SQL>select ename,sal from ( select ename,sal,DENSE_RANK() OVER (ORDER BY sal DESC) SAL_RANK
2 from T ) where SAL_RANK <= 10;
ENAME SAL
---------- ----------
King 31059
Scott 18675
Ford 18675
Jones 18520
Blake 17746
Clark 15270
Victor 11000
SMITH 1700
Turner 1600
Miller 1400
Ward 1350
Martin 1350
12 rows selected.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
