B****a
发帖数: 154 | 1 new to SQL, please help...thanks!
2 tables join, A left join B
A has id column
B has id and date columns
in table B, each id has multiple rows of different dates
I need to get the MAX date for each id
and link back to table A
how to do this in T-SQL...?
thanks a lot... |
B*****g
发帖数: 34098 | 2 group by
【在 B****a 的大作中提到】
: new to SQL, please help...thanks!
: 2 tables join, A left join B
: A has id column
: B has id and date columns
: in table B, each id has multiple rows of different dates
: I need to get the MAX date for each id
: and link back to table A
: how to do this in T-SQL...?
: thanks a lot...
|
B****a
发帖数: 154 | 3 but I need to select a lot more fields from other tables as well, how to
just select and group by ids from table B and get a new field of MAX date?
【在 B*****g 的大作中提到】
: group by
|
B*****g
发帖数: 34098 | 4 why do you want to have group by on table B instead of on table A?
【在 B****a 的大作中提到】
: but I need to select a lot more fields from other tables as well, how to
: just select and group by ids from table B and get a new field of MAX date?
|
B****a
发帖数: 154 | 5 coz only table B has the date variable I need for each id...
【在 B*****g 的大作中提到】
: why do you want to have group by on table B instead of on table A?
|
B*****g
发帖数: 34098 | 6 group by is after join.
below is oracle sql, should be ok for sql server.
SELECT a.id, MAX (b.date)
FROM tab_a a LEFT OUTER JOIN tab_b b ON a.id = b.id
GROUP BY a.id
【在 B****a 的大作中提到】
: coz only table B has the date variable I need for each id...
|
B****a
发帖数: 154 | 7 谢谢。。。
我试了，但是不work
我一共有三个tables
就是说，A left join B on id
A left join C on seq (another field)
我要B里面的date的MAX
和A里的id，还有C里的一些其他的fields
我晕了。。。
【在 B*****g 的大作中提到】
: group by is after join.
: below is oracle sql, should be ok for sql server.
: SELECT a.id, MAX (b.date)
: FROM tab_a a LEFT OUTER JOIN tab_b b ON a.id = b.id
: GROUP BY a.id
|
B*****g
发帖数: 34098 | 8 You did not mention table C before.
SELECT d.ID, d.maxdate, c.*
FROM (SELECT a.id, a.seqno, MAX (b.date) maxdate
FROM tab_a a LEFT OUTER JOIN tab_b b ON a.id = b.id
GROUP BY a.id, a.seqno) d LEFT OUTER JOIN tab_c c ON d.
seqno = c.seqno
【在 B****a 的大作中提到】
: 谢谢。。。
: 我试了，但是不work
: 我一共有三个tables
: 就是说，A left join B on id
: A left join C on seq (another field)
: 我要B里面的date的MAX
: 和A里的id，还有C里的一些其他的fields
: 我晕了。。。
|
B****a
发帖数: 154 | 9 thanks a lot! but I am a little confused here...the d table in select
statement should be a?
【在 B*****g 的大作中提到】
: You did not mention table C before.
: SELECT d.ID, d.maxdate, c.*
: FROM (SELECT a.id, a.seqno, MAX (b.date) maxdate
: FROM tab_a a LEFT OUTER JOIN tab_b b ON a.id = b.id
: GROUP BY a.id, a.seqno) d LEFT OUTER JOIN tab_c c ON d.
: seqno = c.seqno
|
B*****g
发帖数: 34098 | 10 d is the join of a and b
【在 B****a 的大作中提到】
: thanks a lot! but I am a little confused here...the d table in select
: statement should be a?
|
|
|
B****a
发帖数: 154 | 11 I C
thanks a lot! you are so nice! :)
【在 B*****g 的大作中提到】
: d is the join of a and b
|
c*******e
发帖数: 8624 | 12 you can do a qualify here
select a.whatever ,
b.whatever
from table_a a
left outer join table_b b
on a.id = b.id
qualify row_number() over (partition by a.id order by b.dt desc) = 1
【在 B****a 的大作中提到】
: but I need to select a lot more fields from other tables as well, how to
: just select and group by ids from table B and get a new field of MAX date?
|
m**********2
发帖数: 2252 | 13 我用self join,
SELECT a.*,b.*, c.* (--whatever field you want)
FROM tbla a,tblb b1,tblc c
where a.id = b1.id
and a.seqno = c.seqno
and b1.date =
(SELECT MAX (b2.date) from tblb b2
where b2.id = b1.id
group by b2.id)
【在 B****a 的大作中提到】
: 谢谢。。。
: 我试了，但是不work
: 我一共有三个tables
: 就是说，A left join B on id
: A left join C on seq (another field)
: 我要B里面的date的MAX
: 和A里的id，还有C里的一些其他的fields
: 我晕了。。。
|
c*******e
发帖数: 8624 | 14 如果table b里面有不止一个max(date)的话,就会出现多个结果
【在 m**********2 的大作中提到】
: 我用self join,
: SELECT a.*,b.*, c.* (--whatever field you want)
: FROM tbla a,tblb b1,tblc c
: where a.id = b1.id
: and a.seqno = c.seqno
: and b1.date =
: (SELECT MAX (b2.date) from tblb b2
: where b2.id = b1.id
: group by b2.id)
|
B*****g
发帖数: 34098 | 15 1. 虽然人家要left join
2. 虽然你的performance更好
但是，原题说。。。
in table B, each id has multiple rows of different dates
【在 c*******e 的大作中提到】
: 如果table b里面有不止一个max(date)的话,就会出现多个结果
|
c*******e
发帖数: 8624 | 16 multiple rows of differnt dates我很怀疑LZ具体想说什么
【在 B*****g 的大作中提到】
: 1. 虽然人家要left join
: 2. 虽然你的performance更好
: 但是，原题说。。。
: in table B, each id has multiple rows of different dates
|
