CODEWITHSUNDEEP
oracle-databaseplsql
user3313364
user3313364

Reputation: 1

PL/SQL How to select n-1 values?

declare 
    hour_in number:=4;
    minute_in number:=59;
    sek_in number:=45;
    together_in number;
    sat varchar(10):='G6';
    M0 number;
begin
    kopa_in:=((sek_in/60)+minute_in)/60+hour_in;

    select M0 into M0 
    from jsm_nav 
    where ((sekunde/60)+minute)/60+stunda<together_in 
    and prn=sat;

    select delta_n into deltan 
    from jsm_nav 
    where ((sekunde/60)+minute)/60+stunda<together_in 
    and prn=sat;
end;

This only works if there is only one value that is lower than together_in! I need to select n-1 value, if we assume that together_in is n value! Thanks!

Upvotes: 0

Views: 372

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269883

What do you mean you want "n - 1" values? You can only put one into a variable.

An easy way to get one value without changing the query very much is to use min() or max():

select min(M0) into M0 from jsm_nav where ((sekunde/60)+minute)/60+stunda<together_in and prn=sat;
select min(delta_n) into deltan from jsm_nav where ((sekunde/60)+minute)/60+stunda<together_in and prn=sat;

Of course, you don't need two queries to do this:

select min(M0),min(delta_n) into M0, jsm_nav
from jsm_na
where ((sekunde/60)+minute)/60+stunda<together_in and prn=sat;

Upvotes: 1

Razvan
Razvan

Reputation: 2596

SELECT INTO requires one and only one row. You should limit the number of rows returned by your queries.

One way to do this is using ROWNUM:

select M0 into M0 from (select M0 from jsm_nav where ((sekunde/60)+minute)/60+stunda<together_in and prn=sat order by ((sekunde/60)+minute)/60+stunda desc) where rownum = 1;
select delta_n into deltan from (select delta_n from jsm_nav where ((sekunde/60)+minute)/60+stunda<together_in and prn=sat order by ((sekunde/60)+minute)/60+stunda desc) where rownum = 1;

I used a combination of ROWNUM and ORDER BY ... DESC so that you get exactly one row (the row that is closest to together_in).

Upvotes: 0

Elfentech
Elfentech

Reputation: 747

Add this to the given query

and rownum < together_in;

Be sure together_in is well initialized

Upvotes: 0

Related Questions