asked on

SQL Query Help Top 1 and Distinct?

Good Day Experts!

I have some functionality that records, in a SQL table, each time a timed event runs from my dashboard. After all the timed events are completed, I need to get the last occurrence that ran for each timed event so I can report it back to the dashboard. I have been trying to use a combination of Top1 and Distinct but cannot seem to come up with the proper solution. My table is pretty straight forward with only 2 fields...Event and DateRan fields. So I would just need a list of all the unique Events and the last DateRan for each.

Can you help point me in the right direction?

Thanks,
jimbo99999

SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mike Eghtebas

;with cte([Event], DateRan)
  AS
  (
    Select [Event], max(DateRan) LastDate From myTable
	Group By [Event]
  )
  Select * From cte;

Open in new window

The solution from Vitor should work. The cte above is a different flavor of the same thing.

Vitor Montalvão

Mike, the CTE will only add overhead. I can't see any advantage of using it.

Jimbo99999

ASKER

Thanks all for your responses. I will be try your suggestions this morning.

Thanks,
jimbo99999

Jimbo99999

ASKER

Thanks for the help. That link is excellent and I will put it in my toolbox.

Thanks,
jimbo99999