Find the Most Recent of Consecutive Dates

[robsmith]

Hi,

I have a table of horses that have run on consecutive days (extract below). I would like to run a query to return the record of the second of the consecutive days. I have tried using Max() but sometimes a horse has run on consecutive days on multiple occasions so it just returns the very latest record.

Is it possible to do this?

Thanks

|EVENT_DT |SELECTION_NAME |WIN_LOSE|BSP |PPWAP |MORNINGWAP |MORNINGTRADEDVOL|PPTRADEDVOL|RankByPPVol.Rank|RankByBSP.Rank|RankByMornVol.Rank|
|----------|---------------|--------|-----------|-----------|-----------|----------------|-----------|----------------|--------------|------------------|
|01/01/2015|Jonnie Skull |0 |16 |10.68291002|9.807729117|353.88 |16805.98 |6 |6 |6 |
|02/01/2015|Jonnie Skull |0 |21.57408997|21.98415495|14.49689739|354.58 |29349.4 |6 |7 |5 |
|15/01/2015|Arrayan |0 |15 |20.23567871|17.93733216|99.26 |22557.66 |7 |7 |7 |
|16/01/2015|Arrayan |0 |184.3723085|193.9580134|161.3214542|106.16 |9232.1 |3 |3 |3 |
|23/01/2015|Burnt Cream |0 |6.8 |5.927023848|4.686454969|2743.04 |169159.8 |2 |3 |2 |
|24/01/2015|Burnt Cream |0 |31.25596061|21.96212766|20.18012532|700.36 |10737.74 |11 |10 |8 |
|05/02/2015|Etaad |1 |4.9 |4.519037247|7.42556455 |2514.74 |203153.5 |2 |2 |2 |
|05/02/2015|Waterloo Dock |0 |9.8 |7.961858988|7.895361275|1155.52 |52870.6 |3 |4 |1 |
|06/02/2015|Etaad |0 |3.837935094|3.737524372|5.039030618|3507.06 |249861.34 |1 |1 |3 |
|06/02/2015|Waterloo Dock |0 |14.45134541|13.95022023|15.01439283|261.46 |21187.86 |6 |6 |8 |
|10/02/2015|Crockett |0 |4.713154225|4.843584394|6.892763232|1024.22 |103602.44 |2 |3 |1 |
|11/02/2015|Crockett |0 |6.4 |6.046984493|4.085887873|347.4 |53270.28 |4 |4 |4 |
|17/02/2015|More Drama |0 |5.7 |6.173083577|7.125849727|440.98 |125848.9 |2 |2 |3 |
|18/02/2015|More Drama |0 |24 |22.33647011|13.33738992|302.78 |34440.56 |3 |3 |3 |
|22/02/2015|Sirop De Menthe|0 |3.3 |3.285927318|3.54200376 |1111.86 |155561.82 |2 |2 |2 |
|23/02/2015|Sirop De Menthe|0 |2.1 |2.223773125|3.106702019|7138.88 |534756.44 |1 |1 |1 |
|26/02/2015|Norse Light |1 |9.240800217|7.861302504|7.215787256|176.84 |29841.18 |4 |4 |5 |
|27/02/2015|Norse Light |0 |10 |9.530335433|8.545462046|1971.72 |34637.36 |3 |2 |3 |

 

[arnelgp]

if you add an Autonumber field on your table, you can determine the "last" record
by using max() on the autonumber.

 

[robsmith]

Hi Arnelgp, I don't understand how that will help? Thanks

 

[arnelgp]

ok, something like this:

SELECT YourTableName.*
FROM YourTableName INNER JOIN YourTableName AS data_1 ON (YourTableName.selection_name = data_1.selection_name) AND (YourTableName.event_dt = data_1.event_dt+1);

 

[plog]

Your data doesn't demonstrate your issue, please provide better data. Not one of those horses has multiple consecutive day entries.

I need 2 sets of data:

A. Starting data from your table. Include table and field names and enough data to cover all cases.

B. Expected results of A. Show me what data you expect your query to return when you feed it the data from A.

Again, 2 sets of data that demonstrate the issue.