How to structure my SQL Query for showing unique data and combining with only showing the most current date

I am a pretty novice coder/scripter, and have primarily studied creating MySQL scripts.

I am now needing to create a script for executing on MS SQL Server.

There are 4 columns in a table (MasterAlert) which I need to reference for extracting my required data:

Id
AlertTypeId
C_Date
EffectiveDate

I want to create a script that will output each unique AlertTypeId associated with an Id. I want it to display only the most recent C_Date associated with the AlertTypeId, however only if EffectiveDate is less than today’s date.

I’m not quite certain how to structure the logic for this in a SQL statement.

I was thinking I could use Distinct to help give me my unique values, however I don’t know how to tie that into only showing the most current value.

Could someone please help point me in the right direction? Please let me know if you need me to elaborate on any of the above.

I cannot test it right now but my first guess would be

select AlertTypeId , max(C_Date) from MasterAlert where EffectiveDate < getdate() group by AlertTypeId

Thanks, I will check with my IT staff to see why my script is not working.

When I include either “max(C_Date)” or a “Group By”, then I get error messages:

Column ‘MasterAlert_UV.MasterId’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Then you added MasterId to the select statement which is not possible. If you also need the id the query will be more complicated.

Yes, the MasterId must be included in the output.

In some google results for similar scripts, I have noted that there is mention of partitioning results? I have no familiarity with that, but I suspect that is maybe what needs to be done?

No, you just need to include all selected fields in the group by. It’s a quirk that has been in SQL forever.

SELECT MasterId
     , AlertTypeId
     , MAX(C_Date) AS LatestDate
  FROM MasterAlert 
 WHERE EffectiveDate < GETDATE() 
 GROUP BY MasterId
        , AlertTypeId

What this essentially does is take ALL of the rows, then sorts them out and throws all the results that don’t match the criteria out.

So from

MasterID  AlertTypeId  C_Date
-------- ------------ -------
    1          2      1/1/2022
    1          2      1/2/2022
    1          2      1/3/2022
    2          2      1/1/2022
    2          2      1/2/2022
    1          3      2/5/2022
    1          3      2/6/2022
    1          3      2/7/2022
    2          4      4/1/2022
    2          4      1/2/2022

to

MasterID  AlertTypeId  C_Date
-------- ------------ -------
    1          2      1/3/2022
    2          2      1/2/2022
    1          3      2/7/2022
    2          4      4/1/2022

If you’re used to an excel document, it’s like using the A=>Z sort and it asks you to expand the criteria. Same concept. It needs ALL the data to know how to sort it properly.

1 Like

I was still getting the same errors with that code.

Fortunately one of our IT people were able to help me out with this (they are very busy and rarely available so I got lucky on this today):

With Alert_CTE
   ( MasterId
   , AlertTypeId
   , C_Date )
As ( Select MasterId
      , AlertTypeId
      , Max(C_Date)
   From MasterAlert_UV
   Where GetDate() Between EffectiveDate And ExpiredDate
      And AlertTypeId In (1, 2, 13)
   Group By MasterId
      , AlertTypeId)
Select mpa.*
   From Alert_CTE       pa
   Join MasterAlert_UV mpa On mpa.MasterId = pa.MasterId
      And mpa.AlertTypeId    = pa.AlertTypeId
      And mpa.C_Date         = pa.C_Date

Way overkill for what you asked.

What errors?

That query does nothing except cause more time because it’s going to do all the group by work, THEN go back and pull the records from the table which match the filtered values out, which depending on your table size can get VERY expensive CPU/time wise.

Only thing I see different is the date check, which depending on how you coded it originally might have caused you issues…but that’s not what you asked for originally…