Jaka jest najprostsza kwerenda SQL pozwalająca znaleźć drugą co do wielkości wartość?


168

Jakie jest najprostsze zapytanie SQL do znalezienia drugiej co do wielkości liczby całkowitej w określonej kolumnie?

W kolumnie mogą być zduplikowane wartości.


użyj przesunięcia do tego celu ... wybierz rozszerzenie z [dbo]. [Pracownicy] uporządkuj według rozszerzenia malejąco przesunięcie 2 wiersze pobierz tylko następny 1
wiersz

Odpowiedzi:


294
SELECT MAX( col )
  FROM table
 WHERE col < ( SELECT MAX( col )
                 FROM table )

7
Odpowiedź Matta i Vinoya również dotyczy duplikatów. Załóżmy, że powtórzono największą wartość, a następnie użycie odpowiedzi Matta da poprawną drugą co do wielkości wartość, podczas gdy jeśli użyjesz podejścia 2 górnych desc i min , zamiast tego możesz otrzymać największą wartość.
Pankaj Sharma

A co, jeśli istnieje wiele drugich najwyższych ... Wtedy to nie da wszystkich krotek
Parth Satra

2
Dziękuję, użył tego, aby znaleźć drugą datę ostatniej tutaj
shaijut

Znacznie lepsze niż moje podejście z użyciem ORDER_BY i LIMIT dla wewnętrznego oświadczenia
iig

Zwróć uwagę, że nie zwróci to wyniku, jeśli nie ma żadnych rekordów przed żądanym.
andig

61
SELECT MAX(col) FROM table WHERE col NOT IN (SELECT MAX(col) FROM table);

31

W T-Sql są dwa sposoby:

--filter out the max
select max( col )
from [table]
where col < ( 
    select max( col )
    from [table] )

--sort top two then bottom one
select top 1 col 
from (
    select top 2 col 
    from [table]
    order by col) topTwo
order by col desc 

W Microsoft SQL pierwszy sposób jest dwa razy szybszy niż drugi, nawet jeśli dana kolumna jest zgrupowana.

Dzieje się tak, ponieważ operacja sortowania jest stosunkowo powolna w porównaniu ze skanowaniem tabeli lub indeksu używanym przez maxagregację.

Alternatywnie w Microsoft SQL 2005 i nowszych możesz użyć ROW_NUMBER()funkcji:

select col
from (
    select ROW_NUMBER() over (order by col asc) as 'rowNum', col
    from [table] ) withRowNum 
where rowNum = 2

20

Widzę tutaj zarówno rozwiązania specyficzne dla SQL Server, jak i niektóre rozwiązania specyficzne dla MySQL, więc możesz chcieć wyjaśnić, której bazy danych potrzebujesz. Chociaż gdybym miał zgadywać, powiedziałbym, że SQL Server, ponieważ jest to trywialne w MySQL.

Widzę również rozwiązania, które nie zadziałają, ponieważ nie uwzględniają możliwości duplikatów, więc uważaj, które z nich akceptujesz. Wreszcie widzę kilka, które zadziałają, ale pozwolą na wykonanie dwóch pełnych skanów tabeli. Chcesz się upewnić, że drugi skan dotyczy tylko 2 wartości.

SQL Server (przed 2012):

SELECT MIN([column]) AS [column]
FROM (
    SELECT TOP 2 [column] 
    FROM [Table] 
    GROUP BY [column] 
    ORDER BY [column] DESC
) a

MySQL:

SELECT `column` 
FROM `table` 
GROUP BY `column` 
ORDER BY `column` DESC 
LIMIT 1,1

Aktualizacja:

SQL Server 2012 obsługuje teraz znacznie czystszą (i standardową ) składnię OFFSET / FETCH:

SELECT TOP 2 [column] 
FROM [Table] 
GROUP BY [column] 
ORDER BY [column] DESC
OFFSET 1 ROWS
FETCH NEXT 1 ROWS ONLY;

To właśnie miałem nadzieję zobaczyć. Zaakceptowana odpowiedź okazuje się brzydka, jeśli potrzebujesz jej do pracy n. Ten wytrzymuje ten test.
Robin Maben

@RobinMaben Robin, a co ze scenariuszem, w którym powtarzana jest największa wartość? Załóżmy, że kolumna zawiera liczby od 1 do 100, ale 100 jest powtarzane dwukrotnie. Wtedy to rozwiązanie da drugą co do wielkości wartość 100, co będzie niepoprawne. Dobrze?
Pankaj Sharma

1
@PankajSharma nie, to nadal będzie działać, z powodu klauzuli GROUP BY
Joel Coehoorn

To jest standardowy sposób na zrobienie tego. Zaakceptowaną odpowiedź należy zaktualizować do tej.
Guilherme Melo

1
Pojawia się błąd, który mówi, że nie mogę użyć TOPi OFFSETw tym samym zapytaniu.
Fałszywy

15

Przypuszczam, że możesz zrobić coś takiego:

SELECT * FROM Table ORDER BY NumericalColumn DESC LIMIT 1 OFFSET 1

lub

SELECT * FROM Table ORDER BY NumericalColumn DESC LIMIT (1, 1)

w zależności od serwera bazy danych. Wskazówka: SQL Server nie ma LIMITU.


aktualizacja - SQL Server 2012 dodał klauzulę offset / fetch podobną do powyższej dbadiaries.com/…
iliketocode

Załóżmy, że masz 2 elementy o tej samej wartości, ale jednocześnie największym. Myślę, że musisz to zrobićOFFSET 2
Kangkan

Dodanie klauzuli GROUP BY spowoduje spełnienie warunku powielenia w tym.
Saif

7

Najłatwiej byłoby uzyskać drugą wartość z tego zestawu wyników w aplikacji:

SELECT DISTINCT value FROM Table ORDER BY value DESC LIMIT 2

Ale jeśli musisz wybrać drugą wartość za pomocą SQL, co powiesz na:

SELECT MIN(value) FROM (SELECT DISTINCT value FROM Table ORDER BY value DESC LIMIT 2) AS t

1
Czy uruchomiłeś to na SQL Server?
Craig

1
@Craig - LIMITto składnia MySql, pytanie nie określa wersji SQL.
Keith


4

Bardzo proste zapytanie mające na celu znalezienie drugiej co do wielkości wartości

SELECT `Column` FROM `Table` ORDER BY `Column` DESC LIMIT 1,1;

4

MSSQL

SELECT  *
  FROM [Users]
    order by UserId desc OFFSET 1 ROW 
FETCH NEXT 1 ROW ONLY;

MySQL

SELECT  *
  FROM Users
    order by UserId desc LIMIT 1 OFFSET 1

Nie ma potrzeby wykonywania zapytań podrzędnych ... po prostu pomiń jeden wiersz i wybierz drugie wiersze po zamówieniu, malejąco


3
SELECT MAX(Salary) FROM Employee WHERE Salary NOT IN (SELECT MAX(Salary) FROM Employee )

To zapytanie zwróci maksymalne wynagrodzenie z wyniku - które nie zawiera maksymalnego wynagrodzenia z ogólnej tabeli.


2
Czy możesz edytować, aby wyjaśnić, czym różni się to znacząco od starych odpowiedzi?
Nathan Tuggy

3

Stare pytanie, które znam, ale dało mi to lepszy plan wykonawczy:

 SELECT TOP 1 LEAD(MAX (column)) OVER (ORDER BY column desc)
 FROM TABLE 
 GROUP BY column

3

To jest bardzo prosty kod, możesz spróbować tego: -

np .: nazwa tabeli = test

salary 

1000
1500
1450
7500

Kod MSSQL, aby uzyskać drugą co do wielkości wartość

select salary from test order by salary desc offset 1 rows fetch next 1 rows only;

tutaj „przesunięcie 1 wierszy” oznacza drugi wiersz tabeli, a „pobierz tylko następny 1 wiersz” służy do wyświetlenia tylko tego 1 wiersza. jeśli nie użyjesz opcji „pobierz tylko następny 1 wiersz”, zostaną wyświetlone wszystkie wiersze z drugiego wiersza.


Najbardziej zoptymalizowana i przyjazna dla zasobów odpowiedź. Zaoszczędziłem sporo czasu używając go w moim podzapytaniu. Dzięki.
vibs2006

2

select * from (select ROW_NUMBER() over (Order by Col_x desc) as Row, Col_1
    from table_1)as table_new tn inner join table_1 t1
    on tn.col_1 = t1.col_1
where row = 2

Mam nadzieję, że pomoże to uzyskać wartość dla dowolnego wiersza…


2

Najprostszy ze wszystkich

select sal from salary order by sal desc limit 1 offset 1

1
select min(sal) from emp where sal in 
    (select TOP 2 (sal) from emp order by sal desc)

Uwaga

sal to nazwa kol.
emp to nazwa tabeli


1

Tomek, uwierz, że to się nie powiedzie, gdy w select max([COLUMN_NAME]) from [TABLE_NAME]sekcji zwróconych zostanie więcej niż jedna wartość . tj. gdy w zbiorze danych jest więcej niż 2 wartości.

Nieznaczna modyfikacja zapytania zadziała -

select max([COLUMN_NAME]) from [TABLE_NAME] where [COLUMN_NAME] **IN** 
  ( select max([COLUMN_NAME]) from [TABLE_NAME] )

1
select max(COL_NAME) from TABLE_NAME where COL_NAME in 
    (select COL_NAME from TABLE_NAME where COL_NAME < (select max(COL_NAME) from TABLE_NAME));

podzapytanie zwraca wszystkie wartości inne niż największe. wybierz maksymalną wartość ze zwróconej listy.


1
select col_name
from (
    select dense_rank() over (order by col_name desc) as 'rank', col_name
    from table_name ) withrank 
where rank = 2

1
SELECT 
    * 
FROM 
    table 
WHERE 
    column < (SELECT max(columnq) FROM table) 
ORDER BY 
    column DESC LIMIT 1

1

To najzwyczajniejszy sposób:

SELECT
      Column name
FROM
      Table name 
ORDER BY 
      Column name DESC
LIMIT 1,1

1
select age from student group by id having age<(select max(age) from student)order by age limit 1

1

Jak wspomniałeś zduplikowane wartości. W takim przypadku możesz użyć DISTINCT i GROUP BY, aby znaleźć drugą najwyższą wartość

Oto stół

wynagrodzenie

:

wprowadź opis obrazu tutaj

GRUPUJ WEDŁUG

SELECT  amount FROM  salary 
GROUP by amount
ORDER BY  amount DESC 
LIMIT 1 , 1

ODRĘBNY

SELECT DISTINCT amount
FROM  salary 
ORDER BY  amount DESC 
LIMIT 1 , 1

Pierwsza część LIMIT = indeks początkowy

Druga część LIMITU = ile wartości


1
SELECT MAX(sal) FROM emp
WHERE sal NOT IN (SELECT top 3 sal FROM emp order by sal desc )

zwróci to trzecią najwyższą salę tabeli emp


1
select max(column_name) from table_name
where column_name not in (select max(column_name) from table_name);

nie w to warunek wykluczający najwyższą wartość nazwa_kolumny.

Odniesienie: wywiad z programistą


0

Coś takiego? Jednak nie testowałem tego:

select top 1 x
from (
  select top 2 distinct x 
  from y 
  order by x desc
) z
order by x


0

Korzystanie z zapytania skorelowanego:

Select * from x x1 where 1 = (select count(*) from x where x1.a < a)

0
select * from emp e where 3>=(select count(distinct salary)
    from emp where s.salary<=salary)

To zapytanie wybiera maksymalnie trzy pensje. Jeśli dwa pracownicy otrzymują taką samą pensję, nie ma to wpływu na zapytanie.


0
select top 1 MyIntColumn from MyTable
where
 MyIntColumn <> (select top 1 MyIntColumn from MyTable order by MyIntColumn desc)
order by MyIntColumn desc

0

Działa to w MS SQL:

select max([COLUMN_NAME]) from [TABLE_NAME] where [COLUMN_NAME] < 
 ( select max([COLUMN_NAME]) from [TABLE_NAME] )
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.