SQL запрос

[uniqueman]

Есть две таблицы

Table 1:

ID Name
-----------------
1 JP
2 ML


table 2:

ID Sum Time
-------------------------------
1 10 9/10/2003
1 20 10/10/2003
2 30 9/10/2003
2 40 10/10/2003

Надо выбрать ID и SUM всех записей которые есть в Table 1 и Time котороых во второй таблице самое последнее.. То есть результат должен быть

ID Sum
-------------
1 20
2 40

Как это сделать в одном запросе ?

Спсибо

[Dmitry67]

select Table2.ID,Table2.Sum
from Table2, ((select ID,max(Time) as MaxTime from Table2 group by ID)) MAXI
where MAXI.ID=Table2.ID and MAXI.MaxTime=Table2.Time
and exists(select * from Table1 where ID=Table2.ID)

Не проверял

[uniqueman]

спасибо, работает. Но так долго думает Секунды три, прежде чем результат выдать. В чем могут быть грабли?

[hb]

Вместо Table 1 я пишу T1, вместо Table 2 -- T2, вместо ID -- id.

Code: Select all

SELECT T2.id, T2.Sum
FROM T1, T2
WHERE T1.id=T2.id AND
      T2.Time=(SELECT MAX(Time) FROM T2 T WHERE T.id=T1.id)

Работает, проверено. Но, думаю, с той же самой скоростью. Проверяйте.

[uniqueman]

он так выдаст информацию только под одному ID, у которого самое последнее время. А надо чтобы выдало по всем, которые находятся в таблице T1

[tengiz]

спасибо, работает. Но так долго думает Секунды три, прежде чем результат выдать. В чем могут быть грабли?

Много ли строк в Table2 где ID не содержится в Table1? Что с индексами? Попробуйте ещё вот так:

Code: Select all

select table2.ID, table2.Time, table2.Sum 
from   table2 join
       (
          select table2.ID, max (Time) as MaxTime
          from   table2 join table1 on table2.ID = table1.ID
          group  by table2.ID
       )  rs
       on table2.ID = rs.ID and table2.Time = rs.MaxTime

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

[vc]

спасибо, работает. Но так долго думает :cry: Секунды три, прежде чем результат выдать. В чем могут быть грабли?

Много ли строк в Table2 где ID не содержится в Table1? Что с индексами? Попробуйте ещё вот так:

Code: Select all

select table2.ID, table2.Time, table2.Sum 
from   table2 join
       (
          select table2.ID, max (Time) as MaxTime
          from   table2 join table1 on table2.ID = table1.ID
          group  by table2.ID
       )  rs
       on table2.ID = rs.ID and table2.Time = rs.MaxTime

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

Two more ;)

Code: Select all

select t2.id, t2.sum,t2.time 
from t1,t2, (select id, max(time) as time from t2 group by id)  t3
where t1.id=t2.id
  and t2.id=t3.id
  and t2.time=t3.time

..and..

Code: Select all

select t2.id, t2.sum,t2.time 
from t1,t2 
where t1.id=t2.id
  and (t2.id, t2.time) in (select id, max(time) from t2 group by id)

I am not sure the last one is valid for MS Server (it's '92 compliant, though).

All the suggested solutions should generate the same exec. plan given good statistics -- they're variations on the same theme.

Rgds.

[tengiz]

The last one is not valid for SQL Server because we don't support row constructors yet. As of the first one - it is valid for MSSQL, however, a subquery in select list is not ANSI/ISO compliant, btw .

[vc]

The last one is not valid for SQL Server because we don't support row constructors yet. As of the first one - it is valid for MSSQL, however, a subquery in select list is not ANSI/ISO compliant, btw .

I must be tired but the subquery is in the FROM list, not in the select list. Probably you meant

select x, (select count(*) from t2 where t1.id=t2.id) from t1

I believe this (the scalar subquery) is also allowed by SQL-92.

Rgds.

[tengiz]

Ouch! Yes, of course, you're right, except I'm the tired one...

[vc]

Ouch! Yes, of course, you're right, except I'm the tired one...

Thank you. I got confused for a while and I do not have the standard at home .

Rgds.

[tengiz]

...I do not have the standard at home .

Just not to confuse you anymore: a subquery in select list - the standart still doesn't allow it.

[hb]

он так выдаст информацию только под одному ID, у которого самое последнее время. А надо чтобы выдало по всем, которые находятся в таблице T1

Это вы про мой запрос? Не понимаю, о чем вы. По-моему, все правильно: мой запрос выдаст по одной записи для всех записей из T1 INNER JOIN T2 ON T1.id=T2.id.

Code: Select all

1> CREATE TABLE T1 (id INTEGER NOT NULL, Name VARCHAR(10) NOT NULL);
2> INSERT INTO T1 VALUES(1, 'JP');
3> INSERT INTO T1 VALUES(2, 'ML');
4> CREATE TABLE T2 (id INTEGER NOT NULL, Sum FLOAT NOT NULL, Time SMALLDATETIME NOT NULL);
5> INSERT INTO T2 VALUES(1, 10, '9 Oct 2003');
6> INSERT INTO T2 VALUES(1, 20, '10 Oct 2003');
7> INSERT INTO T2 VALUES(2, 30, '9 Oct 2003');
8> INSERT INTO T2 VALUES(2, 40, '10 Oct 2003');
9> GO
(1 row affected)
(1 row affected)
(1 row affected)
(1 row affected)
(1 row affected)
(1 row affected)
1> SELECT * FROM T1;
2> SELECT * FROM T2;
3> GO
 id          Name
 ----------- ----------
           1 JP
           2 ML

(2 rows affected)
 id          Sum                  Time
 ----------- -------------------- --------------------
           1            10.000000     9 окт 2003  0:00
           1            20.000000    10 окт 2003  0:00
           2            30.000000     9 окт 2003  0:00
           2            40.000000    10 окт 2003  0:00

(4 rows affected)

1> SELECT T2.id, T2.Sum
2> FROM T1, T2
3> WHERE T1.id=T2.id AND
4>       T2.Time=(SELECT MAX(Time) FROM T2 T WHERE T.id=T1.id)
5> ORDER BY 1;
6> GO
 id          Sum
 ----------- --------------------
           1            20.000000
           2            40.000000

(2 rows affected)

(Я добавил лишь сортировку по id для удобства). Запрос возвращает именно то, что хотел автор этого треда, не так ли?

[hb]

мой запрос выдаст по одной записи для всех записей из T1 INNER JOIN T2 ON T1.id=T2.id

Я хотел сказать, по одной записи для каждого id из T1 INNER JOIN T2 ON T1.id=T2.id

На результат эти нюансы формулировки не влияют: запрос делает то, что надо. Если я ошибаюсь, пожалуйста, контрпример данных на к-рых запрос вернет не то, что надо.

[vc]

...I do not have the standard at home .

Just not to confuse you anymore: a subquery in select list - the standart still doesn't allow it.

tengiz,

According to the Mimer validator (http://developer.mimer.com/validator/pa ... tml#parser):

select (select count(*) from t1) from t2;

For '92:
*** Intermediate SQL-92 ***
For '99:
Conforms to Core SQL-99

Rgds.

P.S. From '92:

<select sublist> := <derived column>
<derived column> ::= <value expression>
<value expression primary> ::=
<unsigned value specification>
| <column reference>
| <set function specification>
| <scalar subquery>