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 |
|
codism
Starting Member
11 Posts |
 Posted - 2007-02-06 : 17:57:06
|
| Hello, everyone. I have a query to return all customers along with some information about their last purchase:SELECT CustomerTable.CustomerName, (SELECT TOP 1 PurchaseTable.Date FROM PurchaseTable WHERE PurchaseTable.CustomerID=CustomerTable.CustomerID ORDER BY PurchaseTable.Date DESC), (SELECT TOP 1 PurchaseTable.Amount FROM PurchaseTable WHERE PurchaseTable.CustomerID=CustomerTable.CustomerID ORDER BY PurchaseTable.Date DESC)FROM CustomerTableI hope the query described related tables and it's purpose clear enough;-)As you see, I do need more than one field from the exact same subquery. To avoid selecting and sorting twice on the same data, I write my query in this way:SELECT CustomerTable.CustomerName, (SELECT TOP 1 PurchaseTable.Date, PurchaseTable.Amount FROM PurchaseTable WHERE PurchaseTable.CustomerID=CustomerTable.CustomerID ORDER BY PurchaseTable.Date DESC)FROM CustomerTableSql server returns error:"Only one expression can be specified in the select list when the subquery is not introduced with EXISTS"Can any one give me a hint on how to avoid redundant selecting and sorting in my query?Thanks in advance! |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-02-06 : 18:16:34
|
| If date is unique for customerSELECTc.CustomerName,p.Date ,p.AmountFROMCustomerTable cleft join PurchaseTable pon p.CustomerID = c.CustomerIDand p.Date = (select max(p2.Date) from PurchaseTable p2 where p2.CustomerID = p.CustomerID)==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
codism
Starting Member
11 Posts |
Posted - 2007-02-07 : 11:36:37
|
| That did the trick! Thanks a lot! |
|
|
|
|
|
|
how to pick up more than one field from a subquery