Skip to content
Advertisement

SQL multiple rows calculate

I have a table in SQL Server and I am trying to calculate if a number is bigger than a selected row, but only if the rows that exist are greater than 1.

I’ve looked online and looked into Union, Intersect and Exist but cannot see a way to do it and of course I may have to program the calculation in C++ but would rather do it in SQL as it will be faster.

The example is as follows: the table lists a number of horse races and data (needs to sorted by date and time in the selection):

In the above data I wouldn’t be interested in the last record as only one for that date and time exists. From the other four races I would like to calculate how many races that have a ‘W’, have a ‘OF’ value greater than the ‘LC’ line. The output would count how many races have this condition.

A further complication is there may be more than two records for that date and time. I’ve looked at this for two days now without success so just before I do program it I thought I’d ask.

SG

Advertisement

Answer

Based on your sample data, the following should do the trick…

User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement