Guillaume L. Guillaume L. - 1 month ago 4
SQL Question

Try optimize a specific SQL query

I use this SQL query (PostgreSQL) :

select *
from (select row_number()
over (order by oid1, oid2, oid3)
as row_number, *
from snmpProperties)
as toto
where toto.row_number=(select row_number
from (select row_number()
over (order by oid1, oid2, oid3)
as row_number, *
from snmpProperties)
as titi
where titi.oid1='4'
AND titi.oid2='1'
AND titi.oid3='')+1;


But it uses the same select two times :

select row_number()
over (order by oid1, oid2, oid3)
as row_number, *
from snmpProperties


Is it possible to factorize my request to use this select one time ?

thanks,
Guillaume

Answer
select      *

from       (select      lag ((oid1,oid2,oid3)) over (order by oid1, oid2, oid3) = ('4','1','')  as is_requested_row
                       ,s.*

            from        snmpProperties as s
            ) as s

where       is_requested_row
;

Addition solution, just to demonstrate that it can also be done based on row_number

select      *

from       (select      min (case when (oid1,oid2,oid3) = ('4','1','') then rn end) over ()   base_rn
                       ,s.*

            from       (select      row_number() over (order by oid1, oid2, oid3) as rn, s.*
                        from        snmpProperties as s
                        ) as s
            ) as s

where       rn = base_rn + 1
;

Explanations

1) Using the LAG window function, every records looks at the (oid1,oid2,oid3) vector of its previous record (when ordered oid1,oid2,oid3) and compares it to ('4','1',''). When the comparison it true that means we are standing on the requested record, the record that follows the record with oid1='4', oid2='1' and oid3=''.

2) Using the row_number window function, we're giving row numbers to the records ordered by oid1,oid2,oid3. Using the min window function, we're putting the row_number of the row with oid1,oid2 and oid3 values of '4','1' and '' in every row in the set. We're taking the row that its row_number is equal that row_number + 1

Comments