www.sql-ex.ru #14 -

i trying solve problem number 14 known(i believe) website www.sql-ex.ru because need learn sql(i quite new language) same question answered here on stackoverflow problem 14 question stackoverflow. first approach :

select maker, type product group maker, type having count(type)=1 , count(model)>1 

obviously wrong not understand why, i've been looking around , found on google:

select maker, max(type) product group maker, type having count(type)=1 , count(model)>1 

maybe retarted don't understand max doing on char value , why working now.

can please shed light on problem?

cheers,

characters have "collating sequence" (with other things) gives ordering ">", order , max/min on them.

in sqls when group (or use having without it, implicitly groups columns) can select columns either in group or appear arguments of aggregates. that's because there can multiple rows multiple values per group column name doesn't mean particular value. although in standard sql , products can use column when dbms has been told there 1 value of column per subrow of grouping columns.

that allegedly linked query show not in linked answer. , not correct answer question. returns yours does.

select maker, max(type) product group maker, type having count(type)=1 , count(model)>1 

the max, in solution, can dropped, because type grouping column. not solution because no matter types appear has 1 group per maker-type, 1 type per group. doesn't restrict output makers having 1 type.

the actual code @ link is: (note distinct copy missing.)

select maker, max(type) product group maker, type having count(distinct type)=1 , count(model)>1 

this makes group each maker-type pair. naturally max of 1 type value of group type value of group. , there 1 distinct value per group. returns yours does.

that not wanted. trying write was:

select maker, max(type) product group maker having count(distinct type)=1 , count(model)>1 

this selects groups 1 distinct type value among possibly many rows given maker. ok mention type in select because it's in aggregate max. max of type maximum value among bunch of rows have same (distinct) value.

but max can't dropped, because type not grouping column.

ps if table empty?