getting two or more values from max min aggregate function

Question

Lets say i have the table sales

+------------+---------+------------------------------+-------------+
| SaleAmount | OrderID | CompanyName                  | ShippedDate |
+------------+---------+------------------------------+-------------+
|       3302 |   10393 | Save-a-lot Markets           | 1997-01-03  |
|       2736 |   10398 | Save-a-lot Markets           | 1997-01-09  |
|       3063 |   10400 | Eastern Connection           | 1997-01-16  |

if i query with aggregate function

    select max(saleamount),shippeddate from sales where shippeddate = '1997-01-16'
            group by shippeddate

It will display the max of sale amount for the particular date. here i want to show the top n max values for the particular date. this i can achieve by doing the same using subquery or storing in table like below

select * from 
 (select * from sales h order by shippeddate, saleamout) where shippeddate = '1997-01-16' and rownum <= 2

Is there any other way to display the top n (max,min) values or any way to write the multirow functions.

EDIT:

here i don't want to use the subquery where the buffer size is too low and it takes larger time for execution time. My database is oracle 11g

Have you checked this? http://stackoverflow.com/questions/5636507/selecting-top-n-rows-without-rownum — Multisync, Dec 01 '14 at 20:21
@Multisync Thanks for posting link. Each query runs slower as it. even i agree with the answer posted by you. That seems to run faster than any query.Hence i agree with you — Exhausted, Dec 02 '14 at 03:49
Did you see my answer ? No subquery are used. Can you test it and let me know if it's faster ? Thanks ! — Fabien TheSolution, Dec 02 '14 at 04:46

score 2 · Accepted Answer · answered Nov 28 '14 at 11:26

2

You can use analitic functions:

select * from
(
    select s.*,
           row_number() over(partition by shippeddate order by saleamout desc) max_rw, 
           row_number() over(partition by shippeddate order by saleamout asc) min_rw
    from sales s
) 
-- where max_rw <= N -- max N
-- where min_rw <= N -- min N

partition by defines a group (in this case all the rows with the same shippeddate
order by sorts the rows inside the group
row_number() assigns row number for each row in the group according to the ORDER BY

answered Nov 28 '14 at 11:26

Multisync

8,657
1
16
20

here i don't want to use subquery, is any other way? – Exhausted Nov 28 '14 at 11:29
@Sudharsanan Oracle 12c has an OFFSET operator. In lower versions you need a subquery. What's wrong with subqueries? – Multisync Nov 28 '14 at 11:32
the subquery makes more time to execute, but your solution is quite attractive – Exhausted Nov 28 '14 at 11:36
1

@Sudharsanan More time than ... what? ) – Multisync Nov 28 '14 at 11:39
here the records are higher and buffer size is also too low hence it takes more time for subqueries – Exhausted Nov 28 '14 at 11:41

score 0 · Answer 2 · answered Dec 01 '14 at 19:55

Maybe one of the examples below will help?

-- one row two columns
SELECT MAX (saleamount), MIN (saleamount) FROM sales WHERE shippeddate = '1997-01-16';

-- two rows one column
SELECT MAX (saleamount) FROM sales WHERE shippeddate = '1997-01-16'
UNION SELECT MIN (saleamount) FROM sales WHERE shippeddate = '1997-01-16';

Fabien TheSolution · Answer 3 · 2014-12-02T04:50:25.243

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE sales
    ("SaleAmount" int, 
     "OrderID" int primary key, 
     "CompanyName" varchar2(18), 
     "ShippedDate" varchar2(10))
;


CREATE INDEX sales_amount ON sales ("SaleAmount");
CREATE INDEX sales_date ON sales ("ShippedDate");

INSERT ALL 
    INTO sales ("SaleAmount", "OrderID", "CompanyName", "ShippedDate")
         VALUES (3302, 10393, 'Save-a-lot Markets', '1997-01-03')
    INTO sales ("SaleAmount", "OrderID", "CompanyName", "ShippedDate")
         VALUES (2736, 10398, 'Save-a-lot Markets', '1997-01-09')
    INTO sales ("SaleAmount", "OrderID", "CompanyName", "ShippedDate")
         VALUES (7063, 10401, 'Save-a-lot Markets', '1997-01-16')
    INTO sales ("SaleAmount", "OrderID", "CompanyName", "ShippedDate")
         VALUES (3063, 10400, 'Eastern Connection', '1997-01-16')
    INTO sales ("SaleAmount", "OrderID", "CompanyName", "ShippedDate")
         VALUES (4063, 10402, 'Save-a-lot Markets', '1997-01-16')
    INTO sales ("SaleAmount", "OrderID", "CompanyName", "ShippedDate")
         VALUES (5063, 10404, 'Eastern Connection', '1997-01-16')
    INTO sales ("SaleAmount", "OrderID", "CompanyName", "ShippedDate")
         VALUES (7763, 10406, 'Save-a-lot Markets', '1997-01-16')
    INTO sales ("SaleAmount", "OrderID", "CompanyName", "ShippedDate")
         VALUES (4763, 10408, 'Save-a-lot Markets', '1997-01-16')
SELECT * FROM dual
;

Query 1:

SELECT /*+ INDEX(sales sales_amount) */ "SaleAmount"
FROM sales 
WHERE "ShippedDate" = '1997-01-16' AND "SaleAmount" <> 0 AND rownum <= 2
UNION ALL
(
SELECT /*+ INDEX(sales sales_amount) */ "SaleAmount"
FROM sales 
WHERE "ShippedDate" = '1997-01-16' AND "SaleAmount" <> 0 AND 
    rownum <= (SELECT COUNT(*) FROM sales WHERE "ShippedDate" = '1997-01-16')
MINUS
SELECT /*+ INDEX(sales sales_amount) */ "SaleAmount"
FROM sales 
WHERE "ShippedDate" = '1997-01-16' AND "SaleAmount" <> 0 AND 
    rownum <= (SELECT COUNT(*)-2 FROM sales WHERE "ShippedDate" = '1997-01-16')
)

Results:

| SALEAMOUNT |
|------------|
|       3063 |
|       4063 |
|       7063 |
|       7763 |



-- AND rownum <= N -- min N
-- SELECT COUNT(*)-N -- max N

This is not as fast as multisync result – Exhausted Dec 02 '14 at 05:20 — Exhausted, Dec 02 '14 at 05:20

getting two or more values from max min aggregate function

3 Answers3