How can I select conditional column between records? [closed]-CodePudding

this is my sql table

 create table #Deneme
 (
    pk_id INT,
    firstname nvarchar(50),
    lastname nvarchar(50),
    process_type smallint,
    create_date datetime,
    modify_date datetime
 )

and below image is records.

All Records

This is my sql query

 select 
 firstname,
 lastname,
 process_type
 from #Deneme where firstname='John' and lastname='Doe'

This is query result

Query Result

This query results have same firstname and same last name. I want to filter this records like this; If process type bigger than zero select a record which has recent create date else select record which has zero process type. So I want to get 3. record. How can I do that. Case when or another one?

CodePudding user response：

where firstname='John' and lastname='Doe' and 
(
    process_type=0
    or
    (process_type>0 and create_date>dateadd(day,-5,getdate())
)

Change dateadd accordingly to specify what you consider "recent"

CodePudding user response：

If I got it right, actually you will get 2 rows.

 Select firstname, lastname, process_type, create_date, modify_date
 From #Deneme 
 Where firstname='John' and lastname='Doe' And
       ((process_type > 0 And create_date >= ALL (Select create_date From #Deneme Where firstname='John' and lastname='Doe' And process_type > 0)) Or
       process_type = 0)

CodePudding user response：

Row_number over partition can help you filter complex logic.

;with data 
as (
    select firstname,
           lastname,
           process_type,
           row_number() over (partition by firstname, lastname order by create_date desc) rid

    from #Deneme 
    where firstname='John' and lastname='Doe')

select *
from data where rid=1

CodePudding user response：

If process type bigger than zero select a record which has recent create date else select record which has zero process type.

This sounds like an ordering problem where you want to select one row. If so you can use select top (1) with the appropriate order by clause:

select top (1) d.*
from #deneme d
where firstname = 'John' and lastname = 'Doe'
order by (case when process_type > 0 then 1 else 2 end),
         create_date desc;

If you wanted to do this for all names, then you can use a trick using row_number():

select top (1) with ties d.*
from #deneme d
where firstname = 'John' and lastname = 'Doe'
order by row_number() over (partition by firstname, lastname
                            order by (case when process_type > 0 then 1 else 2 end),
                                     create_date desc
                            );