Помогите с запросом

← →
Romkin © (2003-04-29 10:40) [0]

Давно уже пишу хранимые процедуры, запросы простые, а тут наткнулся на задачу, и застрял:
Есть таблица вида (ClientID, OrderID) - думаю, понятно, клиенты и документы.
Вывести запросом ClientID - номер клиента(тов!), которые выписали максимальное количество документов.
Например:
ClientID OrderID 1 23 1 46 5 62 5 48 5 81 24 46 34 89 34 13
Должно выдать 5
О выдаче количества речь не идет, нужен номер.
Проблема в том, что сделать нужно одним запросом на основе стандарта. Напоминаю: select top ... нет (и не подходит)
select from select нет, select max(count) - вложенные агрегатные функции недопустимы.
Вопрос: Можно ли написать подобный запрос, один, без использования view?

← →
KoluChi © (2003-04-29 10:43) [1]

Select Max(Count(ClientID)) From Table
Group by ClientID

← →
KoluChi © (2003-04-29 10:46) [2]

не то немного, сейчас соображу ... (может быть)

← →
Соловьев © (2003-04-29 10:46) [3]

> KoluChi © (29.04.03 10:43)
> Select Max(Count(ClientID)) From Table
> Group by ClientID

а поле ClientID не надо разве в SELECT?

← →
KoluChi © (2003-04-29 10:53) [4]

Мой вариант не проходит, нужно было внимательнее прочитать вопрос :)

← →
Romkin © (2003-04-29 10:54) [5]

Говорю же - вложенные агрегаты недомустимы!

← →
KoluChi © (2003-04-29 10:58) [6]

Пойдем логическим путем:
1) нужно вернуть 1 значение - сделать это можно только агр. функцией (max, count) в данном случае нужен max.
2) Максимум нужно искать из количества документов - количество документов - count(OrderID) для конкретного ClientID
3) Порядок сортировки не поможет (top тоже нет)
4) WHERE не знаю куда вставить
Получаем запрос с 2 вложенными группировками, если у нас нет SELECT FROM SELECT и MAX(COUNT), imho, одним запросом не обойтись.

← →
Соловьев © (2003-04-29 11:01) [7]

Select ClientID, sum(OrderID) From Table Group by ClientID Order by ClientID desc

и взять первую запись

← →
Romkin © (2003-04-29 11:01) [8]

А если group by & having добавить? во where & having подзапросы писать же можно...
top, кстати, в любом случае не подойдет - а если есть два ClientID с одинаковым максимальным количеством?

← →
Соловьев © (2003-04-29 11:01) [9]

забыл :)))

Select ClientID, sum(OrderID) as SumOrd From Table
Group by ClientID
Order by SumOrd desc

← →
Romkin © (2003-04-29 11:03) [10]

Суммировать OrderID?! И что получится?

← →
KoluChi © (2003-04-29 11:04) [11]

> Соловьев © (29.04.03 11:01)

sum(OrderID) - получим что-то страшное :)

← →
Соловьев © (2003-04-29 11:05) [12]

вот блин, читать надо было внимательно вопрос... сорри я подумал что OrderId это количество... сорри еще раз...

← →
Соловьев © (2003-04-29 11:07) [13]

тогда так...
Select ClientID, count(ClientID) as CntCli From Table Group by ClientID Order by CntCli desc
и взять первую запись...

← →
KoluChi © (2003-04-29 11:08) [14]

> Romkin © (29.04.03 11:01)
> а если есть два ClientID с одинаковым максимальным количеством?

Тогда моя лог. цепочка разбилась в пух и прах :)
>Соловьев © (29.04.03 11:01)
только всесто sum взять count и строки обрабатывать вручную

← →
Соловьев © (2003-04-29 11:14) [15]

> и строки обрабатывать вручную

ну а че? если уж извращяться так по полной программе... :))))

← →
Romkin © (2003-04-29 11:21) [16]

Select ClientID, count(ClientID) as CntCli From Table
Group by ClientID
having count(ClientID) = (Select top 1 count(ClientID) from Table group by ClientID order by count(ClientID) desc)
Теперь проблема - избавиться от этого самого top :-))

← →
KoluChi © (2003-04-29 11:29) [17]

> Соловьев © (29.04.03 11:14)
>
> > и строки обрабатывать вручную
>
> ну а че? если уж извращяться так по полной программе...
> :))))

Почему извращаться, в любом случае нужно будет обрабатывать несколько строк с идентификаторами. Если конечно не найдете способ идентификаторы разместить по колонкам (вот это будут извращение :)

← →
stone © (2003-04-29 11:30) [18]

view:
select clientid, count(orderid) as ordercount from table group by clientid
, а потом запрос

select client id from table where clientid in (select clientid from view where ordercount = (select max(ordercount) from view))

если пооптимизировать, то конструкцию in можно заменить на конструкцию exists

← →
Соловьев © (2003-04-29 11:31) [19]

> Почему извращаться, в любом случае нужно будет обрабатывать
> несколько строк с идентификаторами

я имею ввиду, проверку, когда несколько клиентов будут иметь максимальное количество.

← →
KoluChi © (2003-04-29 11:31) [20]

Select ClientID, count(ClientID) as CntCli From Table
Group by ClientID
having count(ClientID) = (Select count(ClientID) from Table group by ClientID order by count(ClientID) desc)
и не надо избавиться от этого самого top :-))

← →
Romkin © (2003-04-29 11:35) [21]

2KoluChi А вот так не пойдет, при = < > подзапрос должен вернуть строго 1 строку

← →
KoluChi © (2003-04-29 11:43) [22]

вместо = написать IN? Тогда вообще смысла нет.
остановимся на варианте Соловьев © (29.04.03 11:07)

← →
Romkin © (2003-04-29 11:53) [23]

Yesss!!!
Select ClientID, count(ClientID) as CntCli From Table
Group by ClientID
having count(ClientID) not in (Select count(ClientID)-1 from Table group by ClientID)

Должно сработать!!! Или нет? Надо потестить

Только благодаря вам :-))
Не пофлеймишь - не поймешь :-)))

>Romkin © (29.04.03 11:53)

Понятно, что такой запрос может давать некорректный результат...:)

Такой запрос однозначен :
SELECT T1.ClientID, COUNT(T1.ClientID) FROM Table T1 GROUP BY T1.ClientID HAVING COUNT(T1.ClientID) > ALL (SELECT COUNT(T2.ClientID)-1 FROM Table T2 GROUP BY T2.ClientID)

Точно, спасибо :-))

Кстати, могу сказать, что на мой запрос
Select ClientID, count(ClientID) as CntCli From Table
Group by ClientID
having count(ClientID) not in (Select count(ClientID)-1 from Table group by ClientID)
IB рушится:
internal gds software consistency check (EVL_expt: invalid operation (232))
А вот запрос Johnmen проходит, и дает правильный результат :-))

>Romkin © (29.04.03 15:03)
>IB рушится

Может быть потому, что идет прямое обращение к полям таблицы, а не через псевдонимы ?
То есть, я хочу сказать, что сервер не может понять, где, какое поле какой таблицы (логической)...

Если бы, буквально, твой запрос, стоит вместо "> ALL" поставить "NOT IN" - bugcheck :-(

Странно...Видимо, действительно, баг...:)
Проверил на WI-V6.2.908 Firebird 1.0 - нормально...

Нормально?! IB5.6 & FB1.5 - баг. Можно смеяться

Помогите с запросом Найти похожие ветки