Topic: 问大家一个sql server得问题，能否帮我看看是否有更快更简洁的方法： @佛州华人论坛:佛州枫下论坛 The Rolia Forum of Florida

工作学习 / 专业技术讨论 / 问大家一个sql server得问题，能否帮我看看是否有更快更简洁的方法： -luoboyang(萝卜秧); 2007-1-30 {592} (#3466753@0)

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)

(#3466882) ：）不光performance的问题，如果name不一致（常有的事，改名了），输出结果会重复。比方ypeid_1 typename1 version2, typeid_1 typename1_a version2。建议名字分开建表。 -bdbs(不多不少); 2007-1-30 (#3466912@0)

这个表不是我设计的。：））它意图就是track所有的变化纪录，如果for id1, the name becomes name_a, 就会自动insert a new row, and the version will increase 1. 所以，同一个id，决不会有同样的version，这样设计是有目的，当一个订单需要变动，很多XRef table都需要找回当初订的时候的样子。。。。 -luoboyang(萝卜秧); 2007-1-30 {88} (#3466953@0)

如果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)

是搞错了，因为LZ接我话接错了。本来是担心会发生一个ID对应多个Name的情况，LZ的回答是否定的，俺没仔细看贴，以为说Name和ID一一对应，不会有一个ID对应多个不同名字的情况。:-( -bdbs(不多不少); 2007-1-30 (#3467197@0)

select typeid,typename,version from table group by typeid, typename having version=max(version) -beware(十二月的蚂蚱); 2007-1-30 (#3466811@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)

这什么破表哇 -xanada(㊣流水); 2007-1-30 (#3466882@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 answerSELECT 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;

这是ORACLE的函数，SQLSERVER应该有对应的。这样省去了表连接。 -newkid(newkid); 2007-1-30 {305} (#3467019@0)
另一种方法(传统SQL)SELECT id, SUBSTR(MAX(LPAD(version,10)||name),11),MAX(version)
FROM my_versions
GROUP BY id;

这里假设version是数值型，LPAD是为了让它们右对齐。||是ORACLE 的字符串连接符，大概相当于+号. -newkid(newkid); 2007-1-30 {191} (#3467062@0)

@Florida