Select Mulitple Records based on One record's column value

Question

I have a table which contains related records (multiple revisions of the same record). Each record has a string field that resembles a date (date, time, and microtime). I want to select all records that are older than a specific date. If a record has a related record newer than the specific date, I do not want to select any of those related records. Any ideas for that select statement? Eventually, it will be a REMOVE statement.

Edit: Some Sample Rows

id    shared_id    date                         type      other_data...
1     2            2010-01-01 01:02:03.1234567  original  ...
2     3            2010-01-15 11:12:03.1234733  original  ...
3     2            2010-02-01 03:04:04.5465654  amendment ...

If my cut-off date was "2010-01-31", I would want to select id #2 only because id #1 has an amendment newer than the cut-off date.

Answer 1

Maybe you can try the DATEDIFF() function, check this out: Link 1

Or this one: Link 2

Or maybe you can try the classic query (SELECT FROM table WHERE data <= anotherdata), but in this case you need to convert both data in timestamp format

Answer 2

DELETE from tablename where relatedField = 'relatedValue' AND dateField <= dateToDeleteFrom

Something along those lines should do what you need it to do, if I understand your scenario. If you provide a sample data set I can adjust the statement to more accurately represent your need, but I think this is a good starting point.

HTH, Jc

Answer 3

I found this link helping me generate the select statement.

SELECT DISTINCT T.shared_id,T.date,T.id  
FROM table T  WHERE T.date = ( 
SELECT MAX( date ) FROM table WHERE shared_id = T.shared_id )  
AND T.date < 'my_cut_off_date_string'

This seems to work for me. Thanks for everyone's help.