ultranix

Record Number in sequence

Discussion created by ultranix on Jun 21, 2016
Latest reply on Jun 22, 2016 by dtcgnet

I have a database, where I store daily stock market data. Main fields, that differentiate records are DATE and SYMBOL. I also have open, high, low, close and volume data, but those numbers are of secondary importance.

 

Like here:

  

DateSymbolOpenHighLowCloseVolumeRecord ID
2004-01-02AXP48,4348,5547,7347,7530415001
2004-01-05AXP48,0848,3347,8248,1742294002
2004-01-06AXP47,9548,3347,9348,2541389003
2004-01-07AXP48,4448,4448,0848,3944806004
2004-01-08AXP48,4848,7347,8048,2563954005
2004-01-09AXP47,8548,3047,8047,8945290006
2004-01-12AXP47,8948,3047,5947,7057397007
2004-01-13AXP47,8048,0047,4347,8741845008
2004-01-14AXP47,9548,9047,9548,7560772009
2004-01-15AXP48,9049,7548,6549,68794260010

 

Record ID field gives me the most of trouble. I want to have as auto-enter serial number, but I have all of the symbols in one database, so if next bunch of data is not from AXP, but, i.e. from MSFT, that should have Record ID's of 1, 2, 3, 4, 5, 6, 7, 8, 9 and 10, and not 11, 12, 13, 14, 15, etc.

 

Get (RecordNumber) also will not work, as it gets the number of the record in database without filtering of unique symbol.

 

So how do I get correct numbering of particular symbol in the database? I sort those by date in ascending order, so #1 is the oldest date and the most recent date has the highest Record ID.

Outcomes