How can I make this sql statement better?? 1

[rtgordon]

I am referring to the WHERE clause where I repeat the select max incident_num. Can I store it in a variable, and reference in the second part of the where clause?

Thanks,
gordon

SELECT 
	*
FROM 
	irsc_incident_dtl T1, 
	irsc_sts T2, 
	irsc_usr_tbl T3, 
	irsc_cat_desc T4
WHERE 
	T1.incident_num=(select max(incident_num) FROM irsc_incident_dtl) 
	and T2.incident_num=(select max(incident_num) FROM irsc_incident_dtl)
	and T2.lst_upd_date=(select max(lst_upd_date) FROM irsc_sts)
	and T1.cat_cd = "OP"

 

[TomasDill]

Hi!


SELECT
*
FROM
irsc_incident_dtl T1,
irsc_sts T2,
irsc_usr_tbl T3,
irsc_cat_desc T4
WHERE
T1.incident_num=(select max(incident_num) FROM irsc_incident_dtl)
and T2.incident_num=T1.incident_num
and T2.lst_upd_date=(select max(lst_upd_date) FROM irsc_sts)
and T1.cat_cd = "OP"

However, I wondered, how long this query runs from the joind point of view? I guess this query will runn for a looooooong time because no conditions specified to join T1+T2 tables with T3 and T4, so you have a FULL JOIN. Start to optimize query from this point of view. Selecting max value is very quick if that table indexes by the field from which you calculate a max() value.

Vlad Grynchyshyn
vgryn@softserve.lviv.ua

http://www.softservecom.com

The professional level of programmer could be determined by level of stupidity of his/her bugs

 

[rtgordon]

Thanks, I will try that. I am sure that I don't have all of my joins in properly, that is what I wrote from memory, and I was simply concerned with the syntax of the select max portion.

Thanks,
gordon