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.

Answers


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

Need Your Help

iPhone Push Notification (non-English message)

iphone objective-c cocoa-touch json notifications

I'm trying to send iPhone Push Notification (Arabic message) using UTF-8 characters.

Samsung Touchwiz lists widget

android listview samsung-mobile

I want the widget which is used in Samsung Touchwiz UX, in file manager, Music player, and some other apps, that two ListViews are in left and right side of the tablet, and there is a separator in ...