Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Group By & Order By в Функциях!  [new]
adimmat
Member

Откуда: Таджикистан
Сообщений: 180
Доброй ночи, друзья!
Такая задача: "Cоздать функцию, определяющую для заданного месяца заданного года максимальный объем поставки одним внешним поставщиком (все поставщики, кроме склада имеется в виду)"!
Все, вроде, сделал, но СУБД выдает ошибку 1033, мол, проблема с Group & Order By.

Вот мое решение!

CREATE FUNCTION Max_Volume_Supplier 
(	
	-- Add the parameters for the function here
	@month INT, 
	@year INT
)
RETURNS TABLE 
AS
RETURN 
(
	-- Add the SELECT statement with parameter references here
	SELECT 
		[Suppliers].[Supplier],
		[Volume] = SUM ([Storage].[Количество] * [Storage].[Цена]) 
	
	FROM 
		[dbo].[Storage] LEFT JOIN
		[dbo].[Suppliers] ON [Storage].[КодПоставщика] = [Suppliers].[ID]
	
	WHERE 
		MONTH ([Storage].[Датадвижения]) = @month AND
		YEAR ([Storage].[Датадвижения]) = @year AND
		[Storage].[КодПоставщика] <> 21

	GROUP BY [Suppliers].[Supplier]
	ORDER BY [Volume]
) 


Помогите решить проблему, пожалуйста!
Спасибо!
23 ноя 14, 21:45    [16893322]     Ответить | Цитировать Сообщить модератору
 Re: Group By & Order By в Функциях!  [new]
adimmat
Member

Откуда: Таджикистан
Сообщений: 180
Забыл указать "TOP 1" после SELECT-а!
23 ноя 14, 21:48    [16893334]     Ответить | Цитировать Сообщить модератору
 Re: Group By & Order By в Функциях!  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31446
adimmat
Все, вроде, сделал, но СУБД выдает ошибку 1033, мол, проблема с Group & Order By.
А можно просто скопировать сообщение об ошибке, а не "своими словами"?
23 ноя 14, 22:10    [16893422]     Ответить | Цитировать Сообщить модератору
 Re: Group By & Order By в Функциях!  [new]
adimmat
Member

Откуда: Таджикистан
Сообщений: 180
alexeyvg, Вот сообщение об ошибке!

"Msg 1033, Level 15, State 1, Procedure Max_Volume_Supplier, Line 27
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified."
23 ноя 14, 22:29    [16893475]     Ответить | Цитировать Сообщить модератору
 Re: Group By & Order By в Функциях!  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31446
adimmat
alexeyvg, Вот сообщение об ошибке!

"Msg 1033, Level 15, State 1, Procedure Max_Volume_Supplier, Line 27
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified."
А, ну да, не указали TOP

Я думал, ваш предыдущий пост уточняет текст запроса, который выдаёт ошибку...
23 ноя 14, 23:17    [16893669]     Ответить | Цитировать Сообщить модератору
 Re: Group By & Order By в Функциях!  [new]
adimmat
Member

Откуда: Таджикистан
Сообщений: 180
alexeyvg, Огромное спасибо Вам!
24 ноя 14, 08:10    [16894278]     Ответить | Цитировать Сообщить модератору
 Re: Group By & Order By в Функциях!  [new]
LexusR
Member

Откуда: Novosibirsk
Сообщений: 1873
если нужны ...(все поставщики, кроме склада имеется в виду)!
то TOP 1 не прокатит так как выдаст только 1 запись

нужно просто убрать order by и сортировать при вызове функции

select * from Max_Volume_Supplier (@month=1,@year=2014) order by [Volume]


или если уж очень хочется заменить TOP 1 на TOP 100 PERCENT
24 ноя 14, 09:47    [16894541]     Ответить | Цитировать Сообщить модератору
 Re: Group By & Order By в Функциях!  [new]
iap
Member

Откуда: Москва
Сообщений: 47001
LexusR
если нужны ...(все поставщики, кроме склада имеется в виду)!
то TOP 1 не прокатит так как выдаст только 1 запись

нужно просто убрать order by и сортировать при вызове функции

select * from Max_Volume_Supplier (@month=1,@year=2014) order by [Volume]



или если уж очень хочется заменить TOP 1 на TOP 100 PERCENT
1. Нужны не все записи, а с максимумами.
2. Зачем TOP 100 PERCENT? Если для упорядочивания, то сервер игнорирует эту конструкцию, начиная с SQL2005
24 ноя 14, 09:53    [16894572]     Ответить | Цитировать Сообщить модератору
 Re: Group By & Order By в Функциях!  [new]
iap
Member

Откуда: Москва
Сообщений: 47001
iap
LexusR
если нужны ...(все поставщики, кроме склада имеется в виду)!
то TOP 1 не прокатит так как выдаст только 1 запись

нужно просто убрать order by и сортировать при вызове функции

select * from Max_Volume_Supplier (@month=1,@year=2014) order by [Volume]




или если уж очень хочется заменить TOP 1 на TOP 100 PERCENT
1. Нужны не все записи, а с максимумами.
2. Зачем TOP 100 PERCENT? Если для упорядочивания, то сервер игнорирует эту конструкцию, начиная с SQL2005
1. - глупость написал. Там же функция...
24 ноя 14, 09:55    [16894582]     Ответить | Цитировать Сообщить модератору
 Re: Group By & Order By в Функциях!  [new]
LexusR
Member

Откуда: Novosibirsk
Сообщений: 1873
вообщето ему то похоже и в самом деле нужен один поставщик с максиальным объёмом за месяц. Тогда все правильно
top 1
...
order by VOLUME
только добавит DESC

order by VOLUME DESC
24 ноя 14, 10:15    [16894692]     Ответить | Цитировать Сообщить модератору
 Re: Group By & Order By в Функциях!  [new]
angel_zar
Member

Откуда: Барнаул
Сообщений: 902
А если у двоих поставщиков объём за месяц одинаковый и при этом он является максимальным?
24 ноя 14, 10:48    [16894917]     Ответить | Цитировать Сообщить модератору
 Re: Group By & Order By в Функциях!  [new]
iap
Member

Откуда: Москва
Сообщений: 47001
LexusR
вообщето ему то похоже и в самом деле нужен один поставщик с максиальным объёмом за месяц. Тогда все правильно
top 1
...
order by VOLUME
только добавит DESC

order by VOLUME DESC
И, кстати, вызов функции в таком виде, как у Вас, невозможен.
24 ноя 14, 11:46    [16895384]     Ответить | Цитировать Сообщить модератору
 Re: Group By & Order By в Функциях!  [new]
adimmat
Member

Откуда: Таджикистан
Сообщений: 180
Друзья, огромное спасибо Вам всем! Учел замечания Ваши, отдельное спасибо за замечание "Если у 2-х поставщиков одинаковый объем поставок"! Постараюсь исправить наилучшем образом!
Спасибо еще раз ВСЕМ!
24 ноя 14, 16:15    [16897440]     Ответить | Цитировать Сообщить модератору
 Re: Group By & Order By в Функциях!  [new]
Jaffar
Member

Откуда:
Сообщений: 633
adimmat
Друзья, огромное спасибо Вам всем! Учел замечания Ваши, отдельное спасибо за замечание "Если у 2-х поставщиков одинаковый объем поставок"! Постараюсь исправить наилучшем образом!
Спасибо еще раз ВСЕМ!



В запрос добавить max(Volume) over() MAX_VOL, обернуть в подзапрос и в условии сделать
where
VOL = MAX_VOL
25 ноя 14, 08:19    [16899840]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить