| |

VerySource

 Forgot password?
 Register
Search
View: 690|Reply: 7

Please help me write SQL

[Copy link]

1

Threads

2

Posts

3.00

Credits

Newbie

Rank: 1

Credits
3.00

 China

Post time: 2020-1-20 09:00:02
| Show all posts |Read mode
id seq_no value
1 2 B
1 1 A
2 1 C
3 3 F
3 2 E
3 1 D
4 1 G

Now I want to get the line with the largest seq_no corresponding to id. How do I get it?
Reply

Use magic Report

2

Threads

9

Posts

8.00

Credits

Newbie

Rank: 1

Credits
8.00

 China

Post time: 2020-1-28 13:54:01
| Show all posts
select *
from (select id, max (seq_no) as seq_no
     from tb
     group by id) a
join tb b on a.id = b.id and b.seq_no = a.seq_no
Reply

Use magic Report

0

Threads

3

Posts

3.00

Credits

Newbie

Rank: 1

Credits
3.00

 China

Post time: 2020-1-28 14:09:01
| Show all posts
select * from table name A where not exists (select 1 from table name where seq_no> A.seq_No)
Reply

Use magic Report

0

Threads

66

Posts

27.00

Credits

Newbie

Rank: 1

Credits
27.00

 China

Post time: 2020-1-28 14:27:02
| Show all posts
select id, max (seq_no) as seq_no from t group by id?
Reply

Use magic Report

0

Threads

211

Posts

108.00

Credits

Newbie

Rank: 1

Credits
108.00

 China

Post time: 2020-1-28 15:00:01
| Show all posts
select id, seq_no = max (seq_no) from tbName group by id
Reply

Use magic Report

0

Threads

66

Posts

27.00

Credits

Newbie

Rank: 1

Credits
27.00

 China

Post time: 2020-1-28 15:45:01
| Show all posts
select * from table t where not exists (select * from table where id = t.id and seq_no> t.seq_No)
Reply

Use magic Report

0

Threads

211

Posts

108.00

Credits

Newbie

Rank: 1

Credits
108.00

 China

Post time: 2020-1-28 16:27:01
| Show all posts
create table T (id int, seq_no int, value char (1))
insert T select 1, 2, 'B'
union all select 1, 1, 'A'
union all select 2, 1, 'C'
union all select 3, 3, 'F'
union all select 3, 2, 'E'
union all select 3, 1, 'D'
union all select 4, 1, 'G'
--1
select id, max (seq_no) as seq_no from t group by id

--2
select * from T as A
where not exists (select 1 from T where id = A.id and seq_no> A.seq_no)
Reply

Use magic Report

1

Threads

2

Posts

3.00

Credits

Newbie

Rank: 1

Credits
3.00

 China

 Author| Post time: 2020-1-28 18:09:01
| Show all posts
Thank you all
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