SQL - How to select a row having a column with max value

Question

date                 value  18 5 2010  1 pm        40 18 5 2010  2 pm        20 18 5 2010  3 pm        60 18 5 2010  4 pm        30 18 5 2010  5 pm        60 18 5 2010  6 pm        25    i need to query for the row having max value  i e  60   So  here we get two rows  From that  I need the row with the lowest time stamp for that day i e 18 5 2010  3 pm -  60

User · Answer

In Oracle     This gets the key of the max high val  in the table according to the range   select high val  my key from  select high val  my key       from mytable       where something    avalue        order by high val desc  where rownum  lt   1

User · Answer

Technically  this is the same answer as  Sujee   It also depends on your version of Oracle as to whether it works    I think this syntax was introduced in Oracle 12     SELECT   FROM   table ORDER BY value DESC  date column ASC FETCH  first 1 rows only    As I say  if you look under the bonnet  I think this code is unpacked internally by the Oracle Optimizer to read like  Sujee s   However  I m a sucker for pretty coding  and nesting select statements without a good reason does not qualify as beautiful    -P

User · Answer

The simplest answer would be  --Setup a test table called  t1   create table t1  date datetime  value int    -- Load the data  -- Note  date format different than in the question  insert into t1 Select  5 18 2010 13 00  40 union all Select  5 18 2010 14 00  20 union all Select  5 18 2010 15 00  60  union all Select  5 18 2010 16 00  30  union all Select  5 18 2010 17 00  60  union all Select  5 18 2010 18 00  25    -- find the row with the max qty and min date   select   from t1 where value         select max value   from t1  and date         select min date       from t1     where value    select max value   from t1     I know you can do the  TOP 1  answer  but usually your solution gets just complicated enough that you can t use that for some reason

User · Answer

In Oracle DB   create table temp test1  id number  value number  description varchar2 20     insert into temp test1 values 1  22   qq    insert into temp test1 values 2  22   qq    insert into temp test1 values 3  22   qq    insert into temp test1 values 4  23   qq1    insert into temp test1 values 5  23   qq1    insert into temp test1 values 6  23   qq1     SELECT MAX id   value  description FROM temp test1 GROUP BY value  description   Result      MAX ID  VALUE DESCRIPTION     -------------------------     6         23    qq1     3         22    qq

User · Answer

Analytics  This avoids having to access the table twice   SELECT DISTINCT        FIRST VALUE date col   OVER  ORDER BY value col DESC  date col ASC          FIRST VALUE value col  OVER  ORDER BY value col DESC  date col ASC  FROM   mytable

User · Answer

You can use this function  ORACLE DB   public string getMaximumSequenceOfUser string columnName  string tableName  string username                string result               var query   string Format  Select MAX   0  from  1  where CREATED BY    2    columnName  tableName  username ToLower              OracleConnection conn   new OracleConnection  context Database Connection ConnectionString           OracleCommand cmd   new OracleCommand query  conn           try                       conn Open                OracleDataReader dr   cmd ExecuteReader                dr Read                result   dr 0  ToString                dr Dispose                      finally                       conn Close                      return result

User · Answer

SQL gt  create table t  mydate value    2  as   3  select to date  18 5 2010  1 pm   dd mm yyyy  hh am    40 from dual union all   4  select to date  18 5 2010  2 pm   dd mm yyyy  hh am    20 from dual union all   5  select to date  18 5 2010  3 pm   dd mm yyyy  hh am    60 from dual union all   6  select to date  18 5 2010  4 pm   dd mm yyyy  hh am    30 from dual union all   7  select to date  18 5 2010  5 pm   dd mm yyyy  hh am    60 from dual union all   8  select to date  18 5 2010  6 pm   dd mm yyyy  hh am    25 from dual   9     Table created   SQL gt  select min mydate  keep  dense rank last order by value  mydate   2         max value  value   3    from t   4     MYDATE                   VALUE ------------------- ---------- 18-05-2010 15 00 00         60  1 row selected    Regards  Rob

User · Answer

Keywords like TOP  LIMIT  ROWNUM     etc are database dependent  Please read this article for more information   http   en wikipedia org wiki Select  SQL  Result limits   Oracle  ROWNUM could be used   select   from  select   from table  order by value desc  date column   where rownum   1    Answering the question more specifically   select high val  my key from  select high val  my key       from mytable       where something    avalue        order by high val desc  where rownum  lt   1

User · Answer

Answer is to add a having clause   SELECT  columns  FROM table t1 WHERE value   select max value  from table  AND date    select MIN date  from table t2 where t1 value   t2 value    this should work and gets rid of the neccesity of having an extra sub select in the date clause

[sql] SQL - How to select a row having a column with max value

Examples related to sql

Examples related to oracle