Uzyskiwanie rekordów z maksymalną wartością dla każdej grupy zgrupowanych wyników SQL

Poprawne rozwiązanie to:

SELECT o.*
FROM `Persons` o                    # 'o' from 'oldest person in group'
  LEFT JOIN `Persons` b             # 'b' from 'bigger age'
      ON o.Group = b.Group AND o.Age < b.Age
WHERE b.Age is NULL                 # bigger age not found

Jak to działa:

Pasuje do każdego wiersza z o ze wszystkimi wierszami z b o tej samej wartości w kolumnie Group i większej wartości w kolumnie Age. Każdy wiersz z o nie mający maksymalnej wartości swojej grupy w kolumnie Age będzie pasował do jednego lub więcej wierszy z b.

The LEFT JOIN sprawia, że pasuje do najstarszej osoby w grupie (w tym osób, które są same w swojej grupie) z wierszem pełnym NULL S z b ("nie największy wiek w grupie").
użycie INNER JOIN sprawia, że te wiersze nie pasują i są ignorowane.

Klauzula WHERE przechowuje tylko wiersze posiadające NULLs w polach wyodrębnionych z b. Są najstarszymi osobami z każdej grupy.

Dalsze odczyty

To rozwiązanie i wiele innych zostało wyjaśnione w książce Antypatterny SQL: unikanie pułapek programowania baz danych

Moje proste rozwiązanie dla SQLite (i prawdopodobnie MySQL):

SELECT *, MAX(age) FROM mytable GROUP BY `Group`;

Jednak nie działa w PostgreSQL i może na innych platformach.

W PostgreSQL możesz użyć DISTINCT na klauzuli:

SELECT DISTINCT ON ("group") * FROM "mytable" ORDER BY "group", "age" DESC;

Możesz dołączyć przeciwko zapytaniu podrzędnemu, które ciągnie MAX(Group) i Age. Ta metoda jest przenośna w większości RDBMS.

SELECT t1.*
FROM yourTable t1
INNER JOIN
(
    SELECT `Group`, MAX(Age) AS max_age
    FROM yourTable
    GROUP BY `Group`
) t2
    ON t1.`Group` = t2.`Group` AND t1.Age = t2.max_age;

Przy użyciu metody rankingowej.

SELECT @rn :=  CASE WHEN @prev_grp <> groupa THEN 1 ELSE @rn+1 END AS rn,  
   @prev_grp :=groupa,
   person,age,groupa  
FROM   users,(SELECT @rn := 0) r        
HAVING rn=1
ORDER  BY groupa,age DESC,person

Rozwiązanie Axiaca zadziałało dla mnie najlepiej. Miałem jednak dodatkową złożoność: obliczoną "wartość maksymalną", pochodzącą z dwóch kolumn.

Użyjmy tego samego przykładu: chciałbym najstarszej osoby w każdej grupie. Jeśli są ludzie, którzy są równie stare, weź najwyższą osobę.

Musiałem wykonać lewe połączenie dwa razy, aby uzyskać takie zachowanie:

SELECT o1.* WHERE
    (SELECT o.*
    FROM `Persons` o
    LEFT JOIN `Persons` b
    ON o.Group = b.Group AND o.Age < b.Age
    WHERE b.Age is NULL) o1
LEFT JOIN
    (SELECT o.*
    FROM `Persons` o
    LEFT JOIN `Persons` b
    ON o.Group = b.Group AND o.Age < b.Age
    WHERE b.Age is NULL) o2
ON o1.Group = o2.Group AND o1.Height < o2.Height 
WHERE o2.Height is NULL;

Używanie Wyrażeń CTEs - Common Table:

WITH MyCTE(MaxPKID, SomeColumn1)
AS(
SELECT MAX(a.MyTablePKID) AS MaxPKID, a.SomeColumn1
FROM MyTable1 a
GROUP BY a.SomeColumn1
  )
SELECT b.MyTablePKID, b.SomeColumn1, b.SomeColumn2 MAX(b.NumEstado)
FROM MyTable1 b
INNER JOIN MyCTE c ON c.MaxPKID = b.MyTablePKID
GROUP BY b.MyTablePKID, b.SomeColumn1, b.SomeColumn2

--Note: MyTablePKID is the PrimaryKey of MyTable

Nie jestem pewien czy MySQL ma funkcję row_number. Jeśli tak, możesz go użyć, aby uzyskać pożądany rezultat. Na SQL Server możesz zrobić coś podobnego do:

CREATE TABLE p
(
 person NVARCHAR(10),
 gp INT,
 age INT
);
GO
INSERT  INTO p
VALUES  ('Bob', 1, 32);
INSERT  INTO p
VALUES  ('Jill', 1, 34);
INSERT  INTO p
VALUES  ('Shawn', 1, 42);
INSERT  INTO p
VALUES  ('Jake', 2, 29);
INSERT  INTO p
VALUES  ('Paul', 2, 36);
INSERT  INTO p
VALUES  ('Laura', 2, 39);
GO

SELECT  t.person, t.gp, t.age
FROM    (
         SELECT *,
                ROW_NUMBER() OVER (PARTITION BY gp ORDER BY age DESC) row
         FROM   p
        ) t
WHERE   t.row = 1;

Moje rozwiązanie działa tylko wtedy, gdy potrzebujesz odzyskać tylko jedną kolumnę, jednak dla moich potrzeb było najlepsze rozwiązanie pod względem wydajności (używaj tylko jednego zapytania!):

SELECT SUBSTRING_INDEX(GROUP_CONCAT(column_x ORDER BY column_y),',',1) AS xyz,
   column_z
FROM table_name
GROUP BY column_z;

Używa GROUP_CONCAT w celu utworzenia uporządkowanej listy concat, a następnie i substring tylko do pierwszej.

Możesz również spróbować

SELECT * FROM mytable WHERE age IN (SELECT MAX(age) FROM mytable GROUP BY `Group`) ;

Ta metoda ma tę zaletę, że pozwala na uszeregowanie według innej kolumny, a nie niszczenie innych danych. Jest to bardzo przydatne w sytuacji, gdy próbujesz wymienić zamówienia z kolumną dla przedmiotów, wymieniając najcięższe jako pierwsze.

Źródło: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

SELECT person, group,
    GROUP_CONCAT(
        DISTINCT age
        ORDER BY age DESC SEPARATOR ', follow up: '
    )
FROM sql_table
GROUP BY group;

Niech nazwą tabeli będą ludzie

select O.*              -- > O for oldest table
from people O , people T
where O.grp = T.grp and 
O.Age = 
(select max(T.age) from people T where O.grp = T.grp
  group by T.grp)
group by O.grp; 

Jeśli ID (i wszystkie coulmns) jest potrzebne z mytable

SELECT
    *
FROM
    mytable
WHERE
    id NOT IN (
        SELECT
            A.id
        FROM
            mytable AS A
        JOIN mytable AS B ON A. GROUP = B. GROUP
        AND A.age < B.age
    )

W ten sposób otrzymuję n max wierszy na grupę w mysql

SELECT co.id, co.person, co.country
FROM person co
WHERE (
SELECT COUNT(*)
FROM person ci
WHERE  co.country = ci.country AND co.id < ci.id
) < 1
;

Jak to działa:

• self join to the table
• grupy są wykonywane przez co.country = ci.country
• N elementów na Grupę są kontrolowane przez ) < 1 więc dla 3 elementów -)
• aby uzyskać max lub min zależy od: co.id < ci.id
  • co.id
  • co.id > ci.id -min

Pełny przykład tutaj:

Mysql select N max wartości na Grupę

Mam proste rozwiązanie używając WHERE IN

SELECT a.* FROM `mytable` AS a    
WHERE a.age IN( SELECT MAX(b.age) AS age FROM `mytable` AS b GROUP BY b.group )    
ORDER BY a.group ASC, a.person ASC

with CTE as 
(select Person, 
[Group], Age, RN= Row_Number() 
over(partition by [Group] 
order by Age desc) 
from yourtable)`


`select Person, Age from CTE where RN = 1`

Nie użyłbym grupy jako nazwy kolumny, ponieważ jest to słowo zarezerwowane. Jednak po SQL będzie działać.

SELECT a.Person, a.Group, a.Age FROM [TABLE_NAME] a
INNER JOIN 
(
  SELECT `Group`, MAX(Age) AS oldest FROM [TABLE_NAME] 
  GROUP BY `Group`
) b ON a.Group = b.Group AND a.Age = b.oldest