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>

[uniqueman]

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

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

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

Вы абсолютно правы. Я просто подумал что символ T в предикате означает Table 1

[hb]

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

Вы абсолютно правы. Я просто подумал что символ T в предикате означает Table 1

Символ T означал "обзовем таблицу T2 в подзапросе как-нибудь, скажем, T, чтобы не путать с T2 из основного запроса". Я думал, что это делает запрос нагляднее. Но если наоборот, то давайте T просто на фиг уберем :

Code: Select all

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

Результат тот же самый.

[tengiz]

vc, it's different. You're referring to a subquery as a table expression, which is perfectly valid. What I meant is the following:

select pk, a, b, (select c from tab2 where tab2.pk = tab1.pk) as c from tab1

Provided that the subquery returns one column rowset with no more that one row, this is valid for SQL Server; however, is not standard complaint. Here is the equivalent ANSI SQL query:

select tab1.pk, tab1.a, tab1.b, tab2.c from tab1 left join tab2 on tab1.pk = tab2.pk

[vc]

tengiz,

vc, it's different. You're referring to a subquery as a table expression, which is perfectly valid. What I meant is the following:

select pk, a, b, (select c from tab2 where tab2.pk = tab1.pk) as c from tab1

Provided that the subquery returns one column rowset with no more that one row, this is valid for SQL Server; however, is not standard complaint. .........

I beg to differ (not being obnoxious, just willing to clarify ;) ):

From the '92 standard:

"Syntax Rules

1) The degree of a <scalar subquery> shall be 1
..................
"
and

"General Rules

1) If the cardinality of a <scalar subquery> or a <row subquery> is
greater than 1, then an exception condition is raised: cardinal-
ity violation.
"
and elsewhere in the standard:

"X3H2-92-154/DBL CBR-002
6.11 <value expression>


General Rules

.............................
2) If a <value expression primary> is a <scalar subquery> and the
result of the <subquery> is empty, then the result of the <value
expression primary> is the null value.

.........................
Leveling Rules

1) The following restrictions apply for Intermediate SQL:

None.

2) The following restrictions apply for Entry SQL in addition to
any Intermediate SQL restrictions:

........................................
e) A <value expression primary> shall not be a <scalar subquery>
except when the <value expression primary> is simply con-
tained in a <value expression> that is simply contained in
the second <row value constructor> of a <comparison predi-
cate>.
"
To sum up, a scalar subquery can be used anywhere a <value expression> is used, in particular in the select list.

Therefore, if the subquery satisfies the above rules, your query (select pk, a, b, (select c from tab2 where tab2.pk = tab1.pk) as c from tab1 )
is ANSI compatible ('92 intermediate) because (select c from tab2 where tab2.pk = tab1.pk) is a <value expression>.

Rgds.

[tengiz]

vc, I've just looked into the SQL99 final doc - you're right. They've relaxed the restriction I was referring to. Anyway, I should have been more careful - it was the older stadard (a namely - SQL89) that didn't allow correlated subqueries in a select list. Thanks a lot!

[vc]

tengiz,

vc, I've just looked into the SQL99 final doc - you're right. They've relaxed the restriction I was referring to. Anyway, I should have been more careful - it was the older stadard (a namely - SQL89) that didn't allow correlated subqueries in a select list. Thanks a lot!

Thank you -- it's always a pleasure ...

Rgds.

[Dedal]

Вот пара вариантов:

1) Со связанным подзапросом. Такой, в общем-то, уже был:

Code: Select all

select N.ID, N.NAME, V2.SUM, V2.TIME from TABLE1 N, TABLE2 V2
where 
  N.ID=V2.ID
  and
  V2.TIME = (SELECT MAX(V1.TIME) from TABLE2 V1 where V1.ID = V2.ID);

2) С группировкой и агрегатной функцией:

Code: Select all

select N.ID, N.NAME, V2.SUM, MAX(V2.TIME) from TABLE1 N, TABLE2 V2, TABLE2 V1
where N.ID = V2.ID AND V2.ID = V1.ID
group by N.ID, N.NAME, V2.SUM
having MAX(V2.TIME) = MAX(V1.TIME);