Jak wybrać najniższe wiersze?


100

Mogę zrobić SELECT TOP (200) ... ale dlaczego nie BOTTOM (200)?

Cóż, nie wchodząc w filozofię, mam na myśli to, jak mogę zrobić odpowiednik TOP (200), ale odwrotnie (od dołu, tak jak można by się spodziewać, że zrobi to BOTTOM ...)?

Odpowiedzi:


89
SELECT
    columns
FROM
(
     SELECT TOP 200
          columns
     FROM
          My_Table
     ORDER BY
          a_column DESC
) SQ
ORDER BY
     a_column ASC

2
Dlaczego używasz tabeli pochodnej?
RichardOD

14
Jeśli chcesz zwrócić wiersze w kolejności A-> Z, ale wybierz 200 pierwszych w kolejności Z-> A, to jest jeden ze sposobów, aby to zrobić. Pozostałe odpowiedzi, sugerujące samą zmianę ORDER BY, nie zwrócą tych samych wyników, które opisano w pytaniu, ponieważ będą one nie w kolejności (chyba że kolejność nie ma znaczenia, czego OP nie powiedział).
Tom H

3
@Tom H. Musiałem pomyśleć przez kilka sekund, co masz na myśli (nie spałem 14 godzin). Na początku nie widziałem różnicy między twoją a kolejnością po odpowiedziach, ale teraz widzę. Więc +1.
RichardOD

1
Ta i inne odpowiedzi działają dobrze, gdy pracujesz na mniejszych stołach. Myślę, że nie warto porządkować całej tabeli według kolumny, gdy interesuje Cię tylko kilka ostatnich wierszy.
steadyfish

1
dobra odpowiedź, najlepsza imho ... prawdziwy problem polega na tym, że nie mogę tego zrobić ze skryptu ASP, więc myślę, że muszę zmienić kolejność objRecordset ręcznie lub za pomocą funkcji, którą zapewnia ASP ....
Andrea_86

98

To jest niepotrzebne. Możesz użyć ORDER BYi po prostu zmienić sortowanie na, DESCaby uzyskać ten sam efekt.


3
google powiedziało to samo i teraz 9 z was się zgadza, dla mnie wystarczy, dzięki: D
CloudMeta,

9
Użycie DESC zwróci ostatnie N wierszy, ale zwrócone wiersze również będą w odwrotnej kolejności od pierwszych N wierszy.
RickNZ

11
Co się stanie, jeśli na Twoim stole nie ma indeksu do ZAMÓWIENIA?
Protector One

8
@Justin: Wyobraź sobie, że masz tylko jedną kolumnę zawierającą wartości varchar. ORDER BY posortowałoby alfabetycznie, co (prawdopodobnie) nie jest tym, czego chcemy.
Obrońca 1

3
Tom H. jest poprawną odpowiedzią, w przeciwnym razie twoje rzędy będą w odwrotnej kolejności.
Pierre-Olivier Goulet

39

Przepraszam, ale moim zdaniem nie widzę żadnych poprawnych odpowiedzi.

Funkcja TOPx wyświetla rekordy w nieokreślonej kolejności. Z tej definicji wynika, że ​​aBOTTOM funkcji nie można zdefiniować.

Niezależnie od jakiegokolwiek indeksu lub porządku sortowania. Kiedy to zrobisz, ORDER BY y DESCnajpierw otrzymasz wiersze z najwyższą wartością y. Jeśli jest to identyfikator wygenerowany automatycznie, powinien pokazywać rekordy ostatnio dodane do tabeli, jak sugerowano w innych odpowiedziach. Jednak:

  • Działa to tylko wtedy, gdy istnieje automatycznie generowana kolumna identyfikatora
  • Porównując to z TOPfunkcją, ma to znaczący wpływ na wydajność

Prawidłowa odpowiedź powinna być taka, że ​​nie ma i nie może być odpowiednika TOPdla uzyskania najniższych wierszy.


3
To prawda, wydaje się, że nie ma odpowiednika, tylko obejścia.
poke

4
TOP nie ma nic wspólnego z kolejnością, w jakiej elementy zostały dodane do tabeli, oznacza po prostu „Podaj pierwsze X rekordów, które pasują do mojego zapytania”
Luke

4
Tak, daje pierwsze rekordy dodane do tabeli, które pasują do zapytania.
Martijn Burger

4
Zgadzam się z Łukaszem. Tabele bazy danych są z definicji nieporządane. Nigdy nie należy polegać na kolejności dostarczonej przez RDBMS, gdy instrukcja select nie ma klauzuli ORDER BY. przeczytaj tutaj, na wiki Jednak system bazy danych nie gwarantuje żadnej kolejności wierszy, chyba że w instrukcji SELECT, która wysyła zapytanie do tabeli, jest określona klauzula ORDER BY.
Zohar Peled

2
Zgadzam się z tą odpowiedzią, ale w praktyce odpowiedź Toma rozwiązuje problem we wszystkich praktycznych zastosowaniach.
Antonio

18

Logicznie

BOTTOM (x) is all the records except TOP (n - x), where n is the count; x <= n

Np. Wybierz dolne 1000 od pracownika:

W T-SQL

DECLARE 
@bottom int,
@count int

SET @bottom = 1000 
SET @count = (select COUNT(*) from Employee)

select * from Employee emp where emp.EmployeeID not in 
(
SELECT TOP (@count-@bottom) Employee.EmployeeID FROM Employee
)

1
Cześć shadi2014, bez użycia opcji „ZAMÓWIENIE” Twój wynik będzie w jakiś sposób losowy.
bummi

5
bummi, masz rację, ale to sprawia, że ​​ta odpowiedź jest poprawna. Samo Select TOP jest w teorii „losowe” i jest to poprawna implementacja Select BOTTOM. W tabeli zawierającej 5000 rekordów, dolne 1000 to wszystko z wyjątkiem pierwszych 4000.
tzachs

9

Wydawałoby się, że którakolwiek z odpowiedzi, które implementują klauzulę ORDER BY w rozwiązaniu, nie ma sensu lub w rzeczywistości nie rozumie, co TOP Ci zwraca.

TOP zwraca nieuporządkowany zestaw wyników zapytania, który ogranicza zestaw rekordów do pierwszych N zwróconych rekordów. (Z punktu widzenia Oracle jest to podobne do dodania gdzie ROWNUM <(N + 1).

Każde rozwiązanie korzystające z zamówienia może zwrócić wiersze, które również są zwracane przez klauzulę TOP (ponieważ ten zbiór danych był w pierwszej kolejności nieuporządkowany), w zależności od kryteriów zastosowanych w zamówieniu przez

Użyteczność TOP polega na tym, że gdy zbiór danych osiągnie pewien rozmiar N, przestaje pobierać wiersze. Możesz zobaczyć, jak wyglądają dane, bez konieczności pobierania ich wszystkich.

Aby dokładnie zaimplementować BOTTOM, należałoby pobrać cały zestaw danych w nieuporządkowany sposób, a następnie ograniczyć go do ostatecznych N rekordów. Nie będzie to szczególnie skuteczne, jeśli masz do czynienia z dużymi stołami. Niekoniecznie też da ci to, co myślisz że prosisz. Koniec zbioru danych niekoniecznie musi oznaczać „ostatnie wstawione wiersze” (i prawdopodobnie nie będzie to miało miejsce w przypadku większości aplikacji intensywnie korzystających z DML).

Podobnie rozwiązania, które implementują ORDER BY są niestety potencjalnie katastrofalne w przypadku dużych zbiorów danych. Jeśli mam, powiedzmy, 10 miliardów rekordów i chcę ostatnich 10, to głupotą jest zamówienie 10 miliardów rekordów i wybranie ostatnich 10.

Problem polega na tym, że BOTTOM nie ma takiego znaczenia, o jakim myślimy , porównując go z TOP.

Kiedy rekordy są wstawiane, usuwane, wstawiane i usuwane w kółko, w pamięci pojawią się luki, a później wiersze zostaną wstawione, jeśli to możliwe. Ale to, co często widzimy, kiedy wybieramy TOP, wydaje się być posortowanymi danymi, ponieważ mogły zostać wstawione na początku istnienia tabeli. Jeśli w tabeli nie występuje wiele usunięć, może wydawać się, że jest uporządkowana. (np. daty utworzenia mogą być tak odległe w czasie, jak samo utworzenie tabeli). Ale rzeczywistość jest taka, że ​​jeśli jest to tabela z dużą ilością usuwania, to TOP N wierszy może wcale tak nie wyglądać.

Tak więc - najważniejsze tutaj (gra słów zamierzona) jest to, że ktoś, kto prosi o nagrania BOTTOM N, tak naprawdę nie wie, o co proszą. A przynajmniej to, o co proszą i co właściwie oznacza DOLNY, nie jest tym samym.

A więc - rozwiązanie może spełniać rzeczywiste potrzeby biznesowe żądającego… ale nie spełnia kryteriów bycia DOLNYM.


1
Doskonałe wyjaśnienie. Głosuj za rzuceniem więcej światła na ten temat.
rohrl77

Oto mój przypadek użycia. Uruchomiłem duże insertpolecenie, aby umieścić wiersze w dużej, niezindeksowanej tabeli. (Najpierw zapełniam tabelę, zanim zacznę ją indeksować). Straciłem sesję klienta z powodu ponownego uruchomienia komputera lub czegoś innego, a teraz chcę sprawdzić, czy są tam moje nowo dodane wiersze. Jeśli „dolny” wiersz tabeli jest jednym z ostatnich, to wiem, że operacja została zakończona. Jeśli „dolny” wiersz jest czymś innym, cóż, nie ma żadnych gwarancji i muszę przeskanować całą tabelę, aby się upewnić ... ale najprawdopodobniej mógłbym zaoszczędzić trochę czasu, szybko sprawdzając „dół”, tak jak możesz. Top'.
Ed Avis

Dobre wyjaśnienie, ale nadal sugeruje istnienie dna, które wymaga jedynie odwrotnego odczytu / pobierania danych. W przypadku (co prawda krawędzi) przerwanego wstawiania w nowej tabeli, przydatne byłoby sprawdzenie ostatniego wstawionego rekordu (na dole) bez pobierania wszystkiego. Czy istnieje techniczny powód, dla którego danych tabeli nie można pobrać w odwrotnej kolejności?
James

3

Aktualnie przyjęta odpowiedź przez „Justina Ethiera” nie jest poprawną odpowiedzią, jak wskazał „Protektor jeden”.

O ile widzę, na razie żadna inna odpowiedź ani komentarz nie jest odpowiednikiem BOTTOM (x), o które zadał autor pytania.

Najpierw rozważmy scenariusz, w którym ta funkcjonalność byłaby potrzebna:

SELECT * FROM Split('apple,orange,banana,apple,lime',',')

Zwraca tabelę składającą się z jednej kolumny i pięciu rekordów:

  • jabłko
  • Pomarańczowy
  • banan
  • jabłko
  • Limonka

Jak widać: nie mamy kolumny ID; nie możemy złożyć zamówienia według zwróconej kolumny; i nie możemy wybrać dwóch ostatnich rekordów za pomocą standardowego SQL, tak jak możemy to zrobić dla dwóch górnych rekordów.

Oto moja próba rozwiązania:

SELECT * INTO #mytemptable FROM Split('apple,orange,banana,apple,lime',',')
ALTER TABLE #mytemptable ADD tempID INT IDENTITY
SELECT TOP 2 * FROM #mytemptable ORDER BY tempID DESC
DROP TABLE #mytemptable

A oto bardziej kompletne rozwiązanie:

SELECT * INTO #mytemptable FROM Split('apple,orange,banana,apple,lime',',')
ALTER TABLE #mytemptable ADD tempID INT IDENTITY
DELETE FROM #mytemptable WHERE tempID <= ((SELECT COUNT(*) FROM #mytemptable) - 2)
ALTER TABLE #mytemptable DROP COLUMN tempID
SELECT * FROM #mytemptable
DROP TABLE #mytemptable

W żadnym wypadku nie twierdzę, że jest to dobry pomysł w każdych okolicznościach, ale zapewnia pożądane rezultaty.



1

Problem z kolejnością w drugą stronę polega na tym, że często nie wykorzystuje ona dobrze indeksów. Nie można go również zbytnio rozszerzać, jeśli kiedykolwiek zajdzie potrzeba wybrania liczby wierszy, które nie znajdują się na początku ani na końcu. Alternatywny sposób jest następujący.

DECLARE @NumberOfRows int;
SET @NumberOfRows = (SELECT COUNT(*) FROM TheTable);

SELECT col1, col2,...
FROM (
    SELECT col1, col2,..., ROW_NUMBER() OVER (ORDER BY col1) AS intRow
    FROM TheTable
) AS T
WHERE intRow > @NumberOfRows - 20;

2
1) Jeśli zmiana kierunku klauzuli ORDER BY „nie powoduje dobrego wykorzystania indeksów”, zdobądź przyzwoity RDBMS! RDBMS nigdy nie powinien dbać o to, czy nawiguje po indeksie do przodu czy do tyłu. 2) Martwisz się użyciem indeksów, ale Twoje rozwiązanie dołącza sekwencję do każdego wiersza w tabeli ... To jeden ze sposobów na zagwarantowanie, że odpowiedni indeks NIE zostanie użyty.
Rozczarowany

1

Powyższa odpowiedź „Tom H” jest prawidłowa i działa w moim przypadku przy uzyskiwaniu 5 ostatnich wierszy.

SELECT [KeyCol1], [KeyCol2], [Col3]
FROM
(SELECT TOP 5 [KeyCol1],
       [KeyCol2],
       [Col3]
  FROM [dbo].[table_name]
  ORDER BY [KeyCol1],[KeyCol2] DESC) SOME_ALAIS
  ORDER BY [KeyCol1],[KeyCol2] ASC

Dzięki.


0

Spróbuj tego.

declare @floor int --this is the offset from the bottom, the number of results to exclude
declare @resultLimit int --the number of results actually retrieved for use
declare @total int --just adds them up, the total number of results fetched initially

--following is for gathering top 60 results total, then getting rid of top 50. We only keep the last 10
set @floor = 50 
set @resultLimit = 10
set @total = @floor + @resultLimit

declare @tmp0 table(
    --table body
)

declare @tmp1 table(
    --table body
)

--this line will drop the wanted results from whatever table we're selecting from
insert into @tmp0
select Top @total --what to select (the where, from, etc)

--using floor, insert the part we don't want into the second tmp table
insert into @tmp1
select top @floor * from @tmp0

--using select except, exclude top x results from the query
select * from @tmp0
except 
select * from @tmp1

co sprawia, że ​​twój kod dla OP? Dodaj trochę więcej wyjaśnień, jak próbujesz rozwiązać problem
techspider

Dokonałem edycji. Mam nadzieję, że wyjaśnia to trochę lepiej, dodając więcej komentarzy. Głównym pomysłem jest wybranie górnego x z tabeli, następnie wybranie żądanego górnego x - liczba, a następnie użycie instrukcji except do wykluczenia niepotrzebnych wyników.
HumbleWebDev

0

Wymyśliłem rozwiązanie tego problemu, które nie wymaga znajomości liczby zwróconych wierszy.

Na przykład, jeśli chcesz, aby wszystkie lokalizacje zostały zarejestrowane w tabeli, z wyjątkiem ostatniej 1 (lub 2 lub 5 lub 34)

SELECT * 
FROM
    (SELECT ROW_NUMBER() OVER (ORDER BY CreatedDate) AS Row, * 
    FROM Locations
    WHERE UserId = 12345) AS SubQuery
WHERE Row > 1 -- or 2, or 5, or 34

0

Zapytanie o proste podzapytanie posortowane malejąco, a następnie sortowanie rosnąco na tej samej kolumnie załatwia sprawę.

SELECT * FROM 
    (SELECT TOP 200 * FROM [table] t2 ORDER BY t2.[column] DESC) t1
    ORDER BY t1.[column]


0

Najpierw utwórz indeks w podzapytaniu zgodnie z pierwotną kolejnością tabeli, używając:

ROW_NUMBER () OVER (ORDER BY (SELECT NULL) ) AS RowIndex

Następnie uporządkuj tabelę malejąco według RowIndexkolumny utworzonej w głównym zapytaniu:

ORDER BY RowIndex DESC

I na koniec użyj TOPz żądaną liczbą rzędów:

    SELECT TOP 1 * --(or 2, or 5, or 34)
    FROM   (SELECT ROW_NUMBER() OVER (ORDER BY  (SELECT NULL) ) AS RowIndex, * 
            FROM MyTable) AS SubQuery
    ORDER BY RowIndex DESC
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.