the logics of this are cooking my brains..

[cjard]

I have a table with 4 columns:


date_created, date_effective, person_id, credit_limit



i need to find the max(date_created) together with the max(date_effective) for each person_id, and the credit_limit related to that dates and person

hence we look for the max(date_created) when grouped by person_id, linked to the max(date_effective) for this combination, then we find the credit limit for this combination of 3..

and im finding it really hard work without self-joining the table. any tips?


dateC, dateE, person, credit
1,1,jim,1000
1,2,jim,900
1,3,joe,1500
2,2,sue,1200
3,3,sally,500
4,4,bob,1000
4,5,bob,1500


obviously, if i just group by the person id, i get one person id, one date_created and one date_effective

but i dont want to have to self-join that resultset to the table to pull out the client limit for those dates. is there anything i can do?

[Klymer]

why don't you want self-joins on your table? (you're talking about select... from table as t1 inner join table as t2 on... i presume?)

otherwise you could try a correlated subselect

[cjard]

because the sql is 6 pages long already; complicated enough, with multiple nested subselects; ive noticed a significant performance improvement when not using self-joins (i dont know why) but in the end this was what i had to do

thanks for the advice

[Klymer]

okay, if your select is already this big... yes, subselects are significantly faster. but perhaps it would be easier for you to create a view from your select, thus simplifying your -new- select.
good luck

[Krzemo]

because the sql is 6 pages long already; complicated enough, with multiple nested subselects;Maybe this could help (Ms SQL Server example):

SELECT t.person,t.credit
FROM MyTable t
INNER JOIN (
SELECT MAX(dateC) MdateC, MAX(dateE) MdateE, person
FROM MyTable
GROUP BY person
) m ON m.MdateC=t.dateC AND m.MdateE=t.dateE and m.person=t.person

It should be fast enough - if U create good indexes on MyTable

Best regards,
Krzemo.