Question :
Hi Guys so here is my table, I need a slip number for the slipno collom that is based on the number of times the empno has a slipno in a month.
To try and better explain here is my table
----------------------------------
SLIPNO | EMPNO | DATE |
----------------------------------
123 | 0151450 | 07/OCT/17 |
234 | 0151450 | 14/OCT/17 |
345 | 0151460 | 10/OCT/17 |
456 | 0151466 | 05/OCT/17 |
567 | 0151466 | 01/OCT/17 |
----------------------------------
and need a result like this
-----------------------------------------------
SLIPNO | EMPNO | DATE | SLIPNUMBER |
-----------------------------------------------
123 | 0151450 | 07/OCT/17 | 1 |
234 | 0151450 | 14/OCT/17 | 2 |
345 | 0151460 | 10/OCT/17 | 1 |
456 | 0151466 | 05/OCT/17 | 1 |
567 | 0151466 | 01/OCT/17 | 2 |
-----------------------------------------------
Answer :
Use the Windowing Function ROW_NUMBER()
.
SELECT SLIPNO, EMPNO, DT,
ROW_NUMBER() OVER (PARTITION BY EMPNO ORDER BY DT ASC) AS SLIPNUMBER
FROM SLIPS;
DB Fiddle Test to show an example.
Also note that you should not use DATE
as a column name, as it is a reserved word.