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)
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
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 <= 1
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.
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
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.
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;
}
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.
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;
Source: Stackoverflow.com