Post reply

grouping by *

  • July 25, 2018 at 2:00 pm

    #354945

    I have this code:

    select
    D.*
    ,H.[Customs_Recon_Entry_Num] as RH_Recon_entry
    ,max(cast([Recon_File_Date] as Date)) as Recon_File_Date
    from
    [NAFTAFORD].[STEP1_DUTYIMPACT_MAX3] D
    LEft Join
    [ADHOC].[ATS_RL] L
    on D.ENTRY_NUM = l.Customs_Entry_Num
    inner join
    [ADHOC].[ATS_RH] H
    on H.trans_sk = L.trans_Sk
    group by HELP ME HERE!  lol

    SInce i want MAX Recon_file_date, i know i know i need to group.
    I dont know how to group on D*.
    Can i even do that?

    thanks

  • July 25, 2018 at 2:33 pm

    #1998994

    jeffshelix - Wednesday, July 25, 2018 2:00 PM

    I have this code:

    select
    D.*
    ,H.[Customs_Recon_Entry_Num] as RH_Recon_entry
    ,max(cast([Recon_File_Date] as Date)) as Recon_File_Date
    from
    [NAFTAFORD].[STEP1_DUTYIMPACT_MAX3] D
    LEft Join
    [ADHOC].[ATS_RL] L
    on D.ENTRY_NUM = l.Customs_Entry_Num
    inner join
    [ADHOC].[ATS_RH] H
    on H.trans_sk = L.trans_Sk
    group by HELP ME HERE!  lol

    SInce i want MAX Recon_file_date, i know i know i need to group.
    I dont know how to group on D*.
    Can i even do that?

    thanks

    You can, but you can't use the shortcut.  You have to list each of the fields individually.  Another option is to use a CTE to pre-aggregate your [ADHOC].[ATS_RL] (assuming that's where the Recon_File_Date field is located).

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • July 25, 2018 at 2:40 pm

    #1998997

    jeffshelix - Wednesday, July 25, 2018 2:00 PM

    I have this code:

    select
    D.*
    ,H.[Customs_Recon_Entry_Num] as RH_Recon_entry
    ,max(cast([Recon_File_Date] as Date)) as Recon_File_Date
    from
    [NAFTAFORD].[STEP1_DUTYIMPACT_MAX3] D
    LEft Join
    [ADHOC].[ATS_RL] L
    on D.ENTRY_NUM = l.Customs_Entry_Num
    inner join
    [ADHOC].[ATS_RH] H
    on H.trans_sk = L.trans_Sk
    group by HELP ME HERE!  lol

    SInce i want MAX Recon_file_date, i know i know i need to group.
    I dont know how to group on D*.
    Can i even do that?

    thanks

    You might find (depending on how your date is structured) to do the grouping within the inner join and maybe change the inner join to be a cross apply.
    I can't work out from your query which table the [Recon_File_Date] column is coming from. It would be helpful if you could add the table alias for this column in the select.

  • July 26, 2018 at 5:11 am

    #1999052

    jeffshelix - Wednesday, July 25, 2018 2:00 PM

    I have this code:

    select
    D.*
    ,H.[Customs_Recon_Entry_Num] as RH_Recon_entry
    ,max(cast([Recon_File_Date] as Date)) as Recon_File_Date
    from
    [NAFTAFORD].[STEP1_DUTYIMPACT_MAX3] D
    LEft Join
    [ADHOC].[ATS_RL] L
    on D.ENTRY_NUM = l.Customs_Entry_Num
    inner join
    [ADHOC].[ATS_RH] H
    on H.trans_sk = L.trans_Sk
    group by HELP ME HERE!  lol

    SInce i want MAX Recon_file_date, i know i know i need to group.
    I dont know how to group on D*.
    Can i even do that?

    thanks

    If you were to post some data according to the article at the first link under "Helpful Links" in my signature line below, I believe I can show you how to do this without a GROUP BY using MAX() OVER.

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

  • July 26, 2018 at 6:48 am

    #1999073

    Jeff Moden - Thursday, July 26, 2018 5:11 AM

    If you were to post some data according to the article at the first link under "Helpful Links" in my signature line below, I believe I can show you how to do this without a GROUP BY using MAX() OVER.

    If my assumptions are correct I don't think MAX() OVER will work as I think the query is flawed due to LEFT JOIN and the following INNER JOIN.
    Due to the OP asking to group by D.* and wanting max date indicates to me that the data in ATS_RL and ATS_RH is only required once per row in D
    This is my query based on my assumptions (which could be completely BS)

    SELECT D.*
      ,a.Customs_Recon_Entry_Num
      ,a.Recon_File_Date
    FROM [NAFTAFORD][STEP1_DUTYIMPACT_MAX3] D
      OUTER APPLY (
       SELECT TOP(1)
         H.Customs_Recon_Entry_Num,
         CAST(Recon_File_Date as date)
       FROM [ADHOC].[ATS_RL] L
         JOIN [ADHOC].[ATS_RH] H
          ON H.trans_sk = L.trans_Sk
       WHERE L.Customs_Entry_Num = D.ENTRY_NUM
       ORDER BY Recon_File_Date DESC
       ) a (Customs_Recon_Entry_Num,Recon_File_Date);

    Far away is close at hand in the images of elsewhere.
    Anon.

  • July 27, 2018 at 3:16 pm

    #1999313

    Yeah that left join isn't going to function as a left join.

    "If I had been drinking out of that toilet, I might have been killed." -Ace Ventura

Viewing 6 posts - 1 through 5 (of 5 total)

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