Get the correct output

  • kbhanu15

    SSCarpal Tunnel

    Points: 4411

    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

  • scdecade

    SSC Eights!

    Points: 807

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

     

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 719130

    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: 996475

    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)
    Forum FAQ

  • kbhanu15

    SSCarpal Tunnel

    Points: 4411

    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

  • scdecade

    SSC Eights!

    Points: 807

    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 2 months, 2 weeks ago by  scdecade.
    • This reply was modified 2 months, 2 weeks ago by  scdecade.
  • Steve Jones - SSC Editor

    SSC Guru

    Points: 719130

    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?

  • scdecade

    SSC Eights!

    Points: 807

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

  • scdecade

    SSC Eights!

    Points: 807

    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 2 months, 2 weeks ago by  scdecade.
    • This reply was modified 2 months, 2 weeks ago by  scdecade.
  • scdecade

    SSC Eights!

    Points: 807

    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;
  • ScottPletcher

    SSC Guru

    Points: 98434

    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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • scdecade

    SSC Eights!

    Points: 807

    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.

     

  • ScottPletcher

    SSC Guru

    Points: 98434

    scdecade 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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • scdecade

    SSC Eights!

    Points: 807

    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.

  • Jeff Moden

    SSC Guru

    Points: 996475

    scdecade 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)
    Forum FAQ

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

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