Guillaume L. - 7 months ago 22

SQL Question

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
;
```

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