Unique rows in oracle 11g
I have a query which returns a set of records as like the one below:-
Date Dept commission 5-Apr Sales 20 4-Apr Sales 21 1-Jan Marketing 35
case 1: If i run a query between 1 Jan and 5 april I should get
Date Dept commission 5 April Sales 76
case 2: and when I run the query between jan 1 and jan 31 should get the output as
Date Dept commission 1 Jan Marketing 35
Case 2 is simple as when i put hte date range getting the required results , but not sure how to handle case 1 to show the max / latest date , the Dept for that date and a sum of the commission for that Dept , date for the selected date range . The output will be a single row with the latest date and department with a sum(commission) for the selected date range.
SELECT MAX(Date) AS Date , ( SELECT tt.Dept FROM tableX tt WHERE tt.Date = MAX(t.Date) ) AS Dept , SUM(Commission) AS Commission FROM tableX t WHERE Date BETWEEN StartDate AND EndDate
The above works in SQL-Server, MySQL, Postgres as the sql-fiddle, test-1 shows, however it does NOT work in Oracle 11g R2 !
This works though (sql-fiddle, test-2):
SELECT MAX(t.Date) AS Date , MIN(tt.Dept) AS Dept --- MIN, MAX irrelevant , SUM(t.Commission) AS Commission FROM ( SELECT MAX(Date) AS Date , SUM(Commission) AS Commission FROM tableX WHERE Date BETWEEN StartDate AND EndDate ) t JOIN tableX tt ON tt.Date = t.Date
The MIN(tt.Dept) is used to take care of the case you have more than row with the maximum date, say one row with Sales and one with Marketing, both in Apr-5
This works, too, using the LAST_VALUE analytic function (sql-fiddle, test-3):
SELECT MAX(Date) AS Date , MIN(Dept) AS Dept , SUM(Commission) AS Commission FROM ( SELECT Date AS Date , LAST_VALUE(Dept) OVER( ORDER BY Date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS Dept , Commission AS Commission FROM tableX WHERE Date BETWEEN StartDate AND EndDate ) t