Get the correct output
-
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
-
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
-
You want all alerttype=[4,6] records or the first or last one? That's not a very good requirement statement.
-
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) -
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
-
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;Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
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?
-
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
-
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
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
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
-
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) = 0SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
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
- 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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
- 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
- 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)
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply