You could simply run your query then use your programming language's
facilities to scroll down the result set looking for the item in question.
When you found the right record, you would know what it's ranking was
based on how many rows of your results you had to scan through before you
found it.
If you want MySQL to autoassign numbers to rows you need to create an
autoincrementing integer field and enough other fields on a temporary( or
privately-named) table to store the results you want ranked. Let's assume
that you would like to analyze raw sales ranked in descending volume order
CREATE TEMPORARY TABLE tmpRankings (
rank int auto_increment
, PartNumber varchar(20)
, Quantity int
, primary key(PartNumber)
, key(rank)
, key(Quantity)
)
INSERT tmpRankings (PartNumber, Quantity)
SELECT part_number, quantity
FROM sales_data_table
ORDER BY quantity DESC
# this query will tell you the rank for a particular part
SELECT PartNumber, rank
FROM tmpRankings
WHERE PartNumber = 'part_in_question'
#this query will give you the 5th through 15th top selling products
SELECT rank, PartNumber, Quantity
FROM tmpRankings
WHERE rank BETWEEN 5 and 15
#this will give you the sales ranking of each product in a list of
products
SELECT rank, PartNumber, Quantity
FROM tmpRankings
WHERE PartNumber IN ('item1', 'item2', 'item3','item4')
DROP TABLE tmpRankings
This creates a ranking table. Because the first column is
autoincrementing, we don't fill it with data. The autoinc column becomes
the sales ranking because we added the raw sales data to the table in
order of descending sales volume. Select from the ranking table the
product(s) you are interested in then clean up after ourselves. This table
could take up some considerable room as I indexed all 3 fields on it for
the purpose of analysis speed. You could try it without the indices and
see but I think it would be rather slow.
It's not all in one step but it's usually pretty quick and very useful.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"C.F. Scheidecker Antunes" <[EMAIL PROTECTED]> wrote on 08/23/2004
07:50:02 PM:
> Hello All,
>
> I have a table that has a PartNumber and the Quantity of Items sold for
> each partNumber, that is:
>
> PartNumber
> Qty
>
> I need to get its sales raking given its PartNumber, that is. So if I
> order the table by Qyt in descending order the first
> record will be the partNumber that sold the most. If I want to know what
> is 123 raking position according to that.
>
> Is there any easy way to do it?
>
> Thanks in advance.
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
