Group By help

[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

[chazzy]

i'm not sure what you mean. query looks right. why do you need max though?

[jarrren]

Yes, the query works, but isn't it sloppy to group by that many fields? It doesn't logically make sense. I'm using a MAX function because when a user changes their preferences it is appended in the database and given a timestamp, so I need to pull the most recent one.

[russell]

isn't it sloppy to group by that many fields?
not at all. must group by every field not aggregated.

[jarrren]

Hmm. I thought differently because of this article...

http://weblogs.sqlteam.com/jeffs/archive/2005/12/14/8546.aspx

[chazzy]

That post isn't talking about sloppiness it's talking about performance.

Joins will always outperform, much less to have to consolidate.

This should be the rewrite of your query in that format.


Select DISTINCT

a.USER_ID, a.EMAIL, a.FIRST_NAME, a. LAST_NAME, a.COBRAND_ID, a.SUBSCRIBED,
b.USER_COMM_ALERT_ID, PREF_ACTIVE_FLAG, MAX_DATE

From tableA a

INNER JOIN
(select USER_COMM_ALERT_ID, PREF_ACTIVE_FLAG, MAX(TIMESTAMP_) AS MAX_DATE

from tableB b WHERE AND USER_COMM_ALERT_ID = 1 AND PREF_ACTIVE_FLAG=1) c

ON a.USER_ID=.USER_ID

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

[jarrren]

Ahh thank you!

My script that I tried didn't work because i didn't include USER_COMM_ALERT_ID and PREF_ACTIVE_FLAG in the Select portion of the inner join.

Why do I have to include those fields in the inner join select tag, as well as the main select tag? If I only have it in one spot the query breaks.

Having to select all those fields in my inner join forces me to group by all the non aggregated fields, again =/

[carsonweb]

May I ask what is that $ in the query statement?

[jarrren]

$a$ is a variable name for a table