Jak Mogę wybrać wiersze z MAX (wartość kolumny), oddzielone przez inną kolumnę w SQL?

Here goes T-SQL version:

-- Test data
DECLARE @TestTable TABLE (id INT, home INT, date DATETIME, 
  player VARCHAR(20), resource INT)
INSERT INTO @TestTable
SELECT 1, 10, '2009-03-04', 'john', 399 UNION
SELECT 2, 11, '2009-03-04', 'juliet', 244 UNION
SELECT 5, 12, '2009-03-04', 'borat', 555 UNION
SELECT 3, 10, '2009-03-03', 'john', 300 UNION
SELECT 4, 11, '2009-03-03', 'juliet', 200 UNION
SELECT 6, 12, '2009-03-03', 'borat', 500 UNION
SELECT 7, 13, '2008-12-24', 'borat', 600 UNION
SELECT 8, 13, '2009-01-01', 'borat', 700

-- Answer
SELECT id, home, date, player, resource 
FROM (SELECT id, home, date, player, resource, 
    RANK() OVER (PARTITION BY home ORDER BY date DESC) N
    FROM @TestTable
)M WHERE N = 1

-- and if you really want only home with max date
SELECT T.id, T.home, T.date, T.player, T.resource 
    FROM @TestTable T
INNER JOIN 
(   SELECT TI.id, TI.home, TI.date, 
        RANK() OVER (PARTITION BY TI.home ORDER BY TI.date) N
    FROM @TestTable TI
    WHERE TI.date IN (SELECT MAX(TM.date) FROM @TestTable TM)
)TJ ON TJ.N = 1 AND T.id = TJ.id

EDIT
Niestety, w MySQL nie ma funkcji RANK () OVER.
Ale można go emulować, zobacz emulowanie funkcji analitycznych (Aka Ranking) za pomocą MySQL .
Więc to jest MySQL wersja:

SELECT id, home, date, player, resource 
FROM TestTable AS t1 
WHERE 
    (SELECT COUNT(*) 
            FROM TestTable AS t2 
            WHERE t2.home = t1.home AND t2.date > t1.date
    ) = 0

Najszybsze rozwiązanie MySQL, bez wewnętrznych zapytań i bez GROUP BY:

SELECT m.*                    -- get the row that contains the max value
FROM topten m                 -- "m" from "max"
    LEFT JOIN topten b        -- "b" from "bigger"
        ON m.home = b.home    -- match "max" row with "bigger" row by `home`
        AND m.datetime < b.datetime           -- want "bigger" than "max"
WHERE b.datetime IS NULL      -- keep only if there is no bigger than max

Wyjaśnienie :

Połącz tabelę z sobą za pomocą kolumny home. Użycie LEFT JOIN zapewnia, że wszystkie wiersze z tabeli m pojawią się w zestawie wyników. Te, które nie mają dopasowania w tabeli bbędą miały NULL s dla kolumn b.

Drugi warunek JOIN wymaga dopasowania tylko wierszy z b, które mają większą wartość w kolumnie datetime niż wiersz od m.

Korzystając z danych zamieszczonych w pytaniu, LEFT JOIN wytworzy te pary:

+------------------------------------------+--------------------------------+
|              the row from `m`            |    the matching row from `b`   |
|------------------------------------------|--------------------------------|
| id  home  datetime     player   resource | id    home   datetime      ... |
|----|-----|------------|--------|---------|------|------|------------|-----|
| 1  | 10  | 04/03/2009 | john   | 399     | NULL | NULL | NULL       | ... | *
| 2  | 11  | 04/03/2009 | juliet | 244     | NULL | NULL | NULL       | ... | *
| 5  | 12  | 04/03/2009 | borat  | 555     | NULL | NULL | NULL       | ... | *
| 3  | 10  | 03/03/2009 | john   | 300     | 1    | 10   | 04/03/2009 | ... |
| 4  | 11  | 03/03/2009 | juliet | 200     | 2    | 11   | 04/03/2009 | ... |
| 6  | 12  | 03/03/2009 | borat  | 500     | 5    | 12   | 04/03/2009 | ... |
| 7  | 13  | 24/12/2008 | borat  | 600     | 8    | 13   | 01/01/2009 | ... |
| 8  | 13  | 01/01/2009 | borat  | 700     | NULL | NULL | NULL       | ... | *
+------------------------------------------+--------------------------------+

Wreszcie, WHERE klauzula zachowuje tylko pary, które mają NULL s w kolumnach b (są one oznaczone * w tabeli powyżej); oznacza to, ze względu na drugi warunek z JOIN klauzula, wiersz wybrany z m ma największą wartość w kolumnie datetime.

Przeczytaj Antypatterns SQL: unikanie pułapek programowania baz danych książka dla inne porady SQL.

To zadziała, nawet jeśli masz dwa lub więcej wierszy dla każdego {[1] } z równymi DATETIME ' S:

SELECT id, home, datetime, player, resource
FROM   (
       SELECT (
              SELECT  id
              FROM    topten ti
              WHERE   ti.home = t1.home
              ORDER BY
                      ti.datetime DESC
              LIMIT 1
              ) lid
       FROM   (
              SELECT  DISTINCT home
              FROM    topten
              ) t1
       ) ro, topten t2
WHERE  t2.id = ro.lid

Myślę, że to da Ci pożądany efekt:

SELECT   home, MAX(datetime)
FROM     my_table
GROUP BY home

Ale jeśli potrzebujesz również innych kolumn, po prostu wykonaj połączenie z oryginalną tabelą (sprawdź Michael La Voie ODPOWIEDŹ)

Skoro ludzie ciągle wpadają w ten wątek (Data komentarza waha się od 1,5 roku) nie jest o wiele prostsze:

SELECT * FROM (SELECT * FROM topten ORDER BY datetime DESC) tmp GROUP BY home

Nie są potrzebne funkcje agregacji...

Możesz również spróbować tego, a dla dużych tabel wydajność zapytań będzie lepsza. Działa, gdy nie ma więcej niż dwóch rekordów dla każdego domu, a ich daty są różne. Lepiej ogólne zapytanie MySQL jest jednym z Michael La Voie powyżej.

SELECT t1.id, t1.home, t1.date, t1.player, t1.resource
FROM   t_scores_1 t1 
INNER JOIN t_scores_1 t2
   ON t1.home = t2.home
WHERE t1.date > t2.date

Lub w przypadku Postgres lub tych dbs, które zapewniają funkcje analityczne spróbuj

SELECT t.* FROM 
(SELECT t1.id, t1.home, t1.date, t1.player, t1.resource
  , row_number() over (partition by t1.home order by t1.date desc) rw
 FROM   topten t1 
 INNER JOIN topten t2
   ON t1.home = t2.home
 WHERE t1.date > t2.date 
) t
WHERE t.rw = 1

To działa na Oracle:

with table_max as(
  select id
       , home
       , datetime
       , player
       , resource
       , max(home) over (partition by home) maxhome
    from table  
)
select id
     , home
     , datetime
     , player
     , resource
  from table_max
 where home = maxhome

SELECT  tt.*
FROM    TestTable tt 
INNER JOIN 
        (
        SELECT  coord, MAX(datetime) AS MaxDateTime 
        FROM    rapsa 
        GROUP BY
                krd 
        ) groupedtt
ON      tt.coord = groupedtt.coord
        AND tt.datetime = groupedtt.MaxDateTime

Spróbuj tego dla SQL Server:

WITH cte AS (
   SELECT home, MAX(year) AS year FROM Table1 GROUP BY home
)
SELECT * FROM Table1 a INNER JOIN cte ON a.home = cte.home AND a.year = cte.year

SELECT c1, c2, c3, c4, c5 FROM table1 WHERE c3 = (select max(c3) from table)

SELECT * FROM table1 WHERE c3 = (select max(c3) from table1)

Oto wersja MySQL, która wyświetla tylko jeden wpis, w którym w grupie znajdują się duplikaty MAX(datetime).

Możesz przetestować tutaj http://www.sqlfiddle.com/#! 2 / 0a4ae/1

Przykładowe Dane

mysql> SELECT * from topten;
+------+------+---------------------+--------+----------+
| id   | home | datetime            | player | resource |
+------+------+---------------------+--------+----------+
|    1 |   10 | 2009-04-03 00:00:00 | john   |      399 |
|    2 |   11 | 2009-04-03 00:00:00 | juliet |      244 |
|    3 |   10 | 2009-03-03 00:00:00 | john   |      300 |
|    4 |   11 | 2009-03-03 00:00:00 | juliet |      200 |
|    5 |   12 | 2009-04-03 00:00:00 | borat  |      555 |
|    6 |   12 | 2009-03-03 00:00:00 | borat  |      500 |
|    7 |   13 | 2008-12-24 00:00:00 | borat  |      600 |
|    8 |   13 | 2009-01-01 00:00:00 | borat  |      700 |
|    9 |   10 | 2009-04-03 00:00:00 | borat  |      700 |
|   10 |   11 | 2009-04-03 00:00:00 | borat  |      700 |
|   12 |   12 | 2009-04-03 00:00:00 | borat  |      700 |
+------+------+---------------------+--------+----------+

Wersja MySQL ze zmienną Użytkownika

SELECT *
FROM (
    SELECT ord.*,
        IF (@prev_home = ord.home, 0, 1) AS is_first_appear,
        @prev_home := ord.home
    FROM (
        SELECT t1.id, t1.home, t1.player, t1.resource
        FROM topten t1
        INNER JOIN (
            SELECT home, MAX(datetime) AS mx_dt
            FROM topten
            GROUP BY home
          ) x ON t1.home = x.home AND t1.datetime = x.mx_dt
        ORDER BY home
    ) ord, (SELECT @prev_home := 0, @seq := 0) init
) y
WHERE is_first_appear = 1;
+------+------+--------+----------+-----------------+------------------------+
| id   | home | player | resource | is_first_appear | @prev_home := ord.home |
+------+------+--------+----------+-----------------+------------------------+
|    9 |   10 | borat  |      700 |               1 |                     10 |
|   10 |   11 | borat  |      700 |               1 |                     11 |
|   12 |   12 | borat  |      700 |               1 |                     12 |
|    8 |   13 | borat  |      700 |               1 |                     13 |
+------+------+--------+----------+-----------------+------------------------+
4 rows in set (0.00 sec)

Accepted Answers ' outout

SELECT tt.*
FROM topten tt
INNER JOIN
    (
    SELECT home, MAX(datetime) AS MaxDateTime
    FROM topten
    GROUP BY home
) groupedtt ON tt.home = groupedtt.home AND tt.datetime = groupedtt.MaxDateTime
+------+------+---------------------+--------+----------+
| id   | home | datetime            | player | resource |
+------+------+---------------------+--------+----------+
|    1 |   10 | 2009-04-03 00:00:00 | john   |      399 |
|    2 |   11 | 2009-04-03 00:00:00 | juliet |      244 |
|    5 |   12 | 2009-04-03 00:00:00 | borat  |      555 |
|    8 |   13 | 2009-01-01 00:00:00 | borat  |      700 |
|    9 |   10 | 2009-04-03 00:00:00 | borat  |      700 |
|   10 |   11 | 2009-04-03 00:00:00 | borat  |      700 |
|   12 |   12 | 2009-04-03 00:00:00 | borat  |      700 |
+------+------+---------------------+--------+----------+
7 rows in set (0.00 sec)

Inny sposób na gt najnowszy wiersz na Grupę za pomocą zapytania podrzędnego, które w zasadzie oblicza rangę dla każdego wiersza na grupę, a następnie odfiltrowuje najnowsze wiersze jak z rank = 1

select a.*
from topten a
where (
  select count(*)
  from topten b
  where a.home = b.home
  and a.`datetime` < b.`datetime`
) +1 = 1

DEMO

Oto visual demo dla rangi nie dla każdego wiersza dla lepszego zrozumienia

Czytając kilka komentarzy a jeśli są dwa wiersze, które mają takie same wartości pól' home 'i' datetime'?

Powyższe zapytanie będzie fail i zwróci więcej niż 1 wiersz dla powyższej sytuacji. Aby zatuszować tę sytuację, będzie potrzeba innego kryterium / parametru / kolumny, aby zdecydować, który wiersz należy przyjąć, który wypada w powyższej sytuacji. Przeglądając przykładowy zestaw danych zakładam, że istnieje kolumna klucza głównego id, która powinna być ustawiona na auto increment. Możemy więc użyć tej kolumny, aby wybrać najnowszy wiersz, poprawiając to samo zapytanie za pomocą instrukcji CASE, takiej jak

select a.*
from topten a
where (
  select count(*)
  from topten b
  where a.home = b.home
  and  case 
       when a.`datetime` = b.`datetime`
       then a.id < b.id
       else a.`datetime` < b.`datetime`
       end
) + 1 = 1

DEMO

Powyższe zapytanie wybierze wiersz o najwyższym id spośród tych samych wartości datetime

Visual demo dla Rang no dla każdego wiersza

Spróbuj tego

select * from mytable a join
(select home, max(datetime) datetime
from mytable
group by home) b
 on a.home = b.home and a.datetime = b.datetime

Pozdrawiam K

Dlaczego nie używać: Wybierz home, MAX (datetime) jako MaxDateTime, player, resource z grupy topten by home Coś mnie ominęło?

To jest zapytanie, którego potrzebujesz:

 SELECT b.id, a.home,b.[datetime],b.player,a.resource FROM
 (SELECT home,MAX(resource) AS resource FROM tbl_1 GROUP BY home) AS a

 LEFT JOIN

 (SELECT id,home,[datetime],player,resource FROM tbl_1) AS b
 ON  a.resource = b.resource WHERE a.home =b.home;

@ Michae zaakceptowana odpowiedź będzie działać dobrze w większości przypadków, ale nie dla jednego Dla jak poniżej.

W przypadku, gdy były 2 wiersze posiadające HomeID i Datetime same zapytanie zwróci oba wiersze, a nie odrębny HomeID zgodnie z wymaganiami, aby dodać Distinct w zapytaniu jak poniżej.

SELECT DISTINCT tt.home  , tt.MaxDateTime
FROM topten tt
INNER JOIN
    (SELECT home, MAX(datetime) AS MaxDateTime
    FROM topten
    GROUP BY home) groupedtt 
ON tt.home = groupedtt.home 
AND tt.datetime = groupedtt.MaxDateTime