| |

VerySource

 Forgot password?
 Register
Search
View: 921|Reply: 5

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

[Copy link]

2

Threads

3

Posts

4.00

Credits

Newbie

Rank: 1

Credits
4.00

 Spain

Post time: 2020-11-30 22:00:02
| Show all posts |Read mode
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!
Reply

Use magic Report

0

Threads

48

Posts

30.00

Credits

Newbie

Rank: 1

Credits
30.00

 China

Post time: 2020-11-30 22:15:01
| Show all posts
delete a from tablename a where cc=(select max(cc) from tablename where aa=a.aa)
Reply

Use magic Report

0

Threads

114

Posts

69.00

Credits

Newbie

Rank: 1

Credits
69.00

 China

Post time: 2020-12-1 00:00:01
| Show all posts
delete aa from tablename where cc=(select max(cc) from tablename group by aa)
Reply

Use magic Report

2

Threads

3

Posts

4.00

Credits

Newbie

Rank: 1

Credits
4.00

 China

 Author| Post time: 2020-12-1 01:00:01
| Show all posts
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!
Reply

Use magic Report

0

Threads

3

Posts

3.00

Credits

Newbie

Rank: 1

Credits
3.00

 China

Post time: 2020-12-1 10:45:01
| Show all posts
- 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
Reply

Use magic Report

0

Threads

3

Posts

3.00

Credits

Newbie

Rank: 1

Credits
3.00

 China

Post time: 2020-12-1 11:00:01
| Show all posts
- 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
)
Reply

Use magic Report

You have to log in before you can reply Login | Register

Points Rules

Contact us|Archive|Mobile|CopyRight © 2008-2023|verysource.com ( 京ICP备17048824号-1 )

Quick Reply To Top Return to the list