Подскажите SQL запрос

← →
d@nger (2010-04-25 09:41) [0]

Здравствуйте! Имеется таблица (Firebird) с полем дата, и идентификатор клиента.
id org_id date 1 231 01.03.2010 2 231 02.04.2010 3 231 07.04.2010 4 115 15.04.2010 5 115 17.04.2010 6 205 20.04.2010 7 205 02.05.2010 ...

нужно выбрать все записи за определенный месяц. Это я конечно сделал, но теперь нужно выбрать последнюю по дате запись каждого клиента, т.е из приведенного примера должны остаться записи 3, 5, 6. Я конечно могу это сделать в цикле проверяя каждую запись, но это будет не так эффективно если бы это сделать с помощью запроса. Возможно ли сделать такой запрос?

← →
_Юрий © (2010-04-25 09:52) [1]

передать требуемую дату параметром?

select * from table where date=:p

← →
d@nger (2010-04-25 10:01) [2]

> _Юрий © (25.04.10 09:52) [1]
>
> передать требуемую дату параметром?

дата не известна, известен месяц. Нужно вытащить все записи за этот месяц и среди этих записей оставить только уникальные организации с последней датой...
select * from table where (date>="01.04.2010")and(date<="30.04.2010") вот это все на что у меня ума хватило... как теперь убрать повторные организации с меньшими дататами, а оставить только одну с наибольшей датой...

← →
sniknik © (2010-04-25 10:23) [3]

> только уникальные организации с последней датой...
+ GROUP BY
и Max

← →
d@nger (2010-04-25 10:24) [4]

Урраа! Получилось... погуглил в инете... вот мое решение:

select org_id, max(call_date) from call_log group by call_log.org_id

← →
Anatoly Podgoretsky © (2010-04-25 10:44) [5]

> d@nger (25.04.2010 10:01:02) [2]

Последняя, она же наибольшая - это MAX естественно.

← →
d@nger (2010-04-25 10:49) [6]

теперь не могу объединить эти 2 запроса в один

select * from table where (date>="01.04.2010")and(date<="30.04.2010")
select org_id, max(call_date) from call_log group by call_log.org_id

пишу так:
select id,call_date from call_log where (call_date>="01.04.2010")and(call_date<="30.04.2010")and (id in (select id, max(call_date) from call_log group by call_log.org_id))

ошибка
Invalid token. Dynamic SQL Error. SQL error code = -104. Invalid command. count of column list and variable list do not match.

как правильно его записать?

← →
d@nger (2010-04-25 11:30) [7]

я снова погуглил... решение следующее...
select org_id, max(call_date) from call_log group by org_id having (max(call_date)>="01.04.2010")and(max(call_date)<="30.04.2010")

← →
sniknik © (2010-04-25 12:30) [8]

> select org_id, max(call_date) from call_log
> group by org_id having (max(call_date)>="01.04.2010")and(max(call_date)<="30.04.2010")

индекс по полю org_id или call_date есть? если только по call_date то решение очень глупое... если есть и по org_id, то просто слегка "туповатое".
группировать по всей таблице например 10 миллионов записей. и после обрезать до 10 нужных, это не то же самое, что отобрать 1000 за месяц и после сгруппировать их в нужные 10...

← →
d@nger (2010-04-26 06:50) [9]

да... я после понял это как оставил пост...
пытался переделать запрос.. сначала отобрать нужный период а потом сгруппировать по org_id но кроме как создать представление с необходимым периодом и в этом представлении группировать дни... ничего больше не придумал... подскажите как быть...

← →
d@nger (2010-04-26 07:09) [10]

может уважаемый админ перенесет мою тему в раздел баз данных?...
может кто нибудь подскажет что нибудь.. оптимально ли делать следующим образом
1) создавать сначала представление,
2) потом делать группировку в этом представлении,
3 )формировать отчет на основе полученных данных,
4) удалить представление

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

← →
sniknik © (2010-04-26 07:44) [11]

все можно, нужно только немного изучить синтаксис sql, а не искать готовое по инету, понимать что пишешь...
а то до смешного доходит, не мочь сделать в уже готовом легкую коррекцию, поменять оператор и изменить синтаксис в соответствии с его правилами.

p.s. самое место теме здесь. запросы с where описывают на втором, или третьем занятии в книгах типа "sql для чайников", сразу после введения... группировки попозже, но с ними у тебя проблем вроде нет.

← →
d@nger (2010-04-26 08:14) [12]

вымучил я все таки этот запрос...

select org_id, max(call_date) from call_log where call_date between "01.05.2010" and "30.05.2010" group by org_id

всем спасибо, что направили на путь истинный.. ))))

← →
Anatoly Podgoretsky © (2010-04-26 08:44) [13]

> может уважаемый админ перенесет мою тему в раздел баз данных?
> ...

С какой стати, твой вопрос вообще не имеет отношения к програмированию в Дельфи, тебе нужно в форум по FB, а здесь такого нет. Наиболее близкая конференция это Общая.

← →
d@nger (2010-04-26 09:03) [14]

придется мне опять поднять эту тему...

кроме полей org_id, и call_date? нужно вытащить еще 2 текстовых поля, но в этом случае скрипт не работает, НО если дабавить эти текстовые поля в group by, ошибки нет, но результат не группируется по org_id... как быть в этом случае?

← →
sniknik © (2010-04-26 09:30) [15]

> но результат не группируется по org_id...
эк, я тебя "сглазил"...
> но с ними у тебя проблем вроде нет.

> как быть в этом случае?
читать четвертый раздел "sql для чайников" по "group by". и внимательно смотреть на call_date в запросе.

← →
Дмитрий С © (2010-04-26 09:41) [16]

тут поможет уловка с CONCAT
SELECT * FROM `call_log` WHERE `id` IN ( SELECT CAST(SUBSTRING(MAX(CONCAT(LPAD(`date`,20,0), `id`)), 21) AS INT) FROM `call_log` WHERE `date` between "01.05.2010" and "30.05.2010" GROUP BY `org_id` )
Это на MYSQL. На FB переведешь сам

← →
d@nger (2010-04-26 10:42) [17]

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

← →
d@nger (2010-04-26 10:43) [18]

LPAD(`date`,20,0), `id`) может здесь нужнобыло использовать RPAD ?

← →
test © (2010-04-26 11:08) [19]

ИМХО как то так.

select a.org_id, max(a.call_date) from call_log a left join table b on ((date>="01.04.2010")and(date<="30.04.2010")) and a.org_id = b.id group by a.org_id

← →
sniknik © (2010-04-26 11:17) [20]

ужас какой...

простейший вариант это включить поля в агрегатных функциях, аналогично call_date (ведь он то тебе не мешает группировать по org_id)
но лучше конечно так (надеюсь id это ключ, и авто инкремент (ну аналог его в FB), и еще надеюсь даты в строках воспринимаются правильно... хотя в любом случае советую параметры)

select org_id, call_date, еще 2 текстовых поля from call_log WHERE id IN (select max(id) from call_log where call_date between "01.05.2010" and "30.05.2010" group by org_id)

← →
test © (2010-04-26 11:20) [21]

sniknik © (26.04.10 11:17) [20]
Oracle))

← →
Дмитрий С © (2010-04-26 11:20) [22]

> sniknik ©

В условиях нет факта, что id и call_date одновременно возрастают.

← →
boriskb © (2010-04-26 11:26) [23]

Самое время задуматься: "От инета больше вреда или пользы?"

← →
d@nger (2010-04-26 11:29) [24]

> test © (26.04.10 11:08) [19]
>
> ИМХО как то так.
>
> select a.org_id, max(a.call_date) from call_log a
> left join table b on ((date>="01.04.2010")and(date<="30.
> 04.2010")) and a.org_id = b.id
> group by a.org_id

этот вариант не дает доступа к тектовым полям таблицы call_log, в принципе он вообще ничем не отличается от моего варианта...

← →
d@nger (2010-04-26 11:31) [25]

> Дмитрий С © (26.04.10 11:20) [22]
>
>
> > sniknik ©
>
> В условиях нет факта, что id и call_date одновременно возрастают.
>

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

← →
test © (2010-04-26 11:35) [26]

d@nger (26.04.10 11:29) [24]
Какой доступ нужен?

← →
d@nger (2010-04-26 11:37) [27]

у меня такая мысль возникла, если группировка работает с агрегатными функциями, нельзя ли написать свою агрегатную функцию, которая ничего не делает, а возвращает то же значение что в него и посупило, например: TXT("это текст") = это текст .... т.е сделав такю функцию мы сможем ее включить в параметры select и вывести отдельным столбцом

← →
test © (2010-04-26 11:37) [28]

select c.* from call_log left join (select a.org_id, max(a.call_date) from call_log a left join table b on ((date>="01.04.2010")and(date<="30.04.2010")) and a.org_id = b.id group by a.org_id) b on c.id = b.id

← →
test © (2010-04-26 11:39) [29]

d@nger (26.04.10 11:37) [27]
Все уже украдено до нас))

select c.*, "это мы напишем тута","а это мы напишем в тама" from call_log с

← →
d@nger (2010-04-26 11:39) [30]

> test © (26.04.10 11:35) [26]
>
> d@nger (26.04.10 11:29) [24]
> Какой доступ нужен?

помимо ORG_ID и CALL_DATE, есть текстовые поля ORG_NAME и TEXT, которые тоже нужно отобразить в результате запроса . если эти поля включить в select то придется и в группировку их включить, что повлияет на результат группировки

← →
test © (2010-04-26 11:41) [31]

d@nger (26.04.10 11:39) [30]
Проверь запрос из поста 28.

← →
d@nger (2010-04-26 11:45) [32]

> test © (26.04.10 11:41) [31]
>
> d@nger (26.04.10 11:39) [30]
> Проверь запрос из поста 28.

проверяю....
select * from call_log c left join (select a.org_id, max(a.call_date) from call_log a left join clients b on ((call_date>="01.04.2010")and(call_date<="30.04.2010")) and a.org_id = b.id group by a.org_id) b on c.id = b.id

ошибка...
Invalid token. Dynamic SQL Error. SQL error code = -104. Invalid command. no column name specified for column number 2 in derived table B.

← →
test © (2010-04-26 11:52) [33]

select * from call_log c left join (select a.org_id, max(a.call_date) max_date from call_log a left join clients b on ((call_date>="01.04.2010")and(call_date<="30.04.2010")) and a.org_id = b.id group by a.org_id) b on c.id = b.id

Ну назови поле как нибудь например max_date он же об этом говорит

← →
d@nger (2010-04-26 11:55) [34]

> Ну назови поле как нибудь например max_date он же об этом
> говорит

я не знаю куда его воткнуть... я sql плохо пока знаю

← →
test © (2010-04-26 11:58) [35]

я привел же написал куда и где

select * from call_log c left join (select a.org_id, max(a.call_date) max_date from call_log a left join clients b on ((call_date>="01.04.2010")and(call_date<="30.04.2010")) and a.org_id = b.id group by a.org_id) b on c.id = b.id

← →
d@nger (2010-04-26 12:03) [36]

сорри.. не увидел...

← →
d@nger (2010-04-26 12:11) [37]

теперь другая ошибка ...
select * from call_log c left join (select a.org_id, max(a.call_date) max_date from call_log a left join clients b on ((call_date>="01.04.2010")and(call_date<="30.04.2010")) and a.org_id = b.id group by a.org_id) b on c.id = b.id

ошибка ...

> Column does not belong to referenced table.
> Dynamic SQL Error.
> SQL error code = -206.
> Column unknown.
> B.ID.
> At line 5, column 34.

← →
sniknik © (2010-04-26 12:17) [38]

> у меня такая мысль возникла, если группировка работает с агрегатными функциями, нельзя ли написать свою агрегатную функцию
читай внимательно, и писать не будет ничего нужно...
> простейший вариант это включить поля в агрегатных функциях, аналогично call_date (ведь он то тебе не мешает группировать по org_id)

← →
test © (2010-04-26 12:17) [39]

ошибка означает нет такого поля
скорее всего это он про
c.id = b.id
b.id должно быть b.org_id
c.id нужно смотреть в названиях полей таблицы

в select * from тожке лучше указать что тебе нужно select c.* from, а то он тебе весь набор вытащит.

Смысл этих операций ты связать по полям id(идентификаторы) выбор из первого запроса и саму таблицу, в результате ты должен получить только те строки из таблицы которые соотвествуют внутреннему запросу.

Надо поля смотреть, может быть у тебя таких и нету в таблице.

← →
d@nger (2010-04-26 12:33) [40]

структура таблиц следующая:
таблица call_log
ID - ключ
ORG_ID - ключ организации клиента из таблицы clients.id
CALL_DATE - дата
TEXT - текст
ORG_NAME - текст

в таблице clients много полей... но поле ID точно есть... тоже ключевое...

← →
d@nger (2010-04-26 12:37) [41]

вобщем вот в таком виде ошибки нет.. но результат просто выводит все записи...
select c.*, org_name, text from call_log c left join (select a.org_id, max(a.call_date) max_date from call_log a left join clients g on (call_date between "01.04.2010" and "30.04.2010") and a.org_id = g.id group by a.org_id) b on c.id = b.org_id

← →
test © (2010-04-26 12:42) [42]

select * from call_log c left join (select a.org_id, max(a.call_date) max_date from call_log a left join clients b on ((call_date>="01.04.2010")and(call_date<="30.04.2010")) and a.org_id = b.id group by a.org_id) b on c.org_id = b.org_id and c.call_date = b.max_date

Я думал что org_id и есть идентификатор, тогда тебе связь нужна по всем полям результирующего набора

← →
test © (2010-04-26 12:47) [43]

d@nger (26.04.10 12:37) [41]
Тут ошибка, он по разным полям связь делает, в смысле он делает связь по ключу первой таблицы с ключем второй таблицы.

← →
d@nger (2010-04-26 12:49) [44]

> test © (26.04.10 12:42) [42]

этот запрос тоже выдает просто все записи таблицы

← →
test © (2010-04-26 13:04) [45]

d@nger (26.04.10 12:49) [44]
Не может быть чтобы все строки в таблице соответствовали c.org_id = b.org_id and c.call_date = b.max_date

← →
d@nger (2010-04-26 13:12) [46]

> Не может быть чтобы все строки в таблице соответствовали
> c.org_id = b.org_id and c.call_date = b.max_date

возможно это условие как то игнорируется фаербердом

← →
Anatoly Podgoretsky © (2010-04-26 13:14) [47]

> d@nger (26.04.2010 12:37:41) [41]

Видимо org_id, call_date и все записи только в интервале "01.04.2010" and "30.04.2010"

← →
d@nger (2010-04-26 13:18) [48]

> идимо org_id, call_date и все записи только в интервале
> "01.04.2010" and "30.04.2010"

ID ORG_ID CALL_DATE TEXT ORG_NAME
97 59 18.03.2010 gregreg Агропромипорт Холдинг
98 59 13.04.2010 ergregerg Агропромипорт Холдинг
99 59 21.04.2010 regergerg Агропромипорт Холдинг
100 59 28.04.2010 erger65ty546 Агропромипорт Холдинг
101 59 20.05.2010 6356356fvgfd Агропромипорт Холдинг
102 59 29.05.2010 ergerger Агропромипорт Холдинг
103 58 14.04.2010 retкуце ецкецкуе Администрация города
104 58 15.04.2010 цукецкуе ку5435 Администрация города
105 58 22.04.2010 е45243525епкупуц Администрация города

в поле TEXT я так просо аброкадабру написал на время разработки

d@nger (26.04.10 13:12) [46]
Это правило по которому он связывает, он его игнорировать не может.
Измени
select * from call_log c
На
select c.org_id, b.org_id, c.call_date, b.max_date from call_log c
И посмотри построчно.

> d@nger (26.04.2010 13:12:46) [46]

Да как он посмел

← →
d@nger (2010-04-26 13:23) [51]

вот сейчас выдал вот это :

ORG_ID ORG_ID1 CALL_DATE MAX_DATE 59 18.03.2010 59 13.04.201 0 59 21.04.2010 59 28.04.2010 59 20.05.2010 59 59 29.05.2010 29.05.2010 58 14.04.2010 58 15.04.2010 58 58 22.04.2010 22.04.2010

пустые значения null

← →
d@nger (2010-04-26 13:28) [52]

т.е. там где ORG_ID1 не пустое правильные ответы, теперь осталось только отсеять строки где оно равно null

d@nger (26.04.10 13:23) [51]

select * from call_log c right join (select a.org_id, max(a.call_date) max_date from call_log a left join clients b on ((call_date>="01.04.2010")and(call_date<="30.04.2010")) and a.org_id = b.id group by a.org_id) b on c.org_id = b.org_id and c.call_date = b.max_date

Помойму все дело в right join/left join.

← →
d@nger (2010-04-26 15:14) [54]

>
> Помойму все дело в right join/left join.

Ураа! действительно ... все заработало как надо.. спасибо огромное TEST ... намучились же вы со мной.. еще раз спасибо от души!

d@nger (26.04.10 15:14) [54]
Пожалуйста. Заодно сам обновил знания SQL.

test © (26.04.10 13:39) [53]
call_date>="01.04.2010"
1. нет alias
2. строка вместо даты
3. left join вместо join
4. последнюю запись на дату принято искать по индексу, а не агрегированием

плохие-плохие советы...

← →
d@nger (2010-04-26 19:21) [58]

рано что то я обрадовался... взял период за май.. где только одна организация должна выйти, т.к. по второй даты на май нет... все равно две организации выходит... неужели эту на первый взгляд тривиальную задачу так трудно решить?

← →
d@nger (2010-04-26 19:38) [59]

попробовал по совету Кщд заменить right join/left join на просто join ... и о чудо все стало работать как надо... еще убрал пару условий не влияющих на результат... итоговый результат следующий:
select c.id, c.org_id, c.call_date, c.org_name, c.text from call_log c join (select a.org_id, max(a.call_date) max_date from call_log a join call_log b on a.call_date between "01.03.2010" and "30.03.2010" group by a.org_id) d on c.call_date = d.max_date

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

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

> и о чудо все стало работать как надо...
до первых повторов дат

вот так попробуй
select org_id, call_date, text, org_name from call_log where id in ( select max(id) from call_log a inner join ( select org_id, max(call_date) as call_date from call_log where call_date between "01.05.2010" and "30.05.2010" group by org_id ) b on a.org_id=b.org_id and a.call_date=b.call_date group by a.org_id) c

не будет зависеть от того
> что id и call_date одновременно возрастают.

главное чтобы id был уникальным. и если в одной группе org_id будет 2 одинаковые даты то возьмется с большим id, хотя это неважно но что то же нужно выбирать.
в твоем запросе ([59]) даже не в диапазоне org_id, а во всем периоде совпадение дат приведет к дублям/"триблям"/"и т.д.блям" ;)

← →
d@nger (2010-04-26 20:46) [62]

← →
d@nger (2010-04-26 20:48) [63]

> главное чтобы id был уникальным

id ключевое автоинкрементное поле..

> тоже все работает нормально...
тогда оставь эту, эта больше учитывает...
вот забей в приведенном примере данных у всех записей одну и туже дату и проверь обе процедуры.

> процедуры.
блин, запроса.

← →
d@nger (2010-04-26 21:00) [66]

> вот забей в приведенном примере данных у всех записей одну
> и туже дату и проверь обе процедуры.

да... твоя корректно отработала... предыдущая показала дубли

спасибо sniknik

>test © (26.04.10 17:47) [57]
это что, приказ?)
мой пост был Вам, а не автору
если считаете мои советы дурными - не следуйте им)

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