SQLTeam.com | Weblogs | Forums

Case in where clause?


I have this attempt to have a case in the where clause:
WHERE CASE WHEN sg.storecode = 'Y1' THEN sg.storecode
ELSE sg.storecode END = sg.storecode

I know it's not correct but I don't know how to code this. Basically what I want to do is if there are two records with storecode 'Y1' and 'S1', always get the record with the 'Y1'.


Here is a sample data:
number year level storecode
31125 2013 LP3 Y1
31125 2014 LP3 Y1
31125 2015 LP4 Y1
31125 2016 LP4 Y1
31125 2017 LP3 S1
31125 2017 LP4 Y1

I want to be able to filter these so that I only get all records with storecode='Y1'. The one records with 'S1' should not be returned.


You are probably going to need to be more clear, but based on what you said should just be simple Where clause

if OBJECT_ID('tempDb..#sample_data') is not null drop table #sample_data

create table #sample_data
number numeric,
[year] smallint,
[level] char(3),
storecode char(2)

insert #sample_data 

select  * 
from #sample_data 
where storecode = 'Y1';

select [number]
      ,max(storecode) as storecode
  from yourtable
 group by [number]


Another approach:

select top(1) with ties
  from yourtable
 order by row_number() over(partition by [number]
                                order by storecode desc