SQL Query help?

[ninefoureight]

I'm trying to create a query which will extract the chronological improvement in my data, i have used nested queries before to get a list of the bests (best being the smallest time duration) per category but this is stumping me. I am thinking i need set a variable to store the first matching value but thereafter im not really sure of my mechanism to list all the results - can anyone either point me in the direction of a good example (my googlefu is failing..) or help here please?

Example data
Date Duration
1/8/1999 28.99 seconds
7/12/2001 39.99 seconds
1/3/2012 24.54 seconds
1/3/2013 23.45 seconds
1/4/2013 25.35 seconds

Example result in rank
Date Duration
1/8/1999 28.99 seconds
1/3/2012 24.54 seconds
1/3/2013 23.45 seconds

Thanks in advance!

 

[ninefoureight]

Yes they are two fields. However what I am looking for is a list of results where the previous best has been lowered. I will be grouping by day. So there could be forty occasions where the best has improved in a calendar year in none in another.

Thank you for your other quickly reply with the query though!

 

[ninefoureight]

No sorry (probably why I haven't been able to Google for answer, my description is poor! )

I want to return only the the values which better the oldest best I'll call this O. Then when the best NEW1 betters OLD then the next value returned is the next better value NEW2 than the new best (NEW2 < NEW1). There can be values in chronological order in the data between these values which will not be returned because they don't improve the last.

If not clear still I'll post a bigger dataset and desired result in the morning. Thanks for your help and patience!

 

[ninefoureight]

I think the key word is the progression of the improvement over all time. In a time line fashion. So from this data:

Value, Date.

12 01/12/2013
12 01/12/2013
12 02/12/2013
12 02/12/2013
12 03/12/2013
12 04/12/2013
13 04/12/2013
13 05/12/2013
13 07/12/2013
12 08/12/2013
12 09/12/2013
12 10/12/2013
12 11/12/2013
14 12/12/2013
12 13/12/2013
13 14/12/2013
12 14/12/2013
13 15/12/2013
14 16/12/2013

I would want to return (..assuming my data was already in a chronological order, but lets not complicate things further just now! ;])

12 01/12/2013
13 04/12/2013
14 12/12/2013

The lowest value and earliest date. The next instance where it is bettered and finally the last in this dataset where it is improved.

Does this assist further?