little explanation:
i have table called passes , it's linked 2 tables (services (cod_serv) , atend (pass)). passes can duplicated different services.
eg.: if have 3 services, can have 3 passes nº 01, but not 2 passes nº 1 same service (i defined in composite primary key).
for tests, added 102 passes (all situation = "f" , same date (today)). added 34 passes each service (i have 3 services).
the following query show how schema more or less defined.
select distinct s.pass, s.data, cod_serv, situation, hour, min passes join atend 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, want 100th (rownumber()) pass (as it's showing below it's 21) specific date situation = 'f' ordering hour , min.
row number 100:
21 26/03/16 3 f 14 34 the following query returning nothing , can't figure out why. way, have more 100 passes situation.
select distinct pass, data, cod_serv, situation (select distinct a.pass, s.data, cod_serv, situation, row_number() over(partition situation, hour, min order situation, hour, min) row passes s join atend on s.pass = a.pass situation = 'f' , trunc(a.data) = trunc('some date')) row = 100; edit:
my query @ moment:
select distinct pass, cod_serv (select distinct s.pass, cod_serv, row_number() over(partition trunc(s.data) order a.hour, a.min) row passes s join atend on s.pass = a.pass s.situation = 'f' , trunc(s.data) = trunc(sysdate)) row = 100;
having same fields in partition by , order by in over clause makes little sense.
the partition by clause should list fields define group in start counting records 1.
the order by clause defines order in records counted within group.
as write:
i want 100th (
rownumber()) pass specific date situation = 'f' ordering hour , min
... in words needs put in these clauses:
row_number() over(partition data, situation order hour, min) so main mistake put hour , min in partition by clause, making record count starting 1 minute difference found, giving of records number 1.
edit
it seems oracle not retain same row number when not selected. because order hour, min not deterministic. whatever reason, can solved selecting row in outer query:
select pass, row ( ... etc ...) row = 100 if need pass, can wrap query again:
select pass ( select pass, row ( ... etc ...) row = 100 ) 
Comments
Post a Comment