If dupes are ok but the alerttypeid may only be in (4, 6), then maybe this works

with only_two_cte(alertid) as (
    select
   ...

with only_two_cte(alertid) as (
    select
      alertid
    from
      #aaa
   ...

• This reply was modified 5 years, 10 months ago by Steve Collins.
• This reply was modified 5 years, 10 months ago by Steve Collins.

Ok yeah I'm starting to see the alerttypeid's should be counted separately.

with only_two_cte(alertid) as (
    select
      alertid
    from
      #aaa
   ...

• This reply was modified 5 years, 10 months ago by Steve Collins.
• This reply was modified 5 years, 10 months ago by Steve Collins.

select distinct
  first_value(alertid) over (partition by alerttypeid order by alertid desc) alertid,
  alerttypeid
from
  #aaa
where
  alerttypeid in(4,6);

This...

;with
ordered_amounts as (
    select distinct first_value(amount) over (partition by userid order by keyid desc) amount from #t)
select 
  sum(amount) total
from
  ordered_amounts;

I need one column for BSP between 1 and 3, one column for BSP between 3 and 5, one column for bsp between 5 and 8 (and up to...

Sometimes spreadsheets are useful to concatenate together parameterized sql code.

="sum(case when (td.BSP >="&B7&" and td.BSP <"&C7&") and td.WIN_LOSE=1 then td.BSP end) as SumWinnerPrice"&B7&"to"&C7
="count(case when (td.BSP >="&B8&" and...

declare
  @txt            varchar(100)='CKnovelty2002_pouch-5AR-OneSize';
  --@txt          ...

By "EF" do you mean "Entity Framework" as in Microsoft? You do realize...

"...from a developer point of view..."  Why is it "more difficult" to build with stored procedures:

1. It makes your entire project dependent on the DBA.  Not as satisfying as being...

It's all predicated on the false assumption "a billion flies can't be wrong"

Primarily through indexing.  Indexes are logical objects which exist for purely physical...

There are a lot of assumptions baked into the questions.  Why are you storing shopping cart information as serialized data?