There must be a simple answer to this but I am struggling to find it!Suppose I have a table with fields DateTime, BidPrice, AskpriceI'd like to retrieve the best (highest) bid and the best (lowest) ask and the times at which these occur. Can I do it with one SQL statement?It's easy with two i.e.SELECT TOP 1 DateTime, BidORDER BY [Bid] ASCSELECT TOP 1 DateTime, AskORDER BY [Ask] DESCBut this is obviously wasteful as a full scan of the table is required in each case.Any clever person have an answer?

I can't think of any other way.You can do a Select Max(Bid) From Blah Blah Also, you can throw a "Union" between the two statements so that you can get the results back in one shot but it still hits the table twice.A little lame but may be helpful.Terror.

Yeah, if you do Max(Bid) you get the best bid but not the time at which that occured (a crucial part of my overfitting!)I tried Union but you are only allowed one ORDER BY clause!

select Time,bidPrice,askprice from Tab where bidprice = (select Max(bidprice) from tab)unionselect Time,bidPrice,askprice from Tab where bidprice = (select Min(askprice) from tab);may this version will help u ?????

Russell - if you're using MS SQL Server you'll probably not beat saliq's query. Note there's a typo in the last line, should be: where askprice = (select Min(askprice) from tab);If you're worried about efficiency, just create 2 indexes, one keyed on bidprice and the other on askprice.

saliq - good reply. This works, although when there is a tie for best bid/ask you get all the ties. Would probably be interested in only the earliest one but it's fairly easy to generate a subquery. I wonder if internally the server just does one table scan?Athletico - thanks for the advice. Probably more conscious of the overhead going into the table than coming out, but you are dead right an index would be much faster.Just an academic question really to see if it was possible with only one query,thanks, all!

QuoteOriginally posted by: RussellI wonder if internally the server just does one table scan?No, I don't know of any server that would be smart enough to do this in one scan. I bet it does 4 scans - one to find the min in the subquery, one to find the max, one to match the min, and one to match the max.If you can use cursors, then you can write your own logic to do this with one full scan.

QuoteOriginally posted by: AlphaNumericusQuoteOriginally posted by: RussellI wonder if internally the server just does one table scan?No, I don't know of any server that would be smart enough to do this in one scan. I bet it does 4 scans - one to find the min in the subquery, one to find the max, one to match the min, and one to match the max.If you can use cursors, then you can write your own logic to do this with one full scan.But would the hassle involved in replacing one SQL statement with something cursor-based be worth it? And is it really faster than a SQL query on an appropriately indexed table?Also, I think saliq's version could be simplified toselect Time,bidPrice,askpricefrom Tabwhere bidprice = (select Max(bidprice) from tab) OR askprice = (select Min(askprice) from tab);

Interesting information about scans and using a cursor.I tried version (a) SELECT TOP 1 DateTime, BidORDER BY [Bid] ASCSELECT TOP 1 DateTime, AskORDER BY [Ask] DESCagainst (b):select Time,bidPrice,askpricefrom Tabwhere bidprice = (select Max(bidprice) from tab) OR askprice = (select Min(askprice) from tab); And was suprised to find that (b) is about 4 times faster than (a). I expected about 2x so it's quite a bonus.Finally I tried creating a cursor stored procedure but realised about half way through that for my purposes the Table (FROM clause) is dynamic and so I normally have to create the SQL on the fly rather than having a saved stored procedure.