Little explanation:
I have a table called passes and it's linked with 2 tables (services (cod_serv) and atend (pass)). Passes can be duplicated for different services.
Eg.: If I have 3 services, I can have 3 passes nº 01, BUT not 2 passes nº 1 for the same service (I defined it in my composite primary key).
For tests, I added 102 passes (all with situation = "F" and with the same date (today)). Then I added 34 passes for each service (I have 3 services).
The following query is to show how the schema more or less defined.
SELECT DISTINCT s.pass, s.data, cod_serv, situation, hour, min
FROM passes
JOIN atend a ON s.pass = a.pass;
PASS DATA COD_SERV S HOUR MIN
----- -------- --------- - ------- -------
04 26/03/16 2 F 12 24
04 26/03/16 1 F 13 27
13 26/03/16 1 F 14 26
18 26/03/16 3 F 14 27
18 26/03/16 2 F 14 28
15 26/03/16 1 F 14 29
10 26/03/16 3 F 14 30
... ... ... ... ... ...
Then, I want to get the 100th (ROWNUMBER()) pass (as it's showing below it's 21) from a specific date with the situation = 'F' ordering by hour and min.
Row Number 100:
21 26/03/16 3 F 14 34
The following query is returning nothing and I can't figure out why. By the way, I have more than 100 passes with this situation.
SELECT DISTINCT pass, data, cod_serv, situation FROM
(SELECT DISTINCT a.pass, s.data, cod_serv, situation,
ROW_NUMBER() OVER(PARTITION BY situation, hour, min
ORDER BY situation, hour, min) row
FROM passes s
JOIN atend a ON s.pass = a.pass
WHERE situation = 'F' AND
TRUNC(a.data) = TRUNC('some date'))
WHERE row = 100;
EDIT:
My query at the moment:
SELECT DISTINCT pass, cod_serv FROM
(SELECT DISTINCT s.pass, cod_serv,
ROW_NUMBER() OVER(PARTITION BY TRUNC(s.data)
ORDER BY a.hour, a.min) row
FROM passes s
JOIN atend a ON s.pass = a.pass
WHERE s.situation = 'F' AND
TRUNC(s.data) = TRUNC(SYSDATE))
WHERE row = 100;
Having the same fields in PARTITION BY and ORDER BY in an OVER clause makes little sense.
The PARTITION BY clause should list the fields that define the group in which you start counting records from 1.
The ORDER BY clause defines the order in which records are counted within that group.
As you write:
I want to get the 100th (
ROWNUMBER()) pass from a specific date with the situation = 'F' ordering by hour and min
... you actually say in words what needs to be put in these clauses:
ROW_NUMBER() OVER(PARTITION BY data, situation ORDER BY hour, min)
So your main mistake was to put hour and min in the PARTITION BY clause, making the record count starting from 1 as soon as a minute difference was found, giving most of your records the number 1.
Edit
It seems that Oracle does not retain the same row number when it is not selected. This is probably because the ORDER BY hour, min is not deterministic. Whatever the reason, it can be solved by selecting the row in the outer query:
SELECT pass, row FROM ( ... etc ...) WHERE row = 100
If you only need the pass, you can wrap that query again:
SELECT pass FROM (
SELECT pass, row FROM ( ... etc ...) WHERE row = 100
)