Select rows from a particular row to latest row if that particular row type exist-CodePudding

I want to achieve these two requirements using a single query. Currently I'm using 2 queries in the program and use C# to do the process part something like this.

Pseudocode

select top 1 id from table where type=b
if result.row.count > 0 {var typeBid = row["id"]}
select * from table where id >= {typeBid}
else
select * from table

Req1: If there is records exist with type=b, In this case select result should be latest row with type=b and all other rows added after.

Table

--------------------
id  type    date
--------------------
1   b   2021-10-15
2   a   2021-11-16
3   b   2021-11-19
4   a   2021-12-02
5   c   2021-12-12
6   a   2021-12-16

Result

--------------------
id  type    date
--------------------
3   b   2021-11-19
4   a   2021-12-02
5   c   2021-12-12
6   a   2021-12-16

Req2: There is NO record exist with type=b query should select all the records in the table

Table

---------------------
id  type    date
---------------------
1   a   2021-10-15
2   a   2021-11-16
3   a   2021-11-19
4   a   2021-12-02
5   c   2021-12-12
6   a   2021-12-16

Result

--------------------
id  type    date
--------------------
1   a   2021-10-15
2   a   2021-11-16
3   a   2021-11-19
4   a   2021-12-02
5   c   2021-12-12
6   a   2021-12-16

CodePudding user response：

with max_b_date as (select max(date) as date
                    from table1 where type = 'b')
select t1.*
from table1 t1
cross join max_b_date 
where t1.date >= max_b_date.date
   or max_b_date.date is null

(table is a SQL reserved word, https://en.wikipedia.org/wiki/SQL_reserved_words, so I used table1 as table name instead.)

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=bd05543a9712e27f01528708f10b209f

CodePudding user response：

Looks Like you can use an OR condition here

SELECT  
    *
    FROM
    (
        SELECT  
            *,
            BCount = COUNT(CASE type WHEN 'B' THEN 1 ELSE NULL END)-- to get the Count of Records with Type b.
            FROM Table
    )Q
    WHERE 
    (
        BCount > 0 AND id >= (select top 1 id from table where type=b)-- if there are Row's with Type b then select Req#1
    )
    OR
    (
        BCount = 0 -- if THere are no rows with Type B select All
    )

CodePudding user response：

My solution :

If no type b not in table, i set filter to DATEADD(year, 2000, getdate()) (far date to include all records)
If type b exist, i set filter to it and take records strating from this date

 Select * from tbl
    where 
    tbl.date >=
    (select 
    case when count(*)=0 then DATEADD(year, 2000, getdate())
    else 
    max(tbl.date)
    end
    from tbl where tbl.type='b')