To get the 2nd, 3rd, 4th largest value - 未名空间MITBBS历史存档

国际科技财经博客移民网络热点娱乐民生时事公众号

Redian新闻

>未名空间

>Database - 数据库

To get the 2nd, 3rd, 4th largest value

To get the 2nd, 3rd, 4th largest value# Database - 数据库

y*o2007-05-16 07:05

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*12007-05-16 07:05

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*o2007-05-16 07:05

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*t2007-05-16 07:05

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*o2007-05-16 07:05

5 楼

才试了一下这个，确实不错，比 Oracle 的那个简洁明快。

【在 q**1 的大作中提到】

: 这个在mysql里面比较简单（选第3个）：
: select salary from employees
: order by salary desc
: limit 2,1;

q*12007-05-16 07:05

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*t2007-05-16 07:05

7 楼

in sql server:
select top 5 x.*
from (select distinct salary from employees order by salary desc) x

【在 y********o 的大作中提到】

y*o2007-05-16 07:05

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*12007-05-16 07:05

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*o2007-05-16 07:05

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*e2007-05-16 07:05

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*o2007-05-16 07:05

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*o2007-05-16 07:05

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*g2007-05-16 07:05

14 楼

没考虑duplicate value？

query

【在 s**o 的大作中提到】

y*o2007-05-16 07:05

15 楼

do distinct then.

【在 B*****g 的大作中提到】

: 没考虑duplicate value？
:
: query

B*g2007-05-16 07:05

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*e2007-05-16 07:05

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*e2007-05-16 07:05

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*e2007-05-16 07:05

19 楼

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

【在 x***e 的大作中提到】

: should be:
: select top 5 x.*
: from (select distinct salary from employees) x
: order by salary desc

x*e2007-05-16 07:05

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位的