Is there a better way to find the high-waterline values in a column in SQL?
Assume there is a table with a float column and a CSV with header with the same name and the name numbers below it.
CsvPath Validation Language can find the new high value lines using a one-line csvpath.
I want to make the best apples-to-apples comparison of the SQL way to the csvpath one-liner. This works, but could there be a better way?
~ This csvpath finds all the new high-value thresholds in a CSV file. It gives the same output as this SQL: SELECT id, worker_hours_this_period FROM( SELECT id, worker_hours_this_period, MAX(worker_hours_this_period) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS max_prev_value FROM projects ) AS subquery WHERE worker_hours_this_period > max_prev_value OR max_prev_value IS NULL; id: New high value csvpath test-data: examples/headers/projects_with_reset.csv ~ $[1*][ @hours.onchange.increase = float(#worker_hours_this_period) print.onmatch("line: $.csvpath.line_number: $.variables.hours") ] 
Top comments (0)