|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Tenth Costliest Product!The following query retrieves the tenth costliest product from a table:
---------------------------------------------- SELECT MAX(Price) FROM Products P1 WHERE 9=(SELECT COUNT(*) FROM Products P2 WHERE P1.Price>P2.Price) ---------------------------------------------- Can anyone explain me the logic behind the above query i.e. how does it correctly retrieve the tenth costliest product? Thanks, Arpan >> Can anyone explain me the logic behind the above query i.e. how does it The second column in the resultset of the following query is a rank based on >> correctly retrieve the tenth costliest product? the price. SELECT P1.Price, ( SELECT COUNT( * ) FROM Products P2 WHERE P1.Price > P2.Price ) FROM Products P1 ; All you are doing in your query is simply equating it to a particular value in your WHERE clause. -- Anith Thanks, Anith, but sorry to say that I couldn't exactly follow your
explanation. Can you please explain me what do the 2 queries (in the example you have given) do one-by-one i.e. what does the sub-query first do (since, being the sub-query, it will be executed first though I guess it cannot be resolved independently of the outer query) & then how does the outer query make use of the resultset of the sub-query to generate the final resultset? Thanks once again, Regards, Arpan > Thanks, Anith, but sorry to say that I couldn't exactly follow your They don't work independently. The sub-query is correlated by price, and is > explanation. Can you please explain me what do the 2 queries (in the > example you have given) do one-by-one part of the outer query (but runs for each price in P1). Hi Anith,
I think you mean this SELECT P1.Price, ( SELECT COUNT( distinct P2.Price ) FROM Products P2 WHERE P1.Price > P2.Price ) FROM Products P1 ; The following query works fine but it give wrong value in case the Price is repeated SELECT P1.Price, ( SELECT COUNT( * ) FROM Products P2 WHERE P1.Price > P2.Price ) FROM Products P1 ; With warm regards Jatinder Anith Sen wrote: Show quote > >> Can anyone explain me the logic behind the above query i.e. how does it > >> correctly retrieve the tenth costliest product? > > The second column in the resultset of the following query is a rank based on > the price. > > SELECT P1.Price, > ( SELECT COUNT( * ) > FROM Products P2 > WHERE P1.Price > P2.Price ) > FROM Products P1 ; > > All you are doing in your query is simply equating it to a particular value > in your WHERE clause. > > -- > Anith Arpan wrote on 21 Jul 2005 21:01:09 -0700:
> The following query retrieves the tenth costliest product from a table: The subqueries counts how many products are more expensive than the row in > > ---------------------------------------------- > SELECT MAX(Price) FROM Products P1 WHERE 9=(SELECT COUNT(*) FROM > Products P2 WHERE P1.Price>P2.Price) > ---------------------------------------------- > > Can anyone explain me the logic behind the above query i.e. how does it > correctly retrieve the tenth costliest product? P1 being compared. When this equals 9, there are 9 more costly products than the row in P1 - therefore the row in P1 is the 10th costliest product. The MAX is used to bring back just one value - there could be multiple rows with the same price at the equal 10th position. Dan On Fri, 22 Jul 2005 09:01:41 +0100, Daniel Crichton wrote:
Show quote > Arpan wrote on 21 Jul 2005 21:01:09 -0700: Unfortunately, there could also be ZERO rows at the equal 10th position.> >> The following query retrieves the tenth costliest product from a table: >> >> ---------------------------------------------- >> SELECT MAX(Price) FROM Products P1 WHERE 9=(SELECT COUNT(*) FROM >> Products P2 WHERE P1.Price>P2.Price) >> ---------------------------------------------- >> >> Can anyone explain me the logic behind the above query i.e. how does it >> correctly retrieve the tenth costliest product? > > The subqueries counts how many products are more expensive than the row in > P1 being compared. When this equals 9, there are 9 more costly products than > the row in P1 - therefore the row in P1 is the 10th costliest product. The > MAX is used to bring back just one value - there could be multiple rows with > the same price at the equal 10th position. > > Dan Consider the following list: $10 - 0 more expensive products $9 - 1 more expensive product $8 - 2 more expensive products $7 - 3 more expensive products $6 - 4 more expensive products $5 - 5 more expensive products $5 - 5 more expensive products $5 - 5 more expensive products $5 - 5 more expensive products $5 - 5 more expensive products $4 - 5 more expensive products $3 - 10 more expensive products Below 2 versions of a query that also return the 9th priciest (is that a
word?) product. They are more simple to read and may (depending on your record volume and indexing) prove to return the result faster. Let's assume the following Prices: 120,110,89,70,67,60,55,50,12,7,5,3,2 In the example below, the sub-query returns the top 9 Prices in descending order, and the outer query takes the minimum Price from that result. From the above list, it would return 55. select min(Price) from ( select top 9 Price from Products order by Price desc ) as x Here, the (distinct) clause removes duplicates from the sub-query, so from the above list, it would return 12. select min(Price) from ( select distinct top 9 Price from Products order by Price desc ) as x JT Show quote "Arpan" <arpan***@hotmail.com> wrote in message news:1122004869.696764.3450@g43g2000cwa.googlegroups.com... > The following query retrieves the tenth costliest product from a table: > > ---------------------------------------------- > SELECT MAX(Price) FROM Products P1 WHERE 9=(SELECT COUNT(*) FROM > Products P2 WHERE P1.Price>P2.Price) > ---------------------------------------------- > > Can anyone explain me the logic behind the above query i.e. how does it > correctly retrieve the tenth costliest product? > > Thanks, > > Arpan >  |
|||||||||||||||||||||||
Other interesting topics