How to give group numbers based on a condition in sql

Question

Here is my table. I am using Snowflake

CREATE TABLE testx
(
    c_order int,    
    c_condition varchar(10)
);

INSERT INTO testx 
VALUES (1, 'and'), (2, 'and'), (3, 'or'), (4, 'and'), (5, 'or');

SELECT * FROM testx



c_order  c_condition
--------------------
1         and
2         and
3         or
4         and
5         or

I am trying to write a query which will give me group numbers based on the fact that consecutive 'and's should be with same group number. when 'or' comes, it should increase the group number. by the way, we should maintain the c_order also.

Here is the expected result:

c_order  c_condition   group_no
-------------------------------
1         and              1
2         and              1
3         or               2
4         and              2
5         or               3

I have tried using dense_rank like this:

SELECT 
    *, 
    DENSE_RANK() OVER (ORDER BY c_condition) 
FROM testx

But it doesn't return exactly what I want. Can somebody please help?`

Answer 1

The 1 equal sign (=) is used for assigning values, it is not used for comparisons.

Comparisons are made with == or ===, that makes your $stock_return === "Yes"; line incorrect as well because it looks like you want to assign a value to $stock_return

The difference between == and === is that

== is for testing if both values are equal, with php, a string '20' and an integer 20 are equal

=== is more strict and will test the type as well so a string '20' is no longer equal to an integer 20

Your code should look something like this to be correct

$vendor_name = "Stock Returned";
if($vendor_name == "Stock Returned")
{
    $stock_return = "Yes";
}
else
{
    $stock_return = "No";
}
echo $stock_return;