Wsrtjtyk

I've been working on this query and it's working as intended by displaying one output per p.ID since I'm grouping by p.ID (since there would be multiple p.ID values without the grouping.) However, depending on the p.ID, I will sometimes get a value of e.alternateEventDurationInMilliseconds that is not the fastest e.alternateEventDurationInMilliseconds.

Is there a way I could still use the GROUP BY p.ID but also add a check to get the minimum e.alternateEventDurationInMilliseconds as long as the duration is still > 0? Otherwise, I'll be given a random value by default that is greater than 0, but I want to specifically get the minimum duration that is greater than 0, with only one row per p.ID.

Thanks! :)

SELECT

    p.ID,

    e.personaId,

    e.EVENTID,

    e.alternateEventDurationInMilliseconds                  

FROM

    EVENT_DATA e

INNER JOIN

    PERSONA p ON e.personaId = p.ID

WHERE

    e.EVENTID = '43' AND e.alternateEventDurationInMilliseconds > '0'

GROUP BY

    p.ID

ORDER BY

    e.`alternateEventDurationInMilliseconds` ASC

You have to use the MIN() aggregate function to select the minimun value (of a particular column) of each group after grouping. Also, why you need to join table PERSONA if you only are retrieving the ID from that table, but this ID is already part of the table EVENT_DATA (on column personaId). The query you may need is this one:

SELECT

    p.ID,

    MIN(e.personaId), -- This could be replaced by ANY_VALUE(e.personaId).

    MIN(e.EVENTID), -- This could be replaced by ANY_VALUE(e.EVENTID).

    MIN(e.alternateEventDurationInMilliseconds) AS minAlternateEventDurationInMilliseconds

FROM

    EVENT_DATA e

INNER JOIN

    PERSONA p ON e.personaId = p.ID

WHERE

    e.EVENTID = '43' AND e.alternateEventDurationInMilliseconds > 0

GROUP BY

    p.ID

ORDER BY

    minAlternateEventDurationInMilliseconds ASC

If you don't need other data from table PERSONA with the exception of the ID, previous query could be simplified to this:

SELECT

    MIN(e.personaId), -- This could be replaced by ANY_VALUE(e.personaId).

    MIN(e.EVENTID), -- This could be replaced by ANY_VALUE(e.EVENTID).

    MIN(e.alternateEventDurationInMilliseconds) AS minAlternateEventDurationInMilliseconds

FROM

    EVENT_DATA e

WHERE

    e.EVENTID = '43' AND e.alternateEventDurationInMilliseconds > 0

GROUP BY

    e.personaId

ORDER BY

    minAlternateEventDurationInMilliseconds ASC

You'll have to make a new table in a subquery and then join to that table.

This is the query that will give you the e.id and min (but not zero) data you're looking for:

select id, personalID, EVENTID,

min(alternateEventDurationInMilliseconds) as mill,

from EVENT_DATA

 where alternateEventDurationInMilliseconds > 0

 and e.EVENTID=43

 group by personaId;

so you should use that table that is created by the subquery as a joined table instead of joining to EVENT_DATE, something like:

join ( select id, personalID, e.EVENTID,

min(alternateEventDurationInMilliseconds) as mill,

from EVENT_DATA

 where alternateEventDurationInMilliseconds > 0

 and e.EVENTID=43

 group by personaId ) e...

I think that should work.

This may also work, but I'm not sure - just take your previous query and change e.alternateEventDurationInMilliseconds to "min(e.alternateEventDurationInMilliseconds) as e.alternateEventDurationInMilliseconds" to your original query.

You can use this: (assuming mysql)

SELECT p.ID, e.alternateEventDurationInMilliseconds from persona p

join (select min(alternateEventDurationInMilliseconds) as alternateEventDurationInMilliseconds, personaid

from event_data where personaid = '43' and alternateEventDurationInMilliseconds > 0 

group by personaid) as e on e.personaid = p.Id;

This is a famous problem:

The Rows Holding the Group-wise Maximum of a Certain Column