Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Ещё 1 вопрос group by и про аналог Last() от MS Access  [new]
LightN
Member

Откуда: Москва
Сообщений: 189
 		Таблица: dbo.Rote
KeyID REQ DateRote

1 105 01.01.14
2 100 21.01.14
3 101 03.02.14*
4 102 25.03.14
5 103 02.04.14*
6 104 09.04.14
7 102 11.05.14*
8 105 28.06.14*
9 106 13.07.14*
10 100 17.08.14*
11 104 29.09.14*

Вопрос: нужно сделать вот такую группировку и выбрать строки = (*), т.е. нужно сгруппировать по REQ,
найти все REQ с последними (max) KeyID, соответственно оставить только эти строки.

Я делаю так

select KeyID,REQ,DateRote from dbo.Rote as R1 inner join
(select max(KeyID) as mKeyID, REQ from dbo.Rote) as R2
on R1.KeyID=R2.KeyID

Существует ли более оптимальный способ?
19 окт 14, 15:37    [16727597]     Ответить | Цитировать Сообщить модератору
 Re: Ещё 1 вопрос group by и про аналог Last() от MS Access  [new]
iap
Member

Откуда: Москва
Сообщений: 47052
LightN
Таблица: dbo.Rote
KeyID REQ DateRote

1 105 01.01.14
2 100 21.01.14
3 101 03.02.14*
4 102 25.03.14
5 103 02.04.14*
6 104 09.04.14
7 102 11.05.14*
8 105 28.06.14*
9 106 13.07.14*
10 100 17.08.14*
11 104 29.09.14*

Вопрос: нужно сделать вот такую группировку и выбрать строки = (*), т.е. нужно сгруппировать по REQ,
найти все REQ с последними (max) KeyID, соответственно оставить только эти строки.

Я делаю так

select KeyID,REQ,DateRote from dbo.Rote as R1 inner join
(select max(KeyID) as mKeyID, REQ from dbo.Rote) as R2
on R1.KeyID=R2.KeyID

Существует ли более оптимальный способ?
Хоть бы версию сервера сказали!
Для SQL2005+
SELECT TOP(1)WITH TIES *
FROM dbo.Rote
ORDER BY ROW_NUMBER()OVER(PARTITION BY REQ ORDER BY DateRote DESC);
или
WITH CTE AS(SELECT N=ROW_NUMBER()OVER(PARTITION BY REQ ORDER BY DateRote DESC),* FROM dbo.Rote)
SELECT * FROM CTE WHERE N=1;
И ещё 100500 способов.
19 окт 14, 16:42    [16727740]     Ответить | Цитировать Сообщить модератору
 Re: Ещё 1 вопрос group by и про аналог Last() от MS Access  [new]
aleks2
Guest 		Ты забыл главный
SELECT * FROM dbo.Rote r
  WHERE r.KeyID = ( select top(1) KeyID from dbo.Rote where REQ = r.REQ order by KeyID desc );
19 окт 14, 17:07    [16727788]     Ответить | Цитировать Сообщить модератору
 Re: Ещё 1 вопрос group by и про аналог Last() от MS Access  [new]
iap
Member

Откуда: Москва
Сообщений: 47052
aleks2
Ты забыл главный
SELECT * FROM dbo.Rote r
  WHERE r.KeyID = ( select top(1) KeyID from dbo.Rote where REQ = r.REQ order by KeyID desc );
Осталось 100499
19 окт 14, 17:39    [16727845]     Ответить | Цитировать Сообщить модератору
 Re: Ещё 1 вопрос group by и про аналог Last() от MS Access  [new]
LightN
Member

Откуда: Москва
Сообщений: 189
iap
aleks2
Ты забыл главный
SELECT * FROM dbo.Rote r
  WHERE r.KeyID = ( select top(1) KeyID from dbo.Rote where REQ = r.REQ order by KeyID desc );
Осталось 100499


Версия 2012 ...
Большое спасибо за ответы.
Если можно, то ещё есть 1 вопрос. Как Вы думаете, насколько оправдано делать выборки такого типа, при условии что пользовательская нагрузка на этот запрос очень высока? Имеет ли смысл заводить bit-поле (LastREQ) и триггерами маркировать последние записи в этом поле для ускорения запроса (LastREQ=1) и вообще отказаться от применения r.KeyID = ( select top(1) KeyID ... ? Как правильно и грамотно сделать?
19 окт 14, 19:15    [16728091]     Ответить | Цитировать Сообщить модератору
 Re: Ещё 1 вопрос group by и про аналог Last() от MS Access  [new]
LightN
Member

Откуда: Москва
Сообщений: 189
iap
aleks2
Ты забыл главный
SELECT * FROM dbo.Rote r
  WHERE r.KeyID = ( select top(1) KeyID from dbo.Rote where REQ = r.REQ order by KeyID desc );
Осталось 100499

One More Time.

Версия 2014 !!!.
Большое спасибо за ответы.
Если можно, то ещё есть 1 вопрос. Как Вы думаете, насколько оправдано делать выборки такого типа, при условии что пользовательская нагрузка на этот запрос очень высока? Имеет ли смысл заводить bit-поле (LastREQ) и триггерами маркировать последние записи в этом поле для ускорения запроса (LastREQ=1) и вообще отказаться от применения r.KeyID = (select top(1) KeyID ... ? Как правильно и грамотно сделать?

Да и ещё один косяк! При сохранении View мс-студия ругается на (order by KeyID desc), говорит, что во View получится негарантированный результат, но сохраниться тем не менее позволяет ... :(
20 окт 14, 11:26    [16729921]     Ответить | Цитировать Сообщить модератору
 Re: Ещё 1 вопрос group by и про аналог Last() от MS Access  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8350
 		Не факт, что последняя вставленная запись будет иметь максимальную дату.
20 окт 14, 11:29    [16729945]     Ответить | Цитировать Сообщить модератору
 Re: Ещё 1 вопрос group by и про аналог Last() от MS Access  [new]
LightN
Member

Откуда: Москва
Сообщений: 189
 		Владислав Колосов,
MS тоже не гарантирует. Я вот выписал триггер, но тем не менее интересно (и странно) почему MS в MS Access имеет функцию Last(), а более продвинутом MS SQL её нет и приходится делать лишние движения, да и ещё с негарантированными результатами.
20 окт 14, 11:48    [16730052]     Ответить | Цитировать Сообщить модератору
 Re: Ещё 1 вопрос group by и про аналог Last() от MS Access  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8350
 		LightN,

а как работает last()?
20 окт 14, 11:58    [16730107]     Ответить | Цитировать Сообщить модератору
 Re: Ещё 1 вопрос group by и про аналог Last() от MS Access  [new]
LightN
Member

Откуда: Москва
Сообщений: 189
Владислав Колосов
LightN,
а как работает last()?


select last(KeyID),REQ,last(DateRote) from dbo.Rote group by REQ ORDER BY KeyID

Вот, например, в этом примере last(KeyID)=max(KeyID) т.к. ORDER BY KeyID
А вот last(DateRote) будет взята именно у той записи которая стоит последней в "group by REQ ORDER BY KeyID", и она далеко не равна max(DateRote)
Разве это не удобно?
--
А ещё есть first() :)
20 окт 14, 12:24    [16730299]     Ответить | Цитировать Сообщить модератору
 Re: Ещё 1 вопрос group by и про аналог Last() от MS Access  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8350
 		LightN, понятно. Last() с ещё большим успехом можно заменить на row_number() over(), как было написано ранее.
20 окт 14, 12:40    [16730406]     Ответить | Цитировать Сообщить модератору
 Re: Ещё 1 вопрос group by и про аналог Last() от MS Access  [new]
iap
Member

Откуда: Москва
Сообщений: 47052
LightN
Владислав Колосов
LightN,
а как работает last()?


select last(KeyID),REQ,last(DateRote) from dbo.Rote group by REQ ORDER BY KeyID

Вот, например, в этом примере last(KeyID)=max(KeyID) т.к. ORDER BY KeyID
А вот last(DateRote) будет взята именно у той записи которая стоит последней в "group by REQ ORDER BY KeyID", и она далеко не равна max(DateRote)
Разве это не удобно?
--
А ещё есть first() :)
В SQL 2012+ есть и FIRST()OVER(), и LAST()OVER() и много-много чего ещё...
Только в данном случае я не вижу, чем это лучше предложенного.
20 окт 14, 12:45    [16730449]     Ответить | Цитировать Сообщить модератору
 Re: Ещё 1 вопрос group by и про аналог Last() от MS Access  [new]
aleks2
Guest
LightN
Да и ещё один косяк! При сохранении View мс-студия ругается на (order by KeyID desc), говорит, что во View получится негарантированный результат, но сохраниться тем не менее позволяет ... :(


Это просто конструктор Access запутался при разборе View.
Все там гарантировано.
20 окт 14, 12:55    [16730512]     Ответить | Цитировать Сообщить модератору
 Re: Ещё 1 вопрос group by и про аналог Last() от MS Access  [new]
LightN
Member

Откуда: Москва
Сообщений: 189
iap
LightN
пропущено...


select last(KeyID),REQ,last(DateRote) from dbo.Rote group by REQ ORDER BY KeyID

Вот, например, в этом примере last(KeyID)=max(KeyID) т.к. ORDER BY KeyID
А вот last(DateRote) будет взята именно у той записи которая стоит последней в "group by REQ ORDER BY KeyID", и она далеко не равна max(DateRote)
Разве это не удобно?
--
А ещё есть first() :)
В SQL 2012+ есть и FIRST()OVER(), и LAST()OVER() и много-много чего ещё...
Только в данном случае я не вижу, чем это лучше предложенного.
Когда только 1-н Last(), то я понял как делать. Тут да, ни чем не лучше. А если вдруг нужен будет запрос в котором нужно собрать поля с разными Last() и First(), причем в одном group by ... . Вот пример:

select Max([KeyID]),[REQ],last([DateRote]),first([Adress],last([Driver]) from dbo.Rote group by [REQ] ORDER BY [KeyID]

Такое возможно? (где last([DateRote]) <> max и first([Adress]) <> min)
---
А что конструктор запутался, м.б. и так.
20 окт 14, 14:49    [16731359]     Ответить | Цитировать Сообщить модератору
 Re: Ещё 1 вопрос group by и про аналог Last() от MS Access  [new]
iap
Member

Откуда: Москва
Сообщений: 47052
LightN
нужно собрать поля с разными Last() и First(), причем в одном group by
Чо?
Может, лучше посмотреть в документации про Функции аналитики (Transact-SQL)?
20 окт 14, 14:57    [16731416]     Ответить | Цитировать Сообщить модератору
 Re: Ещё 1 вопрос group by и про аналог Last() от MS Access  [new]
iap
Member

Откуда: Москва
Сообщений: 47052
iap
LightN
нужно собрать поля с разными Last() и First(), причем в одном group by
Чо?
Может, лучше посмотреть в документации про Функции аналитики (Transact-SQL)?
Да и вообще: OVER (Transact-SQL)
20 окт 14, 14:58    [16731426]     Ответить | Цитировать Сообщить модератору
 Re: Ещё 1 вопрос group by и про аналог Last() от MS Access  [new]
LightN
Member

Откуда: Москва
Сообщений: 189
 		iap,
Спасибо за ссылки!
--
Разобрался,
ROW_NUMBER() OVER
FIRST_VALUE() OVER
LAST_VALUE() OVER
делают всё то, что нужно ...
20 окт 14, 17:23    [16732551]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить