Hi,

I have a query to get the latest record in a table as follows:

SELECT DISTINCT max(lcns_startdt) as lcns_startdt, max(appt_stopdt) as appt_stopdt, max(appt_startdt) as appt_startdt, agent_tax_id, co_id, state_id, lob_type
FROM license
GROUP BY agent_tax_id, co_id, state_id, lob_type
HAVING lcns_startdt = max(lcns_startdt) AND
appt_stopdt = max(appt_stopdt) AND
appt_startdt = max(appt_startdt)

Now the user wants to redefine a latest record. They want to first look at the latest appt_startdt field. If there is only one record for agent_tax_id, co_id, state_id, lob_type with the lastest appt_startdt then use that record. If there are two or more records using the latest appt_start_dt then look at the appt_stopdt. If there are two or more records that have the same appt_startdt and appt_stopdt then get the latest lcns_startdt. I am not sure how to code this in SQL. Can someone help me out?


Thanks,

Mark

you're not going to like the ANSI SQL solution (the forum you posted in) because it probably won't work in your database system

what database system are you using?

I am using Sybase 11.9.2