Get Top Results For Each Group (In Oracle)

Get top results for each group (in Oracle)

This produces what you want, and it uses no vendor-specific SQL features like TOP N or RANK().

SELECT MAX(e.name) AS name, MAX(e.occupation) AS occupation 
FROM emp e 
  LEFT OUTER JOIN emp e2 
    ON (e.occupation = e2.occupation AND e.emp_id <= e2.emp_id) 
GROUP BY e.emp_id 
HAVING COUNT(*) <= 3 
ORDER BY occupation;

In this example it gives the three employees with the lowest emp_id values per occupation. You can change the attribute used in the inequality comparison, to make it give the top employees by name, or whatever.

Oracle SQL get row with MAX value for each group in a set of grouped results

You can use CTEs

WITH CTE0 AS 
(
  SELECT 
       REV_USAGE_DATA.DDATE, 
       REV_USAGE_DATA.SEGMENT, 
       COUNT(*) AS Freq
  FROM CADA_PERMSISDN_DASH REV_USAGE_DATA
  GROUP BY 
       REV_USAGE_DATA.DDATE,
       REV_USAGE_DATA.SEGMENT
)
SELECT 
  DDATE,
  SEGMENT, 
  FREQ
FROM CTE0 
WHERE (DDATE, SEGMENT, FREQ) IN (
  SELECT DDATE, MAX(SEGMENT), MAX(FREQ)
  FROM CTE0 
  GROUP BY DDATE
)

Select top 15 records from each group

You can use the DENSE_RANK() window function to assign group numbers to rows, and ROW_NUMBER() to assign a serial number within each group. Then, filtering is easy.

For example:

select *
from (
  select
    account_id, 
    transaction_id,
    dense_rank() over(order by account_id) as g,
    row_number() over(partition by account_id order by transaction_id) as rn
  from transactions
  where status = 'P'
) x
where g <= 10 -- the first 10 groups (accounts)
  and rn <= 15 -- the first 15 transactions within each group

Only return the highest value in each group oracle

You can use aggreagation and window functions:

select name, date_occured, no_records
from (
    select 
        name, 
        date_occured, 
        count(*) no_records, 
        rank() over(partition by name order by count(*) desc) rn
    from mytable
    group by name, date_occured
) t
where rn = 1

The subquery aggreagates by name and date_occured, counts the records per group, and ranks groups of records having the same name by descending count. Then the outer query filters on the top record per name. Since we use rank(), possible top ties will be included in the resultset (if you don't want that, use row_number() instead).

Oracle SQL: Get top 3 results per category

One option uses ROW_NUMBER:

SELECT teamid, teamname, Sport
FROM
(
    SELECT t.*, ROW_NUMBER() OVER (PARTITION BY Sport ORDER BY teamid) rn
    FROM Teams t
) s
WHERE rn <= 3
ORDER BY Sport, teamid;

Return the first n records per group Oracle SQL

Consider this non-Windows function approach using a count correlated aggregate query. The idea is to run a department rank subquery and then use that in a derived table that filters outer query by this department rank. Please note your desired results do not return by ordered START_DATE but simply query's row number.

SELECT main.EMPLOYEE, t.START_DATE, t.DEPARTMENT
FROM 
   (SELECT t.EMPLOYEE, t.START_DATE, t.DEPARTMENT, 
          (SELECT Count(*) FROM Employees sub 
           WHERE sub.START_DATE <= t.START_DATE 
           AND sub.Department = t.Department) AS DeptRank
FROM Employees t) main
WHERE main.DeptRank <= 3
ORDER BY main.DEPARTMENT, main.START_DATE;

-- EMPLOYEE START_DATE  DEPARTMENT
-- Tawnee   1/2/1904    Legal
-- Jacinta  1/2/1924    Legal
-- Kirsten  1/2/1933    Legal
-- Edwina   1/2/1902    Mergers
-- Louise   1/2/1912    Mergers
-- Kelly    1/2/1954    Mergers
-- Jane     1/2/1900    Sales
-- Amy      1/2/1901    Sales
-- Cara     1/2/1955    Sales

For the Windows function counterpart:

SELECT main.EMPLOYEE, t.START_DATE, t.DEPARTMENT
FROM 
   (SELECT t.EMPLOYEE, t.START_DATE, t.DEPARTMENT, 
           RANK() OVER (PARTITION BY Department
           ORDER BY START_DATE) AS DeptRank
FROM Employees t) main
WHERE main.DeptRank <= 3
ORDER BY main.DEPARTMENT, main.START_DATE;

---

And as @Matt comments, you may want to handle ties (i.e., employees who started on same day). Both above solutions will output all such employees depending on rank filter. To take one of the ties in correlated subquery, use Employee name as tiebreaker (or better yet a unique ID if available):

SELECT main.EMPLOYEE, t.START_DATE, t.DEPARTMENT
FROM 
   (SELECT t.EMPLOYEE, t.START_DATE, t.DEPARTMENT, 
           (SELECT Count(*) FROM Employees sub 
            WHERE sub.Department =  t.Department 
             AND (sub.START_DATE <= t.START_DATE
                  OR sub.START_DATE = t.START_DATE 
                  AND sub.EMPLOYEE < t.EMPLOYEE) AS DeptRank
FROM Employees t) main
WHERE main.DeptRank <= 3
ORDER BY main.DEPARTMENT, main.START_DATE;

And for window-function query use ROW_NUMBER() in place of RANK().

---

Related Topics