Find the Most Recent of Consecutive Dates (1 Viewer)

robsmith

New member
Local time
Today, 07:28
Joined
Feb 17, 2020
Messages
26
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

..forever waiting... waiting for jellybean!
Local time
Today, 14:28
Joined
May 7, 2009
Messages
16,394
if you add an Autonumber field on your table, you can determine the "last" record
by using max() on the autonumber.
 

robsmith

New member
Local time
Today, 07:28
Joined
Feb 17, 2020
Messages
26
Hi Arnelgp, I don't understand how that will help? Thanks
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:28
Joined
May 7, 2009
Messages
16,394
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

Banishment Pending
Local time
Today, 01:28
Joined
May 11, 2011
Messages
10,916
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.
 

Users who are viewing this thread

Top Bottom