Problem | Max once saw a post, is asked how once (with a query statement) on the query out of a table of the maximum and minimum values, one of these answers: (Take the Northwind Products table for example)
Select top 1 * from the products order by UnitPrice
Union
Select top 1 * from the products UnitPrice DESC
This seems to be the correct one, but in fact, when you use the Union, only the last select command can use the order by parameter, so this is not the case, running in Query Analyzer will burst the error
The following methods provide the maximum and minimum values for the query:
DECLARE @HighLow table
(
ProductName varchar (50)
)
Insert @HighLow Select top 1 Productname from the ORDER by UnitPrice DESC
Insert @HighLow Select top 1 Productname from [ORDER by UnitPrice
Select ProductName from @HighLow
Instead of querying the maximum and minimum values at once, this method uses a table variable to save the maximum and minimum values of the query into the table.
The following example uses the Northwind database to take out the 3 most expensive books in each bibliography:
DECLARE @Category table
(
ID int identity (1,1) not NULL,
CategoryID int,
CategoryName varchar (50)
)
DECLARE @MostExpensive table
(
ProductName varchar (50)
)
declare @counter int, @number int
Set @counter =0
Insert @Category Select Categoryid,categoryname from Categories
Select @number =count (*) from @Category
While @counter < @number
Begin
Set @counter = @counter +1
Insert @MostExpensive Select top 3 ProductName from Products where categoryid= (select CategoryID from @Category where id=@ Counter) Order BY UnitPrice Desc
End
SELECT * FROM @MostExpensive
