|
|
Posted by Hilarion on 07/18/05 20:41
Jim S. wrote:
> ok, am not sure that that query does what i need, since ranking is a
> variable, for example:
> category , a, b, c
> under category each category, there is a list of products, each having
> its own number (ranking) , now that number could be 5, 1000, 1 or any
>
> so what i want is the "TOP 3" numbers (ranking) of each categoy.
> thanks.
> Note: I never used "between" before, i will check it out, and hopefully it
> can be tweaked to fit the purpose. thanks again, and i hope u reply back.
Expression "x BETWEEN y AND z" is equal to "(x >= y) AND (x <= z)" so it
will not work in your case.
I'm not sure if I understood what is your table structure so my example
below is based on simple table "ranks" containing two columns: "category"
and "rank". Where "category" points to a category and "rank" has a number
which shows it's score/rank.
If the SQL engine you are using supports subqueries, then you may try this:
SELECT r1.category, r1.rank
FROM ranks r1
WHERE r1.rank IN (
SELECT TOP 3 r2.rank
FROM ranks r2
WHERE r1.category = r2.category
ORDER BY r2.rank DESC
)
ORDER BY r1.category, r1.rank
If you want categories with lowest "rank" values then change sort direction
in inner ORDER BY clause to ASC.
I did not test the above query, so I'm not sure if it'll work, but it looks
OK to me :)
Hilarion
Navigation:
[Reply to this message]
|