Redian新闻
>
To get the 2nd, 3rd, 4th largest value
avatar
To get the 2nd, 3rd, 4th largest value# Database - 数据库
y*o
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;
avatar
q*1
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;

avatar
y*o
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;

avatar
a*t
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.

avatar
y*o
5
才试了一下这个,确实不错,比 Oracle 的那个简洁明快。

【在 q**1 的大作中提到】
: 这个在mysql里面比较简单(选第3个):
: select salary from employees
: order by salary desc
: limit 2,1;

avatar
q*1
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 的那个简洁明快。
avatar
c*t
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;

avatar
y*o
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;

avatar
q*1
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

avatar
y*o
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

avatar
w*e
11
应该在subquery里加上top 5

【在 c**t 的大作中提到】
: in sql server:
: select top 5 x.*
: from (select distinct salary from employees order by salary desc) x

avatar
s*o
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.

avatar
y*o
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
: )

avatar
B*g
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
: )

avatar
y*o
15
do distinct then.

【在 B*****g 的大作中提到】
: 没考虑duplicate value?
:
: query

avatar
B*g
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.
avatar
x*e
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
avatar
x*e
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

avatar
w*e
19
你这是把前五个都按desc给了, 人是要2~4位的

【在 x***e 的大作中提到】
: should be:
: select top 5 x.*
: from (select distinct salary from employees) x
: order by salary desc

avatar
x*e
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位的
相关阅读
logo
联系我们隐私协议©2024 redian.news
Redian新闻
Redian.news刊载任何文章,不代表同意其说法或描述,仅为提供更多信息,也不构成任何建议。文章信息的合法性及真实性由其作者负责,与Redian.news及其运营公司无关。欢迎投稿,如发现稿件侵权,或作者不愿在本网发表文章,请版权拥有者通知本网处理。