Skip to content
Advertisement

Only display values of latest available event type

SQL Fiddle:

In the table above the purchasing process for different campaigns is displayed using an Event_Type and a Quantity that applies to the Event_Type. Every campaign can only have each Event_Type once.


Now, I want ot get the Quantity of the latest available status within the purchasing process for each Campaign.
Summarized the logic is the following:

Therefore, the result should look like this:

In order to achieve this I tried to go with the CASE function but could not make it work:

What do I need to change to make it work?

Advertisement

Answer

This is a prioritization query.

In MySQL 8+, use window functions:

In earlier versions, a simple method is a correlated subquery:

If you have a lot of data, you want an index on Purcahsing(campaign, event_type) for this query.

Here is a db<>fiddle.

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement