group by problem 1

[LGTOserv]

Hi All;

I have an SQL Qeury that Groups a certain way and then takes the MAX() of a date and displays unique records.. this appears to work fine in one aspect. When i added another field from another table though, it no longer takes the oldest date and displays unique records but displays all the records. Heres the Query:

** the only field i added to this was the T43.login_name field and it broke ..!

select distinct T1.dbid,
T1.id,
T2.login_name,
T3.callid,
T3.companyid,
T4.login_name,
T43.login_name,
T1.headline,
T1.severity,
T1.priority,
T1.submit_date,max(T5.action_timestamp)

from ( ( ( ( ( ( ( CQ_Owner.Defect T1
INNER JOIN CQ_Owner.users T2 ON T1.submitter = T2.dbid )
INNER JOIN CQ_Owner.users T4 ON T1.owner = T4.dbid )
INNER JOIN CQ_Owner.users T43 ON T1.mtester = T43.dbid )
INNER JOIN CQ_Owner.history T5 ON T1.dbid = T5.entity_dbid and 16777240 = T5.entitydef_id )
INNER JOIN CQ_Owner.statedef T6 ON T1.state = T6.id )
LEFT OUTER JOIN CQ_Owner.parent_child_links T3mm ON T1.dbid = T3mm.parent_dbid and 16779300 = T3mm.parent_fielddef_id )
LEFT OUTER JOIN CQ_Owner.gecontactlist T3 ON T3mm.child_dbid = T3.dbid )
where T1.dbid <> 0 and ((T1.mscrtype in ('Escalation')
and T1.mproduct like '%EmailXaminer%'
and (T6.name not in ('Closed','Duplicate','Resolved') or T1.state = 0)))

group by T1.dbid,T1.id,T2.login_name,T3.callid,T3.companyid,T4.login_name,T43.login_name,T1.headline,
T1.severity,T1.priority,T1.submit_date,T5.action_timestamp


order by T1.dbid ASC, max(T5.action_timestamp) DESC


Any ideas?
thanks in advance..
j

 

[PHV]

Remove ,T5.action_timestamp from the group by clause.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[LGTOserv]

EXCELLENT..!!!

Thank you very much.. i would never have done that as everytime i dont have all the fields from the select in the group by clause i get an error stating that.... hmm now i'm really confused..

thanks again.. worked great

 

[PHV]

In the GROUP BY clause you must have all the fields from the SELECT list not involved in an aggregate function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[rac2]

Dont mean to be rude but it seems unlikely to me that this ever worked. Here is why I think that. Generally speaking a timestamp is a unique value, because few things happen at the same moment in computer applications. Of course it can happen, and that would depend on how fine-grained the timestamp, but if it is simply the system time, then it is probably measured down to the millisecond.

Why should that matter? Because the timestamp is one of the GROUP BY categories. This means that you should get a row for each different timestamp value, and unless a lot of things are being recorded in your application, thousands per second, it is unlikely that any rows will be combined. But maybe that is what you require?

And I offer a minor obsevation that the DISTINCT keyword does nothing in a GROUP BY query, the rows are going to be all different, it is the nature of the GROUP BY. Note that the DISTINCT keyword applies to the whole row, not just to the column it preceeds.

I would approach this by writing a simplified query that uses only the columns that I want to use in the breakdown of MAX(T5.action_timestamp) values, usually these colums are the ones with a few values that occur in many rows. "Breakdown" and "rollup" are closely related to "GROUP BY". For example, many things happen on the same date, submit_date, with the same severity, and the same priority. The aggregate function, MAX(T5.action_timestamp), would give the timestamp of the last thing that happened that date, for each combination of priority and severity levels.

Forgive me if I belabor the obvious.

 

[LGTOserv]

sounds interesting.. but simply PHV's solution works just too perfect to alter... the query was generated by IBM's Clearquest software from *many* tables.. so i would assume that it is already the most efficient way to draw the data. The small alteration is all that i required from PHV...

thanks anyway.. i think the obvious would have belabored

 

[JarlH]

Just a comment regarding timestamps. The ANSI/ISO SQL data type is TIMESTAMP(s), where the seconds precision s is optional. (Default: s=6)

Example of duplicated timestamp values:

SQL>create table ts (c1 timestamp(0));
SQL>insert into ts values (current_timestamp);
SQL>insert into ts values (current_timestamp);
SQL>insert into ts values (current_timestamp);
SQL>insert into ts values (current_timestamp);
SQL>insert into ts values (current_timestamp);
SQL>select * from ts;
C1
===================
2004-12-14 14:10:05
2004-12-14 14:10:05
2004-12-14 14:10:06
2004-12-14 14:10:06
2004-12-14 14:10:06

5 rows found


As you can see above, several rows have the same timestamp value.