Pobranie wiersza, który ma maksymalną wartość dla kolumny

Widzę, że wiele osób używa do tego subqueries lub innych funkcji specyficznych dla dostawcy, ale często robię tego rodzaju zapytania bez subqueries w następujący sposób. Używa zwykłego, standardowego SQL, więc powinien działać w dowolnej marce RDBMS.

SELECT t1.*
FROM mytable t1
  LEFT OUTER JOIN mytable t2
    ON (t1.UserId = t2.UserId AND t1."Date" < t2."Date")
WHERE t2.UserId IS NULL;

Innymi słowy: pobranie wiersza z t1 gdzie żaden inny wiersz nie istnieje z tą samą UserId i większą datą.

(dodałem identyfikator "Date" w ogranicznikach, ponieważ jest to słowo zarezerwowane SQL.)

W przypadku, gdy t1."Date" = t2."Date" pojawia się podwojenie. Zwykle tabele mają auto_inc(seq) klucz, np. id. Aby uniknąć podwojenia można użyć następujących słów:

SELECT t1.*
FROM mytable t1
  LEFT OUTER JOIN mytable t2
    ON t1.UserId = t2.UserId AND ((t1."Date" < t2."Date") 
         OR (t1."Date" = t2."Date" AND t1.id < t2.id))
WHERE t2.UserId IS NULL;

Re komentarz od @ Farhan:

Oto bardziej szczegółowe wyjaśnienie:

Zewnętrzne połączenie próbuje połączyć t1 z t2. Domyślnie zwracane są wszystkie wyniki t1, a jeśli w istnieje dopasowanie w t2, jest ono również zwracane. Jeśli w t2 nie ma dopasowania dla danego wiersza t1, to zapytanie nadal zwraca wiersz t1 i używa NULL jako elementu zastępczego dla wszystkich kolumn t2. Tak w ogóle działają zewnętrzne połączenia.

Sztuczka w tym zapytaniu polega na zaprojektowaniu warunku dopasowania join tak, aby t2 pasował do tego samego userid, i większy date. Jeśli rząd istnieje w t2, który ma większą date, to rząd w t1 jest porównywany z , nie może być największym date dla tego userid. Ale jeśli nie ma dopasowania -- tzn. jeśli nie istnieje wiersz {[8] } z większym date niż rząd w t1 -- wiemy, że rząd w t1 był rzędem o największej date dla danego userid.

W tych przypadkach (gdy nie ma dopasowania), kolumny t2 będą NULL -- nawet kolumny określone w warunku join. Dlatego używamy WHERE t2.UserId IS NULL, ponieważ szukamy przypadków, w których nie znaleziono żadnego wiersza z większym date dla danego userid.

SELECT userid, MAX(value) KEEP (DENSE_RANK FIRST ORDER BY date DESC)
  FROM table
  GROUP BY userid

Nie znam dokładnych nazw kolumn, ale byłoby to coś takiego:

    select userid, value
      from users u1
     where date = (select max(date)
                     from users u2
                    where u1.userid = u2.userid)

Nie będąc w pracy, nie mam Oracle pod ręką, ale przypominam sobie, że Oracle pozwala na dopasowanie wielu kolumn w klauzuli IN, co powinno przynajmniej unikać opcji, które używają skorelowanych zapytań podrzędnych, co rzadko jest dobrym pomysłem.

Coś takiego, może (nie pamiętam czy lista kolumn powinna być w nawiasie czy nie):

SELECT * 
FROM MyTable
WHERE (User, Date) IN
  ( SELECT User, MAX(Date) FROM MyTable GROUP BY User)

EDIT: Just tried it for real:

SQL> create table MyTable (usr char(1), dt date);
SQL> insert into mytable values ('A','01-JAN-2009');
SQL> insert into mytable values ('B','01-JAN-2009');
SQL> insert into mytable values ('A', '31-DEC-2008');
SQL> insert into mytable values ('B', '31-DEC-2008');
SQL> select usr, dt from mytable
  2  where (usr, dt) in 
  3  ( select usr, max(dt) from mytable group by usr)
  4  /

U DT
- ---------
A 01-JAN-09
B 01-JAN-09

Więc to działa, chociaż niektóre z nowych-fangly rzeczy wymienione gdzie indziej mogą być bardziej wydajny.

Wiem, że prosiłeś o Oracle, ale w SQL 2005 używamy teraz tego:

-- Single Value
;WITH ByDate
AS (
SELECT UserId, Value, ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY Date DESC) RowNum
FROM UserDates
)
SELECT UserId, Value
FROM ByDate
WHERE RowNum = 1

-- Multiple values where dates match
;WITH ByDate
AS (
SELECT UserId, Value, RANK() OVER (PARTITION BY UserId ORDER BY Date DESC) Rnk
FROM UserDates
)
SELECT UserId, Value
FROM ByDate
WHERE Rnk = 1

Czy klauzula kwalifikacyjna nie byłaby najprostsza i najlepsza?

select userid, my_date, ...
from users
qualify rank() over (partition by userid order by my_date desc) = 1

Dla kontekstu, na Teradata tutaj przyzwoity test tego działa w 17s z tą wersją I w 23s z "inline view" /Aldridge solution #1.

Nie mam Oracle, aby go testować, ale najskuteczniejszym rozwiązaniem jest użycie zapytań analitycznych. Powinno to wyglądać mniej więcej tak:

SELECT DISTINCT
    UserId
  , MaxValue
FROM (
    SELECT UserId
      , FIRST (Value) Over (
          PARTITION BY UserId
          ORDER BY Date DESC
        ) MaxValue
    FROM SomeTable
  )

Podejrzewam, że można pozbyć się zewnętrznego zapytania i umieścić odrębne na wewnętrznej, ale nie jestem pewien. W międzyczasie wiem, że to działa.

Jeśli chcesz dowiedzieć się o zapytaniach analitycznych, proponuję przeczytać http://www.orafaq.com/node/55 Oraz http://www.akadia.com/services/ora_analytic_functions.html. Oto krótkie podsumowanie.

Pod maską zapytania analityczne sortują cały zbiór danych, a następnie przetwarzają je kolejno. Podczas przetwarzania można podzielić zbiór danych według pewnych kryteriów, a następnie dla każdego wiersza patrzy na jakieś okno (domyślnie pierwsza wartość w partycji do bieżącego wiersza - że domyślnie jest również najbardziej wydajne) i można obliczyć wartości za pomocą wielu funkcji analitycznych (których lista jest bardzo podobna do funkcji agregowanych).

W tym przypadku oto, co robi wewnętrzne zapytanie. Cały zbiór danych jest posortowany według UserId, a następnie date DESC. Następnie przetwarza go w jednym przejściu. Dla każdego wiersza zwracamy identyfikator użytkownika i pierwszą datę widoczną dla tego identyfikatora (ponieważ daty są sortowane DESC, to jest maksymalna Data). Daje to odpowiedź z zduplikowanymi wierszami. Następnie powiela się zewnętrzne, odrębne zgnilizny.

W PostgreSQL 8.4 lub nowszym możesz użyć tego:

select user_id, user_value_1, user_value_2
  from (select user_id, user_value_1, user_value_2, row_number()
          over (partition by user_id order by user_date desc) 
        from users) as r
  where r.row_number=1

W Oracle 12c+, możesz użyć Top N zapytań wraz z funkcją analityczną rank, aby osiągnąć to bardzo zwięźle Bez zapytań podrzędnych:

select *
from your_table
order by rank() over (partition by user_id order by my_date desc)
fetch first 1 row with ties;

Powyższe zwraca wszystkie wiersze z max my_date na użytkownika.

Jeśli chcesz tylko jeden wiersz z maksymalną datą, zamień rank na row_number:

select *
from your_table
order by row_number() over (partition by user_id order by my_date desc)
fetch first 1 row with ties; 

Select  
   UserID,  
   Value,  
   Date  
From  
   Table,  
   (  
      Select  
          UserID,  
          Max(Date) as MDate  
      From  
          Table  
      Group by  
          UserID  
    ) as subQuery  
Where  
   Table.UserID = subQuery.UserID and  
   Table.Date = subQuery.mDate  

Po prostu musiałem napisać" żywy " przykład w pracy:)

Ten obsługuje wiele wartości dla UserId w tej samej date.

Kolumny: UserId, Value, Date

SELECT
   DISTINCT UserId,
   MAX(Date) OVER (PARTITION BY UserId ORDER BY Date DESC),
   MAX(Values) OVER (PARTITION BY UserId ORDER BY Date DESC)
FROM
(
   SELECT UserId, Date, SUM(Value) As Values
   FROM <<table_name>>
   GROUP BY UserId, Date
)

Możesz użyć FIRST_VALUE zamiast MAX i sprawdzić to w planie explain. Nie miałem czasu się tym bawić.

Oczywiście, jeśli przeszukujesz ogromne tabele, prawdopodobnie lepiej będzie, jeśli użyjesz pełnych podpowiedzi w zapytaniu.

select VALUE from TABLE1 where TIME = 
   (select max(TIME) from TABLE1 where DATE= 
   (select max(DATE) from TABLE1 where CRITERIA=CRITERIA))

Myślę, że coś takiego. (Wybacz mi Błędy składniowe; jestem przyzwyczajony do używania HQL w tym momencie!)

EDIT: też źle odczytałem pytanie! Poprawiłem zapytanie...

SELECT UserId, Value
FROM Users AS user
WHERE Date = (
    SELECT MAX(Date)
    FROM Users AS maxtest
    WHERE maxtest.UserId = user.UserId
)

I thing you shuold make this variant to previous query:

SELECT UserId, Value FROM Users U1 WHERE 
Date = ( SELECT MAX(Date)    FROM Users where UserId = U1.UserId)

(T-SQL) najpierw pobierz wszystkich użytkowników i ich maxdate. Połącz się z tabelą, aby znaleźć odpowiednie wartości dla użytkowników na maxdates.

create table users (userid int , value int , date datetime)
insert into users values (1, 1, '20010101')
insert into users values (1, 2, '20020101')
insert into users values (2, 1, '20010101')
insert into users values (2, 3, '20030101')

select T1.userid, T1.value, T1.date 
    from users T1,
    (select max(date) as maxdate, userid from users group by userid) T2    
    where T1.userid= T2.userid and T1.date = T2.maxdate

Wyniki:

userid      value       date                                    
----------- ----------- -------------------------- 
2           3           2003-01-01 00:00:00.000
1           2           2002-01-01 00:00:00.000

Tutaj odpowiedź jest tylko Oracle. Oto nieco bardziej wyrafinowana odpowiedź w całym SQL:

Kto ma najlepszy ogólny wynik pracy domowej (maksymalna suma punktów pracy domowej)?

SELECT FIRST, LAST, SUM(POINTS) AS TOTAL
FROM STUDENTS S, RESULTS R
WHERE S.SID = R.SID AND R.CAT = 'H'
GROUP BY S.SID, FIRST, LAST
HAVING SUM(POINTS) >= ALL (SELECT SUM (POINTS)
FROM RESULTS
WHERE CAT = 'H'
GROUP BY SID)

I trudniejszy przykład, który wymaga jakiegoś wyjaśnienia, na który nie mam czasu:

Podaj książkę (ISBN i tytuł), która jest najpopularniejsza w 2008 roku, czyli najczęściej wypożyczana w 2008 roku.

SELECT X.ISBN, X.title, X.loans
FROM (SELECT Book.ISBN, Book.title, count(Loan.dateTimeOut) AS loans
FROM CatalogEntry Book
LEFT JOIN BookOnShelf Copy
ON Book.bookId = Copy.bookId
LEFT JOIN (SELECT * FROM Loan WHERE YEAR(Loan.dateTimeOut) = 2008) Loan 
ON Copy.copyId = Loan.copyId
GROUP BY Book.title) X
HAVING loans >= ALL (SELECT count(Loan.dateTimeOut) AS loans
FROM CatalogEntry Book
LEFT JOIN BookOnShelf Copy
ON Book.bookId = Copy.bookId
LEFT JOIN (SELECT * FROM Loan WHERE YEAR(Loan.dateTimeOut) = 2008) Loan 
ON Copy.copyId = Loan.copyId
GROUP BY Book.title);

Mam nadzieję, że to pomoże (każdemu).. :)

Pozdrawiam, Guus

Zakładając, że Data jest unikalna dla danego UserID, oto kilka TSQL:

SELECT 
    UserTest.UserID, UserTest.Value
FROM UserTest
INNER JOIN
(
    SELECT UserID, MAX(Date) MaxDate
    FROM UserTest
    GROUP BY UserID
) Dates
ON UserTest.UserID = Dates.UserID
AND UserTest.Date = Dates.MaxDate 

Jestem dość późno na imprezę, ale poniższy hack przewyższy zarówno skorelowane zapytania podrzędne, jak i dowolną funkcję analityczną, ale ma jedno ograniczenie: wartości muszą być przekonwertowane na ciągi. Więc to działa dla dat, liczb i innych ciągów. Kod nie wygląda dobrze, ale profil wykonania jest świetny.

select
    userid,
    to_number(substr(max(to_char(date,'yyyymmdd') || to_char(value)), 9)) as value,
    max(date) as date
from 
    users
group by
    userid

Powodem, dla którego ten kod działa tak dobrze, jest to, że wystarczy zeskanować tabelę tylko raz. Nie wymaga żadnych indeksów i co najważniejsze nie musi sortować tabeli, która większość funkcji analitycznych tak. Indeksy pomogą jednak, jeśli chcesz filtrować wynik dla pojedynczego identyfikatora użytkownika.

Użycie ROW_NUMBER() aby przypisać unikalny ranking na malejąco Date dla każdego UserId, przefiltruj do pierwszego wiersza dla każdego UserId (tj., ROW_NUMBER = 1).

SELECT UserId, Value, Date
FROM (SELECT UserId, Value, Date,
        ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY Date DESC) rn
      FROM users) u
WHERE rn = 1;

select userid, value, date
  from thetable t1 ,
       ( select t2.userid, max(t2.date) date2 
           from thetable t2 
          group by t2.userid ) t3
 where t3.userid t1.userid and
       t3.date2 = t1.date

Myślę, że to powinno zadziałać?

Select
T1.UserId,
(Select Top 1 T2.Value From Table T2 Where T2.UserId = T1.UserId Order By Date Desc) As 'Value'
From
Table T1
Group By
T1.UserId
Order By
T1.UserId

Pierwsza próba źle odczytałem pytanie, podążając za najlepszą odpowiedzią, Oto kompletny przykład z poprawnymi wynikami:

CREATE TABLE table_name (id int, the_value varchar(2), the_date datetime);

INSERT INTO table_name (id,the_value,the_date) VALUES(1 ,'a','1/1/2000');
INSERT INTO table_name (id,the_value,the_date) VALUES(1 ,'b','2/2/2002');
INSERT INTO table_name (id,the_value,the_date) VALUES(2 ,'c','1/1/2000');
INSERT INTO table_name (id,the_value,the_date) VALUES(2 ,'d','3/3/2003');
INSERT INTO table_name (id,the_value,the_date) VALUES(2 ,'e','3/3/2003');

--

  select id, the_value
      from table_name u1
      where the_date = (select max(the_date)
                     from table_name u2
                     where u1.id = u2.id)

--

id          the_value
----------- ---------
2           d
2           e
1           b

(3 row(s) affected)

To również zajmie się duplikatami (zwraca jeden wiersz dla każdego user_id):

SELECT *
FROM (
  SELECT u.*, FIRST_VALUE(u.rowid) OVER(PARTITION BY u.user_id ORDER BY u.date DESC) AS last_rowid
  FROM users u
) u2
WHERE u2.rowid = u2.last_rowid

Właśnie to przetestowałem i wygląda na to, że działa na tabeli logowania

select ColumnNames, max(DateColumn) from log  group by ColumnNames order by 1 desc

To powinno być tak proste jak:

SELECT UserId, Value
FROM Users u
WHERE Date = (SELECT MAX(Date) FROM Users WHERE UserID = u.UserID)

Jeśli używasz Postgres, możesz użyć array_agg Jak

SELECT userid,MAX(adate),(array_agg(value ORDER BY adate DESC))[1] as value
FROM YOURTABLE
GROUP BY userid

SELECT 
  userid,
  MAX(adate),
  SUBSTR(
    (LISTAGG(value, ',') WITHIN GROUP (ORDER BY adate DESC)),
    0,
    INSTR((LISTAGG(value, ',') WITHIN GROUP (ORDER BY adate DESC)), ',')-1
  ) as value 
FROM YOURTABLE
GROUP BY userid 

Oba zapytania zwracają te same wyniki Co zaakceptowana odpowiedź. Zobacz SQLFiddles:

1. zaakceptowana odpowiedź
2. moje rozwiązanie z Postgres
3. moje rozwiązanie z Oracle

Jeśli (UserID, Date) jest unikalne, tzn. żadna data nie pojawia się dwa razy dla tego samego użytkownika, to:

select TheTable.UserID, TheTable.Value
from TheTable inner join (select UserID, max([Date]) MaxDate
                          from TheTable
                          group by UserID) UserMaxDate
     on TheTable.UserID = UserMaxDate.UserID
        TheTable.[Date] = UserMaxDate.MaxDate;

select   UserId,max(Date) over (partition by UserId) value from users;

Rozwiązanie dla MySQL, które nie ma pojęcia o zachowywaniu partycji, DENSE_RANK.

select userid,
       my_date,
       ...
from
(
select @sno:= case when @pid<>userid then 0
                    else @sno+1
    end as serialnumber, 
    @pid:=userid,
       my_Date,
       ...
from   users order by userid, my_date
) a
where a.serialnumber=0

Odniesienie: http://benincampus.blogspot.com/2013/08/select-rows-which-have-maxmin-value-in.html