Oracle SQL - How to Retrieve Highest 5 Values of a Column

How to query data in Oracle SQL that gives the max value of a column and the corresponding columns for that row?

You can use the rank() window function to assign a rank to each row per project. To define the order use the number of workers in descending order. Staring from 1 the rank is the lower the more workers there are in that row. So all the rows where this rank is 1 are the ones you want.

SELECT project,
       date,
       vendor,
       workers
       FROM (SELECT project,
                    date,
                    vendor,
                    workers,
                    rank() OVER (PARTITION BY project
                                 ORDER BY workers DESC) r
                    FROM elbat) x
       WHERE r = 1;

Note: This includes ties. If you don't want ties you can use row_number() instead of rank but then you'd need a second criteria for the order unless it is OK for you that one random row is picked from the tied ones.

Find the top 5 MAX() values from an SQL table and then performi an AVG() on that table without them

You can use row_number to find the top 5 values, and filter them out in a where clause:

select  avg(col1)
from    (
        select  row_number() over (order by col1 desc) as rn
        ,       *
        from    YourTable
        ) as SubQueryAlias
where   rn > 5

Oracle - select all rows with highest x of specific column?

you can use window function:

select * from (
  select * , dense_rank() over (order by score desc) rn
  from table
) t where rn <=5

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

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

Selecting only the highest value in Oracle SQL

Rank them, and then fetch row(s) that rank as the highest.

Sample data:

SQL> with test (acctnbr, controlnbr, linenbr, trandatseq) as
  2    (select 12345, 3771, 1, 5 from dual union all
  3     select 12345, 3771, 1, 11 from dual union all
  4     select 12345, 3771, 1, 12 from dual union all
  5     select 12345, 3771, 1, 18 from dual union all
  6     --
  7     select 12345, 3772, 3, 5  from dual union all
  8     --
  9     select 12345, 3773, 1, 5  from dual union all
 10     select 12345, 3773, 1, 12 from dual union all
 11     --
 12     select 24568, 3786, 2, 4  from dual union all
 13     select 24568, 3786, 2, 12 from dual
 14    ),

Query begins here:

 15  temp as
 16    -- sort them
 17    (select t.*,
 18        rank() over (partition by acctnbr, controlnbr , linenbr order by trandatseq desc) rnk
 19     from test t
 20    )
 21  -- finally, fetch rows that rank as "highest"
 22  select acctnbr, controlnbr, linenbr, trandatseq
 23  from temp
 24  where rnk = 1;

   ACCTNBR CONTROLNBR    LINENBR TRANDATSEQ
---------- ---------- ---------- ----------
     12345       3771          1         18
     12345       3772          3          5
     12345       3773          1         12
     24568       3786          2         12

SQL>

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

I believe this is what you're looking for:

select *
from table
where value = (select max(value) from table);

Fetch the rows which have the Max value for a column for each distinct value of another column

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.

Get the second highest max for each value on Oracle

You need conditional aggregation after appyling analytic function such as ROW_NUMBER() :

WITH t2 AS
(
  SELECT client,order_date,
         ROW_NUMBER() OVER (PARTITION BY client ORDER BY order_date DESC) as rk
   FROM order_table
)
SELECT client, 
       MAX(CASE WHEN rk=1 THEN order_date END) AS "max order date",
       MAX(CASE WHEN rk=2 THEN order_date END) AS "2nd highest max ord.date"
  FROM t2
 GROUP BY client

Demo

---

Related Topics