Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 TOP + ORDER BY  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1310
всё руки не доходили запостить, но
всплыл пару недель назад один из старых

У него перестал работать запрос, проблема оказалась в том, что если идёт присвоение переменной в запросе и стоит оператор order by, то присваивается только первая переменная. Если поставить top, то всё работает как надо

Вот тест кейс, коментим ТОП и смотрим результат.

declare @columnList nvarchar(max) = '';
declare @columnListOut nvarchar(max) = '';
declare @columnListBp nvarchar(max) = '';
declare @columnListBpOut nvarchar(max) = '';

with cte as
(
select '1.1.1' as QID union all
select '1.1.10' union all
select '5.5.21' union all
select '1.1.9' union all
select '1.1.2' union all
select '1.1.8'
  
)

--select * from cte;
select top 10000 
      @columnList = 
       @columnList +
       case 
        when @columnList = ''
        then ''
        else ',
        '
       end +  'max(case r.QID when ''' + c.QID + ''' then r.answer end) as "QID_' + c.QID + '"',
       
       @columnListOut = 
       @columnListOut +
       case 
        when @columnListOut = ''
        then ''
        else ',
        '
       end +  'c."QID_' + c.QID + '"',
       
       @columnListBp =  
       @columnListBp +
       case 
        when @columnList = '' --it is correct
        then ''
        else ',
        '
       end +  'max(case r.QID when ''' + c.QID + ''' then r.BP_ID end) as "BP_' + c.QID + '"',
       
       @columnListBpOut =  
       @columnListBpOut +
       case 
        when @columnList = ''  --it is correct
        then ''
        else ',
       '
       end +  'c."BP_' + c.QID + '"'
       
  from cte c
 where c.QID is not null
/*
Columns are ordered by name in alphabet order
*/
    --order by c.QID;
    order by 
    cast(left(c.QID, charindex('.', c.QID) -1) as int),
    cast(substring(c.QID, charindex('.', c.QID) + 1, LEN(c.QID) - charindex('.', reverse(c.QID)) - charindex('.', c.QID)) as int),
    cast(right(c.QID, charindex('.', reverse(c.QID)) - 1) as int);
    
    print --top 10 
       @columnList
5 мар 13, 11:51    [14013158]     Ответить | Цитировать Сообщить модератору
 Re: TOP + ORDER BY  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74930
автор
У него перестал работать запрос


А кто-то обещал, что такой запрос впринципе должен работать так, как мы того ожидаем?
5 мар 13, 11:53    [14013182]     Ответить | Цитировать Сообщить модератору
 Re: TOP + ORDER BY  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1310
Банальная конкатенация строк.

set @v = @v + t.value

я как бы "ожидал". Причём без "оредер бай" работает. Либо с ТОП работает.
После добавление Ордер бай я бы ожидал "тот же результат с другим порядком".
5 мар 13, 11:57    [14013233]     Ответить | Цитировать Сообщить модератору
 Re: TOP + ORDER BY  [new]
Glory
Member

Откуда:
Сообщений: 104751
NIIIK
я как бы "ожидал". Причём без "оредер бай" работает. Либо с ТОП работает.
После добавление Ордер бай я бы ожидал "тот же результат с другим порядком".

На чем основаны были ожидания ?
5 мар 13, 11:59    [14013253]     Ответить | Цитировать Сообщить модератору
 Re: TOP + ORDER BY  [new]
Гость333
Member

Откуда:
Сообщений: 3683
NIIIK
Банальная конкатенация строк.

set @v = @v + t.value

В вашем примере не банальная конкатенация, а недокументированный приём. Нигде не зафиксировано, как это должно работать.
5 мар 13, 11:59    [14013269]     Ответить | Цитировать Сообщить модератору
 Re: TOP + ORDER BY  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1310
Glory
NIIIK
я как бы "ожидал". Причём без "оредер бай" работает. Либо с ТОП работает.
После добавление Ордер бай я бы ожидал "тот же результат с другим порядком".

На чем основаны были ожидания ?


На том что "без ордер бай работало, но нужно было плучить тот же результат только в другом порядке и том что ордер бай не изменяет количество строк, что кстати хорошо видно в подзапросах которые должны возврщать только одну строку).
5 мар 13, 12:02    [14013301]     Ответить | Цитировать Сообщить модератору
 Re: TOP + ORDER BY  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1310
Гость333
NIIIK
Банальная конкатенация строк.

set @v = @v + t.value

В вашем примере не банальная конкатенация, а недокументированный приём. Нигде не зафиксировано, как это должно работать.


Прям хакерский приём :) ?!
Я сейчас не про факт конкатенации говорю (хотя явно не в курсорах обычно реализуют её), а про количество строк.
5 мар 13, 12:03    [14013310]     Ответить | Цитировать Сообщить модератору
 Re: TOP + ORDER BY  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1310
Гость333
NIIIK
Банальная конкатенация строк.

set @v = @v + t.value

В вашем примере не банальная конкатенация, а недокументированный приём. Нигде не зафиксировано, как это должно работать.

+
это должно работать "одинаково" и нигде не зафиксировано что присваивание переменных в запросе делается "по первой строке если стоит ордер бай".
5 мар 13, 12:04    [14013320]     Ответить | Цитировать Сообщить модератору
 Re: TOP + ORDER BY  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
https://www.sql.ru/faq/faq_topic.aspx?fid=731
5 мар 13, 12:05    [14013327]     Ответить | Цитировать Сообщить модератору
 Re: TOP + ORDER BY  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1310
tpg
https://www.sql.ru/faq/faq_topic.aspx?fid=731


Для случая с группировкой я предпочитаю CLR функцию.
Тот метод кстати медленее работает. По быстродействую сопостовим "тупо написать отдельную функци делающую отдельный запрос и конкатенацию)" если не медленее.
5 мар 13, 12:08    [14013356]     Ответить | Цитировать Сообщить модератору
 Re: TOP + ORDER BY  [new]
Glory
Member

Откуда:
Сообщений: 104751
NIIIK
+
это должно работать "одинаково" и нигде не зафиксировано что присваивание переменных в запросе делается "по первой строке если стоит ордер бай".

BOL
SELECT @local_variable is typically used to return a single value into the variable. However, when expression is the name of a column, it can return multiple values. If the SELECT statement returns more than one value, the variable is assigned the last value that is returned.
5 мар 13, 12:10    [14013369]     Ответить | Цитировать Сообщить модератору
 Re: TOP + ORDER BY  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381
> это должно работать "одинаково"

это кто сказал?

> и нигде не зафиксировано что присваивание переменных в запросе делается "по первой строке если стоит ордер бай".

давно уж расписались в том, что результат запроса, подобного вашему неопределен.

http://support.microsoft.com/kb/287515/en-us
5 мар 13, 12:11    [14013373]     Ответить | Цитировать Сообщить модератору
 Re: TOP + ORDER BY  [new]
Glory
Member

Откуда:
Сообщений: 104751
NIIIK
На том что "без ордер бай работало, но нужно было плучить тот же результат только в другом порядке и том что ордер бай не изменяет количество строк, что кстати хорошо видно в подзапросах которые должны возврщать только одну строку).

Молодец.
5 мар 13, 12:13    [14013398]     Ответить | Цитировать Сообщить модератору
 Re: TOP + ORDER BY  [new]
Гость333
Member

Откуда:
Сообщений: 3683
NIIIK
Прям хакерский приём :) ?!
Я сейчас не про факт конкатенации говорю (хотя явно не в курсорах обычно реализуют её), а про количество строк.

Недокументированный приём — это запрос вида
select @v = @v + t.value
from ...
5 мар 13, 12:17    [14013436]     Ответить | Цитировать Сообщить модератору
 Re: TOP + ORDER BY  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1310
Glory
NIIIK
+
это должно работать "одинаково" и нигде не зафиксировано что присваивание переменных в запросе делается "по первой строке если стоит ордер бай".

BOL
SELECT @local_variable is typically used to return a single value into the variable. However, when expression is the name of a column, it can return multiple values. If the SELECT statement returns more than one value, the variable is assigned the last value that is returned.


До "last value" оно и не дошло

Разницу вообще все без "top" и "заведомо большой Top" видят ?!
5 мар 13, 12:39    [14013625]     Ответить | Цитировать Сообщить модератору
 Re: TOP + ORDER BY  [new]
Glory
Member

Откуда:
Сообщений: 104751
NIIIK
Разницу вообще все без "top" и "заведомо большой Top" видят ?!

Разницу между "я хочу, чтобы так работало" и "так работает" тоже кто-то видит ?
5 мар 13, 12:43    [14013655]     Ответить | Цитировать Сообщить модератору
 Re: TOP + ORDER BY  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1310
Glory
NIIIK
Разницу вообще все без "top" и "заведомо большой Top" видят ?!

Разницу между "я хочу, чтобы так работало" и "так работает" тоже кто-то видит ?


Вы эксперт местный или "адвокат майкрософта" ?!

Документация на оператор Топ есть? должен ли заведомо большой оператор топ изменять количество строк в запросе или результат запроса?
5 мар 13, 12:53    [14013737]     Ответить | Цитировать Сообщить модератору
 Re: TOP + ORDER BY  [new]
Glory
Member

Откуда:
Сообщений: 104751
NIIIK
Документация на оператор Топ есть?

А документация на select @v = @v + t.value есть ?

NIIIK
должен ли заведомо большой оператор топ изменять количество строк в запросе или результат запроса?

Причем тут количество записей ?
Меняется план выполнения.
5 мар 13, 12:55    [14013750]     Ответить | Цитировать Сообщить модератору
 Re: TOP + ORDER BY  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1310
    order by c.QID;
    /*order by 
    cast(left(c.QID, charindex('.', c.QID) -1) as int),
    cast(substring(c.QID, charindex('.', c.QID) + 1, LEN(c.QID) - charindex('.', reverse(c.QID)) - charindex('.', c.QID)) as int),
    cast(right(c.QID, charindex('.', reverse(c.QID)) - 1) as int);*/


такой вариант тоже работет, только порядок "алфавитный" (собственно его и фиксили).
5 мар 13, 13:10    [14013877]     Ответить | Цитировать Сообщить модератору
 Re: TOP + ORDER BY  [new]
Shakill
Member

Откуда: мск
Сообщений: 1887
NIIIK
У него перестал работать запрос, проблема оказалась в том, что если идёт присвоение переменной в запросе и стоит оператор order by, то присваивается только первая переменная. Если поставить top, то всё работает как надо

Вот тест кейс, коментим ТОП и смотрим результат.
<...>

что-то не сходится с вашим утверждением
закомментил TOP, добавил вывод остальных трех переменных, на выходе
max(case r.QID when '5.5.21' then r.answer end) as "QID_5.5.21"
c."QID_5.5.21"
max(case r.QID when '5.5.21' then r.BP_ID end) as "BP_5.5.21"
c."BP_5.5.21"
т.е., все четыре переменных получают значения
5 мар 13, 13:15    [14013924]     Ответить | Цитировать Сообщить модератору
 Re: TOP + ORDER BY  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1310
Shakill
NIIIK
У него перестал работать запрос, проблема оказалась в том, что если идёт присвоение переменной в запросе и стоит оператор order by, то присваивается только первая переменная. Если поставить top, то всё работает как надо

Вот тест кейс, коментим ТОП и смотрим результат.
<...>

что-то не сходится с вашим утверждением
закомментил TOP, добавил вывод остальных трех переменных, на выходе
max(case r.QID when '5.5.21' then r.answer end) as "QID_5.5.21"
c."QID_5.5.21"
max(case r.QID when '5.5.21' then r.BP_ID end) as "BP_5.5.21"
c."BP_5.5.21"
т.е., все четыре переменных получают значения


1) у меня тоже получается только последняя.
тут моя ошибка, я по запарке (и было не вчера) в голове отложил только как заказчик говорил
2) если стоит топ и ордер - возвращает ВСЕ записи
3) если только ордер (по цифрам) БЕЗ Топ - возвращает только первую запись
4) если стоит ордер БЕЗ топ, но по имени - возвращает все записи.
5 мар 13, 13:21    [14013986]     Ответить | Цитировать Сообщить модератору
 Re: TOP + ORDER BY  [new]
Glory
Member

Откуда:
Сообщений: 104751
NIIIK
1) у меня тоже получается только последняя.
тут моя ошибка, я по запарке (и было не вчера) в голове отложил только как заказчик говорил
2) если стоит топ и ордер - возвращает ВСЕ записи
3) если только ордер (по цифрам) БЕЗ Топ - возвращает только первую запись
4) если стоит ордер БЕЗ топ, но по имени - возвращает все записи.

Для неумеющих читать офф.ссылки

An examination of the SHOWPLAN output of the query reveals that the SQL Server query processor builds an different execution plan when expressions are applied to columns in the query's ORDER BY clause, than when those same expressions are applied to columns in the query's SELECT list. The decision made by the query processor is based on the cost of possible execution plans.
5 мар 13, 13:24    [14014012]     Ответить | Цитировать Сообщить модератору
 Re: TOP + ORDER BY  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1310
Glory,

План выполнения - это путь достижения постоянного результата.
Завтра план может поменятся по другой причине (статистика, данные, новый индекс для другого запроса изначально).

Оператор order by - не должен влиять на количество выводимых строк (не одним датастом, а обрабатываемых, как и в курсоре).

Разарабочик не должен думать о том что "если я измению порядок у меня изменится количество".

Оператор TOP ... НЕ ДОЛЖЕН возвращать больше строк чем БЕЗ этого оператора.
5 мар 13, 13:32    [14014087]     Ответить | Цитировать Сообщить модератору
 Re: TOP + ORDER BY  [new]
Glory
Member

Откуда:
Сообщений: 104751
NIIIK
Оператор order by - не должен влиять на количество выводимых строк (не одним датастом, а обрабатываемых, как и в курсоре).

Оператор TOP ... НЕ ДОЛЖЕН возвращать больше строк чем БЕЗ этого оператора.

Да причем тут количество записей ?

Вы меняете текст запроса. И оптимизатор вправе поменять план выполнения
Конкретно в данном случае меняется положение в плане шагов сортировки и вычисления выражений.
Вы хоть какой то план выполнения смотрели ?

NIIIK
План выполнения - это путь достижения постоянного результата.

Для постоянного результата нужно использовать документированные методы.
5 мар 13, 13:37    [14014130]     Ответить | Цитировать Сообщить модератору
 Re: TOP + ORDER BY  [new]
iap
Member

Откуда: Москва
Сообщений: 47198
NIIIK
Glory,

План выполнения - это путь достижения постоянного результата.
Завтра план может поменятся по другой причине (статистика, данные, новый индекс для другого запроса изначально).

Оператор order by - не должен влиять на количество выводимых строк (не одним датастом, а обрабатываемых, как и в курсоре).

Разарабочик не должен думать о том что "если я измению порядок у меня изменится количество".

Оператор TOP ... НЕ ДОЛЖЕН возвращать больше строк чем БЕЗ этого оператора.
Если всё это относится к обсуждаемой конструкции конкатенации строк,
то никто никому ничего не должен!
Говоря Вашими словами, "разарабочик не должен" применять эту недокументированную конструкцию
ввиду её нестабильной непредсказуемой работы.
5 мар 13, 13:38    [14014141]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить