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)

Answer 1

wrap your original query in another SQL statement

SELECT * FROM (SELECT ....) as t ORDER BY date LIMIT 1

Answer 2

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.

Answer 3

select top 1 * from table order by value desc , date desc

Answer 4

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;

Answer 5

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.

Answer 6

SELECT TOP (1) * FROM Table ORDER BY CostPrice DESC, Date DESC

Answer 7

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;

Answer 8

SQL> 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> 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.