Igor Tandetnik wrote:
>
> On 1/6/2011 5:54 PM, gasperhafner wrote:
>> Igor Tandetnik wrote:
>>>
>>> On 1/6/2011 5:25 PM, gasperhafner wrote:
>>>> Igor Tandetnik wrote:
>>>>>
>>>>> select ID_DISH, sum(ID_INGREDIENT not in (2, 4)) stillMissing
>>>>> from x
>>>>> group by ID_DISH
>>>>> having stillMissing != count(*)
>>>>> order by stillMissing;
>>>>
>>>> What about if a want sort by ingredients asc which i have?
>>>
>>> order by count(*) - stillMissing
>>
>> This doesn't work...
>>
>> I want ID_dishes order by count of ingredient which i have the most,
>
> What do you mean, have the most? I don't understand the problem
> statement. An example, perhaps?
> --
> Igor Tandetnik
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
OK
ID_dish | ID_ingredient
1 | 355
1 | 390
1 | 217
1 | 23
1 | 261
2 | 92
2 | 377
2 | 23
2 | 365
i have ingredients with ID_ingredient (355, 390, 217, 23, 261)
with query i have to get:
ID_dish | missing
1 | 1
2 | 3
because in table you can see that dish with id 1 has ingredients with id
(355, 390, 217, 23, 261, 23) and i have 4/5 of them on second place is dish
with id 2 with ingredients with id (377, 23, 365) and i have 1/3
ingreedients....
--
View this message in context:
http://old.nabble.com/help-with-optimazing-sql-query-tp30608918p30610041.html
Sent from the SQLite mailing list archive at Nabble.com.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
