Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
nhancock99
Starting Member
1 Post |
 Posted - 2010-09-05 : 23:38:47
|
| Hi all,I need to write a SELECT embedded in a bigger SELECT, but I don't want to repeat it [which I think I have to do as I will be retrieving two values]:SELECT.......max_bid = (SELECT TOP 1 amount from previous_auction WHERE .... order by amount desc date desc),previous_auction_date = SELECT date from previous_auction WHERE item_no = previous_auction.item_no AND amount = (SELECT TOP 1 amount from previous_auction WHERE ... order by amount desc date desc),......FROM auction, itemWHERE ...etc etcI don't see that I can include the table previous_auction in the main FROM/WHERE as I will be retrieving multiple rows, and don't want to put a SELECT TOP 1 into the mix.Is there any way to combine the two embedded SELECTs to get the values for max_bid and previous_auction_date from the one [sub] SELECT command?Thanks,Nigel |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2010-09-06 : 00:31:44
|
in short, you can make use of CTE for the "SELECT TOP 1 amount FROM . .. "but looking at the incomplete query, there might be a better way to write it rather than using sub-query. If you can post the full query, i am sure, somebody will give you an alternate way to do it.
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-09-06 : 05:05:50
|
| It seems you need to use derived table approachselect max_bid, ... from( SELECT.......max_bid = (SELECT TOP 1 amount from previous_auction WHERE .... order by amount desc date desc),...) as tMadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|
|
Repeating nested SELECTs