0

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

Joe

Joe
  • 1
  • Can you give an example of your data and the output you want to achieve in order to make the question a little clearer? – DoctorMick Aug 02 '11 at 10:44
  • Max date for each status or the most recent date of as status change? – Karl Aug 02 '11 at 10:46
  • 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 – Joe Aug 02 '11 at 10:55
  • What database are you using Joe ? – Kevin Burton Aug 02 '11 at 19:20

1 Answers1

0

I would select all the rows sorted by create date:

SELECT status, created
FROM t      
WHERE  tid = 1 
ORDER BY created

Then filter (in the language of your choice) dropping any row that has a row after it of the same status.

However this will not quite work for you. Your data only contains a date column and as there are many status on a given date so there will be no order for a several statuses on a single day, you can solve this by storing the date/time created.

Depending on your database it may also be possible to produce the data using an analytic query.

Kevin Burton
  • 11,676
  • 2
  • 24
  • 37