in my table one of column is Status and Date if suppose i want to get max(date) for each state then i can use group by of date But here my problem is i want to get max(date) for each transaction NOT FOR EACH STATUS that means, my status values like ,create / modify / modify / submit / reject / modify / submit / reject / modify / submit now i want to get each transaction along with max date like - create /(only one) modify / submit / reject / (again) modify /submit / reject / modify / Submit... Can any one please suggest me to find solution for the above. Thanks in advance Let me give clear example my table struct like- -------------------------- Status - Date ------------------------- Created - 01-08-2011 Modify - 02-08-2011 Modify - 03-08-2011 Modify - 04-08-2011 Submit - 04-08-2011 Reject - 05-08-2011 Modify - 05-08-2011 Modify - 06-08-2011 Submit - 07-08-2011 Reject - 07-08-2011 Modify - 07-08-2011 Modify - 08-08-2011 My Output should be ------------------------------ Status -Date ------------------------------ Created - 01-08-2011 Modify - 04-08-2011 Submit - 04-08-2011 Reject - 05-08-2011 Modify - 06-08-2011 Submit - 07-08-2011 Reject - 07-08-2011 Modify - 08-08-2011
Welcome to the forums. Did you try: (select id, max(date)fromtablegroupby id) or see this http://www.karaszi.com/SQLServer/info_datetime.asp link for leads.
It looks like there is some logic to the order in which states can be entered or how the transition from one state to another is handled. We would need to know these rules. I also think it would make sense to provide the table structure, because it seems there is more to it than just a striaght forward simple query.
