Delete the largest value of the same data in a table, come and help me!

hzicbc

For example table aa:
aa bb cc
1 255 20161212
1 15 20161210
2 33 20160612
2 11 20160101
Sorted by DESC by cc,
For example, I want to delete the latest time data of where aa='2'or other middle places. Thank you!

waxiaoning

delete a from tablename a where cc=(select max(cc) from tablename where aa=a.aa)

我是杀人狂

delete aa from tablename where cc=(select max(cc) from tablename group by aa)

hzicbc

For example table aa:
aa bb cc
1 255 20161212
1 15 20161210
2 33 20160612
2 11 20160101
Sorted by DESC by cc,
For example, I want to delete the latest time data in where aa='2'or other places.
Result table aa:
aa bb cc
1 255 20161212
2 33 20160612

Thank you!

marsbee

- Your topic is not clear, and I am too lazy to speculate here, although the following generations
- The code may not give you the answer, but it can give you an idea.

declare @aa table (
aa int,
bb int,
cc char(8)
)

insert into @aa (aa, bb, cc)
select 1, 255, '20161212' union all
select 1, 15, '20161210' union all
select 2, 33, '20160612' union all
select 2, 11, '20160101'

--/Before performing the delete operation, you must perform the select operation, first see if the deletion is correct.
select *
from @aa a
where cc = (
select max(cc)
from @aa
where aa = a.aa
)

- After confirming that the previous step is correct, execute the next step to delete.
delete a
from @aa a
where cc = (
select max(cc)
from @aa
where aa = a.aa
)

- View the result after deletion
select * from @aa

marsbee

- Send you a piece of code, maybe you need it.

declare @user table (user_name varchar(20), join_dt datetime)

insert into @user (user_name, join_dt)
select'Zhang Yi', '2017-01-01 01:30:00' union all
select'Zhang Er', '2017-01-01 01:50:00' union all
select'Zhang San', '2017-01-01 02:30:00' union all
select'Zhang Si', '2017-01-01 21:30:00' union all

select'李一', '2017-01-02 01:30:00' union all
select'李二', '2017-01-02 03:30:00' union all
select'李三', '2017-01-02 07:30:00' union all
select'李四', '2017-01-02 09:30:00' union all
select'李五', '2017-01-02 10:30:00'


--/Query the top two users daily
select *
from @user a
where join_dt <= any (
select top 2 join_dt
from @user
where convert(varchar(10), join_dt, 112) = convert(varchar(10), a.join_dt, 112)
order by join_dt
)