Fetch the row which has the Max value for a column

Question

Table    UserId  Value  Date    I want to get the UserId  Value for the max Date  for each UserId  That is  the Value for each UserId that has the latest date  Is there a way to do this simply in SQL   Preferably Oracle   Update  Apologies for any ambiguity  I need to get ALL the UserIds  But for each UserId  only that row where that user has the latest date

User · Answer

Use the code   select T UserId T dt from  select UserId max dt   over  partition by UserId  as dt from t users T where T dt dt    This will retrieve the results  irrespective of duplicate values for UserId   If your UserId is unique  well it becomes more simple   select UserId max dt  from t users group by UserId

User · Answer

With PostgreSQL 8 4 or later  you can use this   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

User · Answer

This will retrieve all rows for which the my date column value is equal to the maximum value of my date for that userid  This may retrieve multiple rows for the userid where the maximum date is on multiple rows   select userid         my date             from   select userid         my date                    max my date  over  partition by userid  max my date from   users   where my date   max my date    Analytic functions rock   Edit  With regard to the first comment       using analytic queries and a self-join defeats the purpose of analytic queries   There is no self-join in this code  There is instead a predicate placed on the result of the inline view that contains the analytic function -- a very different matter  and completely standard practice    The default window in Oracle is from the first row in the partition to the current one   The windowing clause is only applicable in the presence of the order by clause  With no order by clause  no windowing clause is applied by default and none can be explicitly specified   The code works

User · Answer

Below query can work    SELECT user id  value  date   row number   OVER  PARTITION BY user id ORDER BY date desc  AS rn FROM table name WHERE rn  1

User · Answer

If you re using Postgres  you can use array agg like  SELECT userid MAX adate   array agg value ORDER BY adate DESC   1  as value FROM YOURTABLE GROUP BY userid   I m not familiar with Oracle  This is what I came up with  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    Both queries return the same results as the accepted answer  See SQLFiddles    Accepted answer My solution with Postgres My solution with Oracle

User · Answer

select   UserId max Date  over  partition by UserId  value from users

User · Answer

Use ROW NUMBER   to assign a unique ranking on descending Date for each UserId  then filter to the first row for each UserId  i e   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

User · Answer

This should be as simple as   SELECT UserId  Value FROM Users u WHERE Date    SELECT MAX Date  FROM Users WHERE UserID   u UserID

User · Answer

If  UserID  Date  is unique  i e  no date appears twice for the same user then   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

User · Answer

SELECT userid  MAX value  KEEP  DENSE RANK FIRST ORDER BY date DESC    FROM table   GROUP BY userid

User · Answer

I don t know your exact columns names  but it would be something like this        select userid  value       from users u1      where date    select max date                       from users u2                     where u1 userid   u2 userid

User · Answer

I see many people use subqueries or else window functions to do this  but I often do this kind of query without subqueries in the following way   It uses plain  standard SQL so it should work in any brand of RDBMS  SELECT t1   FROM mytable t1   LEFT OUTER JOIN mytable t2     ON  t1 UserId   t2 UserId AND t1  quot Date quot   lt  t2  quot Date quot   WHERE t2 UserId IS NULL   In other words  fetch the row from t1 where no other row exists with the same UserId and a greater Date   I put the identifier  quot Date quot  in delimiters because it s an SQL reserved word   In case if t1  quot Date quot    t2  quot Date quot   doubling appears  Usually tables has auto inc seq  key  e g  id  To avoid doubling can be used follows  SELECT t1   FROM mytable t1   LEFT OUTER JOIN mytable t2     ON t1 UserId   t2 UserId AND   t1  quot Date quot   lt  t2  quot Date quot             OR  t1  quot Date quot    t2  quot Date quot  AND t1 id  lt  t2 id   WHERE t2 UserId IS NULL    Re comment from  Farhan  Here s a more detailed explanation  An outer join attempts to join t1 with t2   By default  all results of t1 are returned  and if there is a match in t2  it is also returned   If there is no match in t2 for a given row of t1  then the query still returns the row of t1  and uses NULL as a placeholder for all of t2 s columns   That s just how outer joins work in general  The trick in this query is to design the join s matching condition such that t2 must match the same userid  and a greater date   The idea being if a row exists in t2 that has a greater date  then the row in t1 it s compared against can t be the greatest date for that userid   But if there is no match -- i e  if no row exists in t2 with a greater date than the row in t1 -- we know that the row in t1 was the row with the greatest date for the given userid  In those cases  when there s no match   the columns of t2 will be NULL -- even the columns specified in the join condition   So that s why we use WHERE t2 UserId IS NULL  because we re searching for the cases where no row was found with a greater date for the given userid

User · Answer

Wouldn t a QUALIFY clause be both simplest and best   select userid  my date      from users qualify rank   over  partition by userid order by my date desc    1   For context  on Teradata here a decent size test of this runs in 17s with this QUALIFY version and in 23s with the  inline view  Aldridge solution  1

User · Answer

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

User · Answer

The answer here is Oracle only  Here s a bit more sophisticated answer in all SQL   Who has the best overall homework result  maximum sum of homework points    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   gt   ALL  SELECT SUM  POINTS  FROM RESULTS WHERE CAT    H  GROUP BY SID    And a more difficult example  which need some explanation  for which I don t have time atm   Give the book  ISBN and title  that is most popular in 2008  i e   which is borrowed most often in 2008   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  gt   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     Hope this helps  anyone        Regards  Guus

User · Answer

I think this should work   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

User · Answer

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

User · Answer

Not being at work  I don t have Oracle to hand  but I seem to recall that Oracle allows multiple columns to be matched in an IN clause  which should at least avoid the options that use a correlated subquery  which is seldom a good idea   Something like this  perhaps  can t remember if the column list should be parenthesised or not    SELECT    FROM MyTable WHERE  User  Date  IN     SELECT User  MAX Date  FROM MyTable GROUP BY User    EDIT  Just tried it for real   SQL gt  create table MyTable  usr char 1   dt date   SQL gt  insert into mytable values   A   01-JAN-2009    SQL gt  insert into mytable values   B   01-JAN-2009    SQL gt  insert into mytable values   A    31-DEC-2008    SQL gt  insert into mytable values   B    31-DEC-2008    SQL gt  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   So it works  although some of the new-fangly stuff mentioned elsewhere may be more performant

User · Answer

Just had to write a  live  example at work     This one supports multiple values for UserId on the same date   Columns  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  lt  lt table name gt  gt     GROUP BY UserId  Date     You can use FIRST VALUE instead of MAX and look it up in the explain plan  I didn t have the time to play with it   Of course  if searching through huge tables  it s probably better if you use FULL hints in your query

User · Answer

I don t have Oracle to test it  but the most efficient solution is to use analytic queries   It should look something like this   SELECT DISTINCT     UserId     MaxValue FROM       SELECT UserId         FIRST  Value  Over             PARTITION BY UserId           ORDER BY Date DESC           MaxValue     FROM SomeTable       I suspect that you can get rid of the outer query and put distinct on the inner  but I m not sure   In the meantime I know this one works   If you want to learn about analytic queries  I d suggest reading http   www orafaq com node 55 and http   www akadia com services ora analytic functions html   Here is the short summary   Under the hood analytic queries sort the whole dataset  then process it sequentially   As you process it you partition the dataset according to certain criteria  and then for each row looks at some window  defaults to the first value in the partition to the current row - that default is also the most efficient  and can compute values using a number of analytic functions  the list of which is very similar to the aggregate functions    In this case here is what the inner query does   The whole dataset is sorted by UserId then Date DESC   Then it processes it in one pass   For each row you return the UserId and the first Date seen for that UserId  since dates are sorted DESC  that s the max date    This gives you your answer with duplicated rows   Then the outer DISTINCT squashes duplicates   This is not a particularly spectacular example of analytic queries   For a much bigger win consider taking a table of financial receipts and calculating for each user and receipt  a running total of what they paid   Analytic queries solve that efficiently   Other solutions are less efficient   Which is why they are part of the 2003 SQL standard    Unfortunately Postgres doesn t have them yet   Grrr

User · Answer

First try I misread the question  following the top answer  here is a complete example with correct results   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

User · Answer

This will also take care of duplicates  return one row for each 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

User · Answer

I know you asked for Oracle  but in SQL 2005 we now use this    -- 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

User · Answer

Just tested this and it seems to work on a logging table  select ColumnNames  max DateColumn  from log  group by ColumnNames order by 1 desc

User · Answer

Assuming Date is unique for a given UserID  here s some 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

User · Answer

select VALUE from TABLE1 where TIME        select max TIME  from TABLE1 where DATE       select max DATE  from TABLE1 where CRITERIA CRITERIA

User · Answer

SELECT a userid a values1 b mm  FROM table name a  SELECT userid Max date1 AS mm FROM table name GROUP BY userid  b WHERE a userid b userid AND a DATE1 b mm

User · Answer

I think something like this   Forgive me for any syntax mistakes  I m used to using HQL at this point    EDIT  Also misread the question  Corrected the query     SELECT UserId  Value FROM Users AS user WHERE Date         SELECT MAX Date      FROM Users AS maxtest     WHERE maxtest UserId   user UserId

User · Answer

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   IMHO this works  HTH

User · Answer

Solution for MySQL which doesn t have concepts of partition KEEP  DENSE RANK    select userid         my date             from   select  sno   case when  pid lt  gt 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   Reference   http   benincampus blogspot com 2013 08 select-rows-which-have-maxmin-value-in html

User · Answer

In Oracle 12c   you can use Top n queries along with analytic function rank to achieve this very concisely without subqueries   select   from your table order by rank   over  partition by user id order by my date desc  fetch first 1 row with ties    The above returns all the rows with max my date per user    If you want only one row with max date  then replace the rank with 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

User · Answer

SELECT a    FROM user a INNER JOIN  SELECT userid Max date  AS date12 FROM user1 GROUP BY userid  b   ON a date b date12 AND a userid b userid ORDER BY a userid

User · Answer

check this link if your questions seems similar to that page then i would suggest you the following query which will give the solution for that link  select distinct  sno item name max start date  over partition by sno  max end date  over partition by sno  max creation date   over partition by sno   max last modified date   over partition by sno   from uniq select records order by sno item name asc   will given accurate results related to that link

User · Answer

T-SQL  First get all the users and their maxdate  Join with the table to find the corresponding values for the users on the 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   results   userid      value       date                                     ----------- ----------- --------------------------  2           3           2003-01-01 00 00 00 000 1           2           2002-01-01 00 00 00 000

User · Answer

I m quite late to the party but the following hack will outperform both correlated subqueries and any analytics function but has one restriction  values must convert to strings  So it works for dates  numbers and other strings  The code does not look good but the execution profile is great    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   The reason why this code works so well is that it only needs to scan the table once  It does not require any indexes and most importantly it does not need to sort the table  which most analytics functions do  Indexes will help though if you need to filter the result for a single userid

[sql] Fetch the row which has the Max value for a column

Examples related to sql

Examples related to oracle

Examples related to greatest-n-per-group