Get the correct output

Question

Get the correct output

kbhanu15

SSCarpal Tunnel

Points: 4493
More actions
March 19, 2020 at 11:47 am

#3735543

Hi Team,
plese help me to get the query to find the below output.
My data:
create table #aaa
(
alertid int,
alerttypeid smallint
)
insert into #aaa
select 1,1
union
select 1,2
union
select 1,4
union
select 1,6
union
select 2,1
union
select 2,2
union
select 2,4
union
select 2,6
UNION
select 3,4
union
select 3,6
--preent out put:
select * from #aaa
alertid alerttypeid
1 1
1 2
1 4
1 6
2 1
2 2
2 4
2 6
3 4
3 6
expected output: i want only alerttypeid 4,6 records
alertid alerttypeid
3 4
3 6

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Steve Collins SSCrazy Points: 2068 More actions · Answer 1

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

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 2

You want all alerttype=[4,6] records or the first or last one? That's not a very good requirement statement.

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 3

To Steve's good point...

From the posted input and the posted required output, it looks like there should only be a return if a given AlertID only has AlertTypeID's of both 4 and 6. Is that correct?

Or, do you want the latest AlertID that contains both a 4 and 6 AlertTypeID no matter how many other types of alerts there are?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

kbhanu15 SSCarpal Tunnel Points: 4493 More actions · Answer 4

thanks for your respone.

my requuirement is

"there should only be a return

if a given AlertID only has AlertTypeID's of both 4 and 6" nothing else.

thanks

bhanu

Steve Collins SSCrazy Points: 2068 More actions · Answer 5

with only_two_cte(alertid) as (
    select
      alertid
    from
      #aaa
    group by
      alertid
    having
      count(*)=2
      and sum(iif(alerttypeid in(4,6), 1, 0))=2)
select 
  a.*
from
  #aaa a
 join
  only_two_cte otc on a.alertid=otc.alertid;

This reply was modified 4 years, 1 month ago by Steve Collins.
This reply was modified 4 years, 1 month ago by Steve Collins.

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 6

I think that query from scdecade might not work with multiple of the same. If I add two rows of (5,4), I get a return.

This requirement (

"there should only be a return

if a given AlertID only has AlertTypeID's of both 4 and 6")

doesn't make sense. In your dataset, you have alertype = 1 with [4,5]. I assume you mean an alert id only has two alerts, and they are 4 and 6. What if I have alertid 6 and there are types (4,6,6,4). Does that count?

I'm not trying to be difficult, but get you to think about what you've asked for and be specific. We don't know the specs of your system, what's possible, or what bad data could get in. If there are two type 4 alerts, does that work?

Steve Collins SSCrazy Points: 2068 More actions · Answer 7

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

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Steve Collins SSCrazy Points: 2068 More actions · Answer 8

with only_two_cte(alertid) as (
    select
      alertid
    from
      #aaa
    group by
      alertid
    having
      count(*)=2
      and sum(iif(alerttypeid=4, 1, 0))=1
      and sum(iif(alerttypeid=6, 1, 0))=1)
select 
  a.*
from
  #aaa a
 join
  only_two_cte otc on a.alertid=otc.alertid;

The nodupes case should be ok

This reply was modified 4 years, 1 month ago by Steve Collins.
This reply was modified 4 years, 1 month ago by Steve Collins.

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Steve Collins SSCrazy Points: 2068 More actions · Answer 9

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

with only_two_cte(alertid) as (
    select
      alertid
    from
      #aaa
    group by
      alertid
    having
      sum(iif(alerttypeid not in(4, 6), 1, 0))=0
      and sum(iif(alerttypeid=4, 1, 0))>0
      and sum(iif(alerttypeid=6, 1, 0))>0)
select 
  a.*
from
  #aaa a
 join
  only_two_cte otc on a.alertid=otc.alertid;

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

ScottPletcher SSC Guru Points: 100942 More actions · Answer 10

I'd stick with something more SQL-ish, and also more readily readable and understandable, like below. If you're converting Access to SQL Server, it's convenient to use IIF, otherwise avoid it. It's not SQL, and thus it's very hard to understand if you're used to writing SQL.

HAVING 
    MAX(CASE WHEN alerttypeid = 4 THEN 1 ELSE 0 END) = 1 AND
    MAX(CASE WHEN alerttypeid = 6 THEN 1 ELSE 0 END) = 1 AND
    MAX(CASE WHEN alerttypeid NOT IN (1, 4) THEN 1 ELSE 0 END) = 0

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Steve Collins SSCrazy Points: 2068 More actions · Answer 11

Steve and Jeff, thank you. Good to have other eyes on this. 🙂 What should I do with the earlier incorrect code? Wipe it out? Should I have kept updating it? Idk.

Scott, ok coding conventions lol. To the extent iif is more compact it represents a normalization of information and therefore it would be impermissible for me to not make use of it. Also, regarding the upper casing of key words -- the collation applied to Sql Server sql code is case insensitive. Therefore, in my (not so serious) opinion, it should be up to the Capitalizers to explain their fruitless pursuit. According to my coding convention anything that's optional should be left out. Besides capitalizing key words things you never see me do:

"insert into" -- INTO is optional and therefore should be left out

"inner join" -- INNER is optional and therefore should be left out

"left outer join/right outer join" OUTER is optional and therefore should be left out

"select col1 as colname" -- AS is optional and should be left out. Alternate syntax like "group =" is ridiculous and not necessary.

🙂 Everybody has their way.

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

ScottPletcher SSC Guru Points: 100942 More actions · Answer 12

Steve Collins wrote:

Steve and Jeff, thank you. Good to have other eyes on this. 🙂 What should I do with the earlier incorrect code? Wipe it out? Should I have kept updating it? Idk.
Scott, ok coding conventions lol. To the extent iif is more compact it represents a normalization of information and therefore it would be impermissible for me to not make use of it. Also, regarding the upper casing of key words -- the collation applied to Sql Server sql code is case insensitive. Therefore, in my (not so serious) opinion, it should be up to the Capitalizers to explain their fruitless pursuit. According to my coding convention anything that's optional should be left out. Besides capitalizing key words things you never see me do:
"insert into" -- INTO is optional and therefore should be left out
"inner join" -- INNER is optional and therefore should be left out
"left outer join/right outer join" OUTER is optional and therefore should be left out
"select col1 as colname" -- AS is optional and should be left out. Alternate syntax like "group =" is ridiculous and not necessary.
🙂 Everybody has their way.

I prefer to always use INNER and OUTER for clarity.

However, INNER should always be used for a different reason: if you ever need to add a hint to the query, the INNER must be present ... so maybe it's not "optional" after all. Try parsing:

SELECT * FROM dbo.table1 t1 INNER HASH JOIN dbo.table2 t2 ON t1.id = t2.id

vs:

SELECT * FROM dbo.table1 t1 HASH JOIN dbo.table2 t2 ON t1.id = t2.id

The WITH before NOLOCK used to be optional, now it's not. The parentheses around a number after TOP are optional, but may soon not be.

I deeply disagree with the idea of automatically leaving out everything that's optional ... well, currently optional.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Steve Collins SSCrazy Points: 2068 More actions · Answer 13

ScottPletcher wrote:

I prefer to always use INNER and OUTER for clarity.

Clarity could not also come from conciseness? It seems to me adding extra information which is of questionable necessity adds opaqueness.

ScottPletcher wrote:

However, INNER should always be used for a different reason: if you ever need to add a hint to the query, the INNER must be present ... so maybe it's not "optional" after all. Try parsing:
SELECT * FROM dbo.table1 t1 INNER HASH JOIN dbo.table2 t2 ON t1.id = t2.id
vs:
SELECT * FROM dbo.table1 t1 HASH JOIN dbo.table2 t2 ON t1.id = t2.id

Do you know how I do know this? Because I tried it my way and it didn't work.

ScottPletcher wrote:

The WITH before NOLOCK used to be optional, now it's not. The parentheses around a number after TOP are optional, but may soon not be.

I don't have any examples handy of optional syntax which was later deprecated and removed. Maybe it's happened as often as the reverse.

ScottPletcher wrote:

I deeply disagree with the idea of automatically leaving out everything that's optional ... well, currently optional.

Om... om... there is only the present version. We're getting ready to upgrade Azure Sql to compatibility level 150 and there's no point in looking back or carrying relics forward imo.

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 14

Steve Collins wrote:

...there's no point in looking back or carrying relics forward imo.

Dude! I'm right here! 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)