rows like this:
id name version(int)
typeid_1 typename1 version1
typeid_1 typename1_a version2
typeid_2 typename2 version1
typeid_2 typename2_a version2
typeid_2 typename2_b version3
typeid_3 typename3 version1
typeid_3 typename3_f version2
now, I want to find all the latest version for each row, like this:
typeid_1 typename1_a version2
typeid_2 typename2_b version3
typeid_3 typename3_f version2
除了用cursor，还有别的简洁办法没有？
谢谢！
sth like: select id, name, max(version) from your_table group by 1,2 order by 1,2
-sammy(21 days);
2007-1-30(#3466771@0)
no, that won't work. you still get all the versions.
-luoboyang(萝卜秧);
2007-1-30(#3466778@0)
OK, I know what you want now...select id,max(version) from your_table group by 1order by 1 will work but won't show you the name. Let me zai see see...
-sammy(21 days);
2007-1-30(#3466788@0)
why cursor ? Max function and group by clause
-hard20(hard20);
2007-1-30(#3466785@0)
of course I tried this way first. :) #3466778@0
-luoboyang(萝卜秧);
2007-1-30(#3466794@0)
select distinct a.id, a.name, a.version from tbl a join (select id, max(version) as version from tbl group by id) b on a.id=b.id and a.version = b.version
-bdbs(不多不少);
2007-1-30(#3466810@0)
:)) Thanks! this one seems work fine! 顺便讨论一下，这种表的设计会有很大的performance问题啊，每个query都要自连接一下。
-luoboyang(萝卜秧);
2007-1-30(#3466833@0)
这个表不是我设计的。：））它意图就是track所有的变化纪录，如果for id1, the name becomes name_a, 就会自动insert a new row, and the version will increase 1. 所以，同一个id，决不会有同样的version，
-luoboyang(萝卜秧);
2007-1-30{88}(#3466953@0)
这样设计是有目的，当一个订单需要变动，很多XRef table都需要找回当初订的时候的样子。。。。
如果id name unique，可以把id & name看作一个field，就不用join了。select cast(id as char(10)) + name, max(version) from tbl group by cast(id as char(10)) + name。咦？你老兄怎么也玩起这个来了？
-bdbs(不多不少);
2007-1-30(#3467116@0)
select typeid,typename,version from table group by typeid, typename having version=max(version)
-beware(十二月的蚂蚱);
2007-1-30(#3466811@0)
no, I also tried this way. thanks.
-luoboyang(萝卜秧);
2007-1-30(#3466823@0)
maybe this:
select * from table1 where version in (select max(version) from table1)
-yangn(Raymond);
2007-1-30(#3466841@0)
I don't think so.
-sammy(21 days);
2007-1-30(#3466845@0)
no, since 'select max(version) from table1' will only get one max version in the whole table, not max version for each id.
-luoboyang(萝卜秧);
2007-1-30(#3466850@0)
how about this:
select id,version from test1 td group by id,version
having version=(select max(version) from test1 te where te.id=td.id)
-yuanzidan(原子弹);
2007-1-30(#3466919@0)
my answer
-newkid(newkid);
2007-1-30{305}(#3467019@0)
SELECT DISTINCT FIRST_VALUE(id) OVER (PARTITION BY id ORDER BY version DESC)
,FIRST_VALUE(name) OVER (PARTITION BY id ORDER BY version DESC)
,FIRST_VALUE(version) OVER (PARTITION BY id ORDER BY version DESC)
FROM my_versions;