Group By Function

[Jarrren]

I'm trying to pull the latest record from a preference table, and check to see if that user is subscribed to emails, and they have a preference of 1. Also their preference active flag must be 1.

When I use the MAX function, it forces me to use group by as well. I also get errors unless I group by all the fields I'm selecting. Is there a better way to write this code? Inner query maybe?


Select DISTINCT $A$.USER_ID, $A$.EMAIL, $A$.FIRST_NAME, $A$. LAST_NAME, $A$.COBRAND_ID, $A$.SUBSCRIBED, $B$.USER_COMM_ALERT_ID, $B$.PREF_ACTIVE_FLAG, MAX($B$.TIMESTAMP_) AS MAX_DATE

From $A$ INNER JOIN $B$ ON $A$.USER_ID=$B$.USER_ID

Where $A$.Subscribed=1 and $B$.User_Comm_Alert_ID=1

GROUP BY $A$.USER_ID, $A$.EMAIL, $A$.FIRST_NAME, $A$. LAST_NAME, $A$.COBRAND_ID, $A$.SUBSCRIBED, $B$.USER_COMM_ALERT_ID, $B$.PREF_ACTIVE_FLAG HAVING $B$.PREF_ACTIVE_FLAG=1

[i2eye]

Have you tried using Analytic Functions? See if this works... not sure with the syntax of your table... you might have to play with it.

Select DISTINCT $A$.USER_ID, $A$.EMAIL, $A$.FIRST_NAME, $A$. LAST_NAME,
$A$.COBRAND_ID, $A$.SUBSCRIBED, $B$.USER_COMM_ALERT_ID,
$B$.PREF_ACTIVE_FLAG, MAX($B$.TIMESTAMP_) OVER (PARTITION BY $B$.TIMESTAMP_)

From $A$ INNER JOIN $B$ ON $A$.USER_ID=$B$.USER_ID

Where $A$.Subscribed=1 and $B$.User_Comm_Alert_ID=1

HAVING $B$.PREF_ACTIVE_FLAG=1