我想实现以下功能,就是如果第一列如果有重复的,就按第二列较大的记录来取。该怎么写此sql语句如数据库表有以下记录
a a
a b
b b
b c
b d
c c
c d
d d我想得到的结果是
a b
b d
c d
d d
这样子表达不知是否清楚,希望各位可以帮我这位新手啊
a a
a b
b b
b c
b d
c c
c d
d d我想得到的结果是
a b
b d
c d
d d
这样子表达不知是否清楚,希望各位可以帮我这位新手啊
select tt.*,row_number()over(partition by col1 order by col2 desc)rn
from tt)
where rn=1
2. insert into t_test ('a','a');...插入所有值。
3.
select A,max(B) from t_test group By A order by A;
但如果要按这个规则显示出所有的列,可以用我写的代码
将select col1,col2..换成select * ...
显示所有字段
where not exists (select 1 from tablename t2 where t2.c1 = t1.c1 and t2.c2 > t1.c2);
好像不行呢
where not exists (select t2.c2 from tablename t2 where t2.c1 = t1.c1 and t2.c2 > t1.c2);这样写不行?把1换成其中一个字段后应该是通用sql吧
select tt.*,row_number()over(partition by col1 order by col2 desc)rn
from table)
where rn=1
1楼的正确,简单!
1.select column1, max(column2) from tablename group by column1;
2.select col1,col2 from(
select tt.*,row_number()over(partition by col1 order by col2 desc)rn
from table)
where rn=1
1.select column1, max(column2) from tablename group by column1;
2.select col1,col2 from(
select tt.*,row_number()over(partition by col1 order by col2 desc)rn
from table)
where rn=1
from (select first,last,row_number()over(partition by first order by last desc) rn from cc) t
where rn=1;这是我给的答案,刚看到,这个跟LZ要的结果一样而
select first,max(last) from cc group by first;
出的结果中d d在最上面
from(
select *,row_number()over(partition by col1 order by col2 desc) r
from table)
where r=1
-----------------
这个我以前也经常用。