52 
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)

Improve this question
1
  • Thanks all. Now if the date spans for 10 days, I need to make this query for each of the 10 days resulting in 10 rows, with each having a max value of that particular day. Please assist me on this. Commented May 19, 2010 at 9:45

9 Answers 9

Reset to default
68

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 <= 1
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

+1 For this question I'd recommend the ROWNUM query. Oracle optimizes these sorts of queries very well (i.e. even though it requires a sort, it doesn't actually sort the entire table - it just keeps the topmost row as it scans the table) - and with an appropriate index it won't even have to do that.
Excellent answer. Anyway, I prefer LIMIT :)
36

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;
Improve this answer

4 Comments

I would be interested in seeing what kind of performance difference this had, from a purely educational point of view. From Toms asktom.oracle site i had understood there were significant overheads in using FIRST_VALUE. Is it possible that you could direct me to some performance results comparing them?
If you have an index on (value_col, date_col) you'll find that Oracle will do quite well using Sujee's query, since it will use the COUNT STOPKEY optimization.
It avoids scanning the table twice, but it calculates the first_value column for every row, and then the distinct discards them all but one. Better than most other answers, but aggregating is the way to go here.
Yeah. Sujee beat me to the ROWNUM solution which is better :)
13

Answer is to add a sub select:

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.

Improve this answer

6 Comments

Any non-aggregate in the HAVING clause needs to be part of a GROUP BY clause
Excelent point. I tested mine on sybase, it lets you get away with that. I modified my answer to suit the required database
um... you still have a sub select - in fact, two of them.
Thank you for your feedback, yes there are two, but the standard alternative is to do AND date = select min(date) from table where value = (select max(value) from table)). i refference J brooks answer (up 3 from this one) as proof. by refferencing the already aquired value of the previous sub select you save 1 level of nesting
ah, I get what you're saying now. ... of course, all the subselects are unnecessary :)
|
10 
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.

Improve this answer

2 Comments

Good, but still Oracle will do a full scan - it cannot apply the COUNT STOPKEY optimization, unfortunately :(
True. And logical because to know for sure something is the maximal value, you'll have to visit them all, or have them pre-ordered like in an index.
9

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

Improve this answer

Comments

2

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
Improve this answer

Comments

1

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
Improve this answer

Comments

0

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.

Improve this answer

2 Comments

Three extra accesses on the same table are just unnecessary in Oracle for this requirement.
Extra access? This will have the same execution plan as some of the others on here that look like they do less.... but are less readable. And I understand that Oracle doesn't have a TOP command.
-5

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; }
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.