POSTGRES : SELECT : Query for number of rows which EXIST BETWEEN given range

Question

I have to query the table to search for given conditions :

order_no specified in the range must exist.

Example: I am getting a request for a range of order_no: 003 - 007. So, I want to check whether all the order_no mentioned in range exist or not.If not exist return false.
The order_status of ALL order_no must be 1. If not exist return false.

Order_string Order_no Order_status ABE 1 1 ABE 2 1 ABE 3 0 PSB 4 0 PSB 5 1 PSB 6 0 REL 7 0 REL 8 1 REL 9 1 REL 10 1

I was trying EXIST and BETWEEN but unable to query the DB.

if you have a parameter of "all" what is the "range"? I think your question is missing some information — Paul Maxwell
– Paul Maxwell, Commented Dec 4, 2017 at 6:17
@Used_By_Already I have edited the description. Is it seems correct? — Anand Vaidya
– Anand Vaidya, Commented Dec 4, 2017 at 6:23

klin · Accepted Answer · 2017-12-04 08:29:02Z

4

You should check these conditions:

count(order_no) = count(distinct order_no) -- order_no are distinct in the range
and count(*) = end_val- start_vat+ 1 -- number of rows equals to size of the range
and bool_and(order_status::bool) -- all order_status are 1

The first condition is not necessary if the column is unique.

You can do this in the function:

create or replace function check_range(int, int)
returns boolean language sql as $$
    select 
        count(order_no) = count(distinct order_no)
        and count(*) = $2- $1+ 1
        and bool_and(order_status::bool)
    from my_table
    where order_no between $1 and $2;   
$$;

edited Dec 4, 2017 at 8:29

answered Dec 4, 2017 at 6:43

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Vash · Accepted Answer · 2017-12-04 06:38:39Z

0

If I understand you question correctly, you want a false if at least one of the ORDER_STATUS for ORDER_NO between 3 and 7 is 0

SELECT
CASE WHEN Z.SUM_STATUS > 0 THEN 'FALSE' ELSE 'TRUE' END AS RESULT
FROM    
    (SELECT
    SUM(CASE WHEN (ORDER_NO BETWEEN 3 AND 7) AND (ORDER_STATUS = 0) THEN 1 ELSE 0 END) AS SUM_STATUS
    FROM YOUR_TABLE) Z;

answered Dec 4, 2017 at 6:38

Vash

1,7972 gold badges13 silver badges19 bronze badges

Comments

Shushil Bohara · Accepted Answer · 2017-12-04 06:39:13Z

0

TRY: You can use this concept, if it will return 1 then TRUE else FALSE

SELECT 1 
FROM tmp t
WHERE Order_no BETWEEN 003 AND 007
    AND Order_status = 1
HAVING COUNT(*) = (007-003)+1

answered Dec 4, 2017 at 6:39

Shushil Bohara

5,6662 gold badges19 silver badges32 bronze badges

Collectives™ on Stack Overflow

POSTGRES : SELECT : Query for number of rows which EXIST BETWEEN given range

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related