OSEC

Neohapsis is currently accepting applications for employment. For more information, please visit our website www.neohapsis.com or email hr@neohapsis.com
Re: ORDER BY Help

From: Olexandr Melnyk (omelnykgmail.com)
Date: Fri Oct 24 2008 - 08:28:09 CDT

SELECT ProductID,
  ProductName,
  AVG(ProductScore * Quantity) AS a
FROM Products
GROUP BY ProductID
ORDER BY a DESC

2008/10/24, Tompkins Neil <neil.tompkinsgooglemail.com>:
>
> Following on from my email below I now need help with the following
> problem. Here is a list of my sample data
>
> Date ProductID ProductName ProductScore
> Quantity
> 2008-11-10 100 Red Light
> 0.05 10
> 2008-11-11 100 Red Light
> 0.05 2
> 2008-11-12 100 Red Light
> 0.05 0
> 2008-11-10 150 Blue Light
> 0.01 5
> 2008-11-11 150 Blue Light
> 0.01 5
> 2008-11-12 150 Blue Light
> 0.01 5
> 2008-11-10 160 Green Light
> 0.05 5
> 2008-11-11 160 Green Light
> 0.06 5
> 2008-11-12 160 Green Light
> 0.11 5
>
> I need to list this data in the order of the the product with the highest
> quantity, followed by ProductScore. Am I able to calculate a quantity
> percentage, based on the number of records for say Red Light.
>
> Thanks,
> Neil
>
>
>
>
> On Fri, Oct 24, 2008 at 1:32 PM, Tompkins Neil <
> neil.tompkinsgooglemail.com> wrote:
>
>> Thanks for the reply, this is exactly what I wanted.
>>
>> Cheers Olexandr !
>>
>> On Fri, Oct 24, 2008 at 1:26 PM, Olexandr Melnyk <omelnykgmail.com>wrote:
>>
>>> SELECT ProductName FROM Products
>>> WHERE ProductScore > 100
>>> ORDER BY CASE WHEN ProductScore = 125
>>> THEN 0
>>> ELSE 1
>>> END, ProductScore
>>>
>>> But this query won't use an index, so it would be a good idea to do this
>>> in two queries
>>> 2008/10/24 Tompkins Neil <neil.tompkinsgooglemail.com>
>>>
>>>> Hi
>>>>
>>>> I've the following basic query like
>>>>
>>>> SELECT ProductName FROM Products
>>>> WHERE ProductScore > 100
>>>> ORDER BY ProductScore
>>>>
>>>> However, how can I order by ProductScore, but ensure the product with ID
>>>> 125
>>>> is at the top ? Is this possible.
>>>>
>>>> Thanks
>>>> Neil
>>>>
>>>
>>>
>>>
>>> --
>>> Sincerely yours,
>>> Olexandr Melnyk
>>> http://omelnyk.net/
>>>
>>
>>
>

--
Sincerely yours,
Olexandr Melnyk
http://omelnyk.net/