Select count() max() Date
I have a table with shifts history along with emp ids.
I'm using this query to retrieve a list of employe开发者_如何学Goes and their total shifts by specifying the range to count from:
SELECT ope_id, count(ope_id)
FROM operator_shift
WHERE ope_shift_date >=to_date( '01-MAR-10','dd-mon-yy') and ope_shift_date
<= to_date('31-MAR-10','dd-mon-yy')
GROUP BY OPE_ID
which gives
OPE_ID COUNT(OPE_ID)
1 14
2 7
3 6
4 6
5 2
6 5
7 2
8 1
9 2
10 4
10 rows selected.
How do I choose the employee with the highest number of shifts under the specified range date?
Assuming your version of Oracle is new enough to support common table expressions:
With ShiftCounts As
(
SELECT ope_id, count(ope_id) ShiftCount
, ROW_NUMBER() OVER( ORDER BY Count(ope_id) Desc ) ShiftRank
FROM operator_shift
WHERE ope_shift_date >=to_date( '01-MAR-10','dd-mon-yy')
and ope_shift_date <= to_date('31-MAR-10','dd-mon-yy')
GROUP BY OPE_ID
)
Select ope_id, ShiftCount
From ShiftCounts
Where ShiftRank = 1
something like this maybe:
SELECT TOP 1 ope_id, Count(ope_id)
FROM operator_shift
WHERE ope_shift_date >=to_date( '01-MAR-10','dd-mon-yy') and ope_shift_date
<= to_date('31-MAR-10','dd-mon-yy')
GROUP BY OPE_ID
ORDER BY Count(ope_id) DESC
Use:
SELECT t.ope_id,
t.num
FROM (SELECT os.ope_id,
COUNT(os.ope_id) AS num
FROM OPERATOR_SHIFT os
WHERE os.ope_shift_date BETWEEN TO_DATE('01-MAR-10','dd-mon-yy')
AND TO_DATE('31-MAR-10','dd-mon-yy')
GROUP BY os.ope_id
ORDER BY num DESC) t
WHERE ROWNUM = 1
Reference:
- Ask Tom: On ROWNUM and Limiting Results
精彩评论