MSSQLSERVER2000: 1.select * from tablename order by col1,col2 desc 2.select top 1 from tablename order by col3 3.select * from tablename where col4=(select top n max(col4) from tablename order by col4)
3.或者 SET ROWCOUNT N SELECT * FROM TABLENAME WHERE COL5=(SELECT MAX(COL5) FROM TABLENAME ORDER BY COL5)
可怜,人家是说group by 以后的吗~
1.select col1,col2 from tablename group by col1,col2 order by col1,col2 desc 2.select top 1 col1,col2 from tablename group by col1,col2 order by col1,col2 desc 3.自己改改吧...
select a.* from tablename a,( select max(col2) col2,col3 from tablename group by col3) b where a.col2=b.col2 and a.col3=b.col3
不好意思,午休: select * from tab t1 where exists( select * from (select col1,max(col2) as col2 from tab group by col1) t2 where t1.col1=t2.col1 and t1.col2=t2.col2) 按col1分组,col2取最大
to hle(老大) :select * from YourTable t1 where b=(select max(b) from YourTable t2 where t1.a=t2.a)当某组的b最大的记录不之一条时,上述结果将不会是每组一条。例如,当a=1时,b的最大值为2,有两条记录:(1,2,2)、(1,2,1)。
这种问题在做学生成绩管理时会碰上:求每班成绩最好的学生:select * from StudentScore t1 where t1.Score=(select max(Score) from StrudentScore t2 where t1.Class=t2.Class)与group by无关!
1.select * from tablename order by col1,col2 desc
2.select top 1 from tablename order by col3
3.select * from tablename where col4=(select top n max(col4) from tablename order by col4)
SET ROWCOUNT N
SELECT * FROM TABLENAME WHERE COL5=(SELECT MAX(COL5) FROM TABLENAME ORDER BY COL5)
2.select top 1 col1,col2 from tablename group by col1,col2 order by col1,col2 desc
3.自己改改吧...
现在我想按照a分组,其中每组内的记录都分别先按照b排序,
然后取每组里b最大时的那条记录(abc三个字段的值都要)
另外关键是你这样取最来只有一条记录呀,我需要取每组的一条记录(COL2最大的那条),分几组就应该得到几条记录
另外关键是你这样取最来只有一条记录呀,我需要取每组的一条记录(COL2最大的那条),分几组就应该得到几条记录
select max(col2) col2,col3 from tablename group by col3) b
where a.col2=b.col2 and a.col3=b.col3
select * from tab t1 where exists( select * from
(select col1,max(col2) as col2 from tab group by col1) t2
where t1.col1=t2.col1 and t1.col2=t2.col2)
按col1分组,col2取最大
from YourTable t1
where b=(select max(b) from YourTable t2 where t1.a=t2.a)当某组的b最大的记录不之一条时,上述结果将不会是每组一条。例如,当a=1时,b的最大值为2,有两条记录:(1,2,2)、(1,2,1)。
from StudentScore t1
where t1.Score=(select max(Score) from StrudentScore t2 where t1.Class=t2.Class)与group by无关!