看下谁的sql简便，大家参与哈
表
select * from test 结果如下：

namecity_namescan_start_times_hourvalue
CDRNC01常市2011-5-2 17:00172822680.5
CDRNC01常市2011-5-2 9:0091805635
CDRNC01常市2011-5-1 12:00122209981.125
CDRNC01常市2011-5-1 17:00172925668.75
CDRNC01常市2011-5-1 3:003249131
CSRNC05天区2011-5-10 204877610
CSRNC05天区2011-5-2 19:001917483989.82
CSRNC05天区2011-5-2 21:002120235228.57
CSRNC06开区2011-5-1 14:00147548153.242
CSRNC06开区2011-5-1 18:00186533271.611
CSRNC06开区2011-5-1 2:0022497404.404
CSRNC06开区2011-5-1 4:0041080070.781
CSRNC06开区2011-5-2 16:00167427590.879
CSRNC06开区2011-5-2 17:00178141454.531

实现效果(每个城市下面value值最大的记录)：
CDRNC01常市2011-5-1 122209981.125
CSRNC05天区2011-5-1 0204877610
CSRNC06开区2011-5-1 147548153.242
CSRNC06开福区2011-5-2 178141454.531
或
CDRNC01常市2011-5-1 12:00122209981.125
CSRNC05天区2011-5-1 0204877610
CSRNC06开区2011-5-1 14:00147548153.242
CSRNC06开福区2011-5-2 17:00178141454.531

现在知道一种sql 如下：
select a.* from testa,(select city_name,max(value) from test group by city_name,day(scan_start_time)) b where a.city_name=b.city_name and a.value=b.value

大家 都出下意见，看还有什么好的sql。。。。。




[解决办法]

SQL code

select name，city_name，scan_start_time，s_hour，value dense_rank() over(partition by city_name order by value desc)
[解决办法]
SQL code
select *  from (select t.*,                row_number() over(partition by city_name, day(scan_start_time) order by value desc) as line          from test t) where line = 1;