BOR15K (4/24/2015)
Without the DDL for the table(s) and sample data, how do you expect us to help with the code you need? We can't see what you unless you give us the DDL, sample data, and expected results. Plus, for the effort you put in to post this information, you will get back tested code.
I see you haven't ran my script, otherwise would get a working result.
Since when people required to publish DDL scripts here? Should I go through and show multiple examples of WITH AS,
which returned the list of data and it sufficed? A simple SELECT * FROM flight_details of mine WITH flight_details AS () returns a valid result.
You don't want to help? That is OK, you don't have to, but please do not start throw things over one which you don't do over another.
Expected results? I am sorry, but I have clearly stated what is the result I want as well - the last line, with a mileage of 3171.
Sean and Lynn, along with almost everyone else, requests DDL, sample data and expected results. This is nothing new. We need to know what you have, what you want and the rules for getting to what you want. We can then figure out the best route for getting from what you have to what you want. Without knowing these three basic pieces of information, there's really no way to provide concrete help. Without this information, anything we do manage to give you will be completely untested, as we have nothing to test against. I've seen people take stabs in the dark before with very limited information. Sometimes, they come up with the answer the OP was looking for, but they're guesses. To provide anything more than a guess, we need the basics.
For example, you state above that you want the last row with the mileage of 3171. Okay, so select your data using whatever order by clause you feel is appropriate and then select the appropriate row from the set. But that's not the answer you're after. In order to give you the answer I THINK you want, I would need to create a table, populate it with some data and then write queries against it to see if I can figure out how to produce your desired results. Without the basics, there's no foundation built on which to work. There's nowhere to start.
And pulling an attitude with good people like Sean and Lynn is certainly no way to solicit help.
Instead, you'll probably want to start here: https://msdn.microsoft.com/en-us/library/hh231256%28v=sql.110%29.aspx
Also, if you don't have it, I would recommend Itzik's book on window functions: http://www.amazon.com/Microsoft-High-Performance-Functions-Developer-Reference/dp/0735658366