showing record with max date

Hello I have a dataset which pulls record numbers(gn_id) and its pulling some duplicates and it looks like if it that gn_id has a duplicate I want to pull the one with the most recent or max date but am unsure on how to accomplish this, I have attached a screenshot of what I'm looking at as on this example would want to show the row with dispatch date of 6/4




  • You can filter the duplicates out using either a MySQL, or magic ETL dataflow.  Let me know your preference and I can try to show you the steps.


    If you want to keep the duplicate records in your dataset but only show one row on this card, try changing the agregation for your date column to be MAX

    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • probably would need to do it through ETL, as doing it on the card I could have a bunch of gn_id numbers which have mulitple records and would just want to pull the one with the max dispatchdate

  • There may be other ways to do this, but I would start by ranking the dates and then filtering the data to only include the max date for each ID.







    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • so I added what you had suggested and my though was it would now in your example show the row for gn_id 1,111,111 with the max dispatchdate in this case being 5/28/19, correct?

  • attached is the way I currently have it, I have an inner join between loads and dispatch, loads contains gn_id, dispatch contains the dispatchdate

  • Canioagain
    Canioagain Contributor

    @ST_-Superman-_ An older post but VERY helpful. I forgot all about rank. Was about to do this in SQL.

    Great pictures to follow along to


  • This was very helpful for me!!