y********o
发帖数: 2565 | 1 It is pretty easy to get the largest value of a field. e.g.:
select max(salary) from employees;
How do you get the 2nd largest value? Or the 3rd largest?
Do this:
select salary, rn from
(select salary, row_number() over (order by salary desc) rn
from employees) x
where rn=2; |
q**1
发帖数: 193 | 2 这个在mysql里面比较简单（选第3个）：
select salary from employees
order by salary desc
limit 2,1;
【在 y********o 的大作中提到】
: It is pretty easy to get the largest value of a field. e.g.:
: select max(salary) from employees;
: How do you get the 2nd largest value? Or the 3rd largest?
: Do this:
: select salary, rn from
: (select salary, row_number() over (order by salary desc) rn
: from employees) x
: where rn=2;
|
y********o
发帖数: 2565 | 3 Good. I love MySQL, but it does not seem to be very popular. Very few job
ads mention MySQL.
【在 q**1 的大作中提到】
: 这个在mysql里面比较简单（选第3个）：
: select salary from employees
: order by salary desc
: limit 2,1;
|
a*******t
发帖数: 891 | 4 it is popular, considered it's open source
can't compete with M$ and Oracle of course, in terms of market share
job
【在 y********o 的大作中提到】
: Good. I love MySQL, but it does not seem to be very popular. Very few job
: ads mention MySQL.
|
y********o
发帖数: 2565 | 5 才试了一下这个，确实不错，比 Oracle 的那个简洁明快。
【在 q**1 的大作中提到】
: 这个在mysql里面比较简单（选第3个）：
: select salary from employees
: order by salary desc
: limit 2,1;
|
q**1
发帖数: 193 | 6 I dont have Oracle, but can you use a query like:
select salary from employees
order by salary desc
where rownum = 2;
【在 y********o 的大作中提到】
: 才试了一下这个，确实不错，比 Oracle 的那个简洁明快。
|
c**t
发帖数: 2744 | 7 in sql server:
select top 5 x.*
from (select distinct salary from employees order by salary desc) x
【在 y********o 的大作中提到】
: It is pretty easy to get the largest value of a field. e.g.:
: select max(salary) from employees;
: How do you get the 2nd largest value? Or the 3rd largest?
: Do this:
: select salary, rn from
: (select salary, row_number() over (order by salary desc) rn
: from employees) x
: where rn=2;
|
y********o
发帖数: 2565 | 8 The one you gave below gives an error:
SQL> select salary from emp order by salary desc where rownum=2;
select salary from emp order by salary desc where rownum=2
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
I tried this:
SQL> select salary from emp where rownum = 2 order by salary desc ;
Which returns:
no rows selected
__Note__: emp is just a synonym of hr.employee.
【在 q**1 的大作中提到】
: I dont have Oracle, but can you use a query like:
: select salary from employees
: order by salary desc
: where rownum = 2;
|
q**1
发帖数: 193 | 9 这个我就不知道了：-）...重来没摸过Oracle，只是在我的
SQL Cookbook上看到一个例子，觉得可能你可以用的上：Page-8
In Oracle, place a restriction on the number of rows
returned by restricting ROWNUM in the WHERE clause:
select *
from emp
where rownum <= 5
【在 y********o 的大作中提到】
: The one you gave below gives an error:
: SQL> select salary from emp order by salary desc where rownum=2;
: select salary from emp order by salary desc where rownum=2
: *
: ERROR at line 1:
: ORA-00933: SQL command not properly ended
: I tried this:
: SQL> select salary from emp where rownum = 2 order by salary desc ;
: Which returns:
: no rows selected
|
y********o
发帖数: 2565 | 10 Yes, <, <=, >, >= are fine.
Looks like = is not ok.
【在 q**1 的大作中提到】
: 这个我就不知道了：-）...重来没摸过Oracle，只是在我的
: SQL Cookbook上看到一个例子，觉得可能你可以用的上：Page-8
: In Oracle, place a restriction on the number of rows
: returned by restricting ROWNUM in the WHERE clause:
: select *
: from emp
: where rownum <= 5
|
|
|
w*******e
发帖数: 1622 | 11 应该在subquery里加上top 5
【在 c**t 的大作中提到】
: in sql server:
: select top 5 x.*
: from (select distinct salary from employees order by salary desc) x
|
s**o
发帖数: 584 | 12 Actually it is not. Rownum are a pseudocolumn (not a real column). It will
be available after the query statement is processed. You may try this query
to get your result.
select *
from ( select e.*, rownum rnum
from ( select emp.*
from emp
order by salary desc ) e
where rownum <= 2
)
where rnum = 2;
【在 y********o 的大作中提到】
: Yes, <, <=, >, >= are fine.
: Looks like = is not ok.
|
y********o
发帖数: 2565 | 13 Yes, yes, this one worked. I think I understand
the logic of rownum now, given your explanation.
Thanks.
Question: How is this one compared with the one
using the row_number() built-in functioin with
regard to performance?
query
【在 s**o 的大作中提到】
: Actually it is not. Rownum are a pseudocolumn (not a real column). It will
: be available after the query statement is processed. You may try this query
: to get your result.
: select *
: from ( select e.*, rownum rnum
: from ( select emp.*
: from emp
: order by salary desc ) e
: where rownum <= 2
: )
|
B*****g
发帖数: 34098 | 14 没考虑duplicate value？
query
【在 s**o 的大作中提到】
: Actually it is not. Rownum are a pseudocolumn (not a real column). It will
: be available after the query statement is processed. You may try this query
: to get your result.
: select *
: from ( select e.*, rownum rnum
: from ( select emp.*
: from emp
: order by salary desc ) e
: where rownum <= 2
: )
|
y********o
发帖数: 2565 | 15 do distinct then.
【在 B*****g 的大作中提到】
: 没考虑duplicate value？
:
: query
|
B*****g
发帖数: 34098 | 16 hehe, then how about null value? How about execution time?
What I want to say is simple sql command does not mean is the best solution,
so does a complicate one.
【在 y********o 的大作中提到】
: do distinct then.
|
x***e
发帖数: 2449 | 17 probably you can NOT in SQL Server.
The best might be
select top 5 salary
from [table name]
group by salary
order by salary desc
【在 w*******e 的大作中提到】
: 应该在subquery里加上top 5
|
x***e
发帖数: 2449 | 18 should be:
select top 5 x.*
from (select distinct salary from employees) x
order by salary desc
【在 c**t 的大作中提到】
: in sql server:
: select top 5 x.*
: from (select distinct salary from employees order by salary desc) x
|
w*******e
发帖数: 1622 | 19 你这是把前五个都按desc给了, 人是要2~4位的
【在 x***e 的大作中提到】
: should be:
: select top 5 x.*
: from (select distinct salary from employees) x
: order by salary desc
|
x***e
发帖数: 2449 | 20 That is easy
select top 3 from the top 5 reversely
I only want to show the idea.
details does not matter that much.
run....
【在 w*******e 的大作中提到】
: 你这是把前五个都按desc给了, 人是要2~4位的
|
