-
Notifications
You must be signed in to change notification settings - Fork 0
/
Window functions.txt
119 lines (87 loc) · 5.47 KB
/
Window functions.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
create database win_fun
use win_fun
create table ineuron_students(
student_id int ,
student_batch varchar(40),
student_name varchar(40),
student_stream varchar(30),
students_marks int ,
student_mail_id varchar(50))
insert into ineuron_students values(119 ,'fsbc' , 'sandeep','ECE',65,'[email protected]')
select * from ineuron_students
insert into ineuron_students values(100 ,'fsda' , 'saurabh','cs',80,'[email protected]'),
(102 ,'fsda' , 'sanket','cs',81,'[email protected]'),
(103 ,'fsda' , 'shyam','cs',80,'[email protected]'),
(104 ,'fsda' , 'sanket','cs',82,'[email protected]'),
(105 ,'fsda' , 'shyam','ME',67,'[email protected]'),
(106 ,'fsds' , 'ajay','ME',45,'[email protected]'),
(106 ,'fsds' , 'ajay','ME',78,'[email protected]'),
(108 ,'fsds' , 'snehal','CI',89,'[email protected]'),
(109 ,'fsds' , 'manisha','CI',34,'[email protected]'),
(110 ,'fsds' , 'rakesh','CI',45,'[email protected]'),
(111 ,'fsde' , 'anuj','CI',43,'[email protected]'),
(112 ,'fsde' , 'mohit','EE',67,'[email protected]'),
(113 ,'fsde' , 'vivek','EE',23,'[email protected]'),
(114 ,'fsde' , 'gaurav','EE',45,'[email protected]'),
(115 ,'fsde' , 'prateek','EE',89,'[email protected]'),
(116 ,'fsde' , 'mithun','ECE',23,'[email protected]'),
(117 ,'fsbc' , 'chaitra','ECE',23,'[email protected]'),
(118 ,'fsbc' , 'pranay','ECE',45,'[email protected]'),
(119 ,'fsbc' , 'sandeep','ECE',65,'[email protected]')
select * from ineuron_students
select student_batch ,sum(students_marks) from ineuron_students group by student_batch
select student_batch ,min(students_marks) from ineuron_students group by student_batch
select student_batch ,max(students_marks) from ineuron_students group by student_batch
select student_batch ,avg(students_marks) from ineuron_students group by student_batch
select count(student_batch) from ineuron_students
select count(distinct student_batch) from ineuron_students
select student_batch , count(*) from ineuron_students group by student_batch
select * from ineuron_students
select max(students_marks) from ineuron_students where student_batch='fsda'
select student_name, student_batch, max(students_marks) from ineuron_students group by student_batch
select student_name,max(students_marks) from ineuron_students where student_batch='fsda'
select student_name from ineuron_students where students_marks in
(select max(students_marks) from ineuron_students where student_batch="fsda")
select student_name from ineuron_students where student_batch='fsda' order by students_marks DESC limit 2
select * from ineuron_students
select * from ineuron_students where student_batch = 'fsda' order by students_marks desc limit 1,1;
select * from ineuron_students where student_batch = 'fsda' order by students_marks desc limit 1,1
select * from ineuron_students where student_batch = 'fsda' order by students_marks desc limit 2,2
select * from ineuron_students where student_batch = 'fsda' order by students_marks desc limit 2 , 1
select * from ineuron_students where student_batch = 'fsda' order by students_marks desc limit 3, 1
select * from ineuron_students where student_batch = 'fsda' order by students_marks desc limit 4 , 1
select * from ineuron_students where student_batch = 'fsda' order by students_marks desc limit 5 , 1
select * from ineuron_students where student_batch = 'fsda' order by students_marks desc limit 6 , 1
select * from ineuron_students where student_batch = 'fsda' order by students_marks desc limit 3, 3
select * from ineuron_students where student_batch = 'fsda' order by students_marks desc limit 3
select * from ineuron_students where student_batch = 'fsda' order by students_marks desc limit 2, 3
select * from ineuron_students where students_marks =(
select min(students_marks) from
(select students_marks from ineuron_students
where student_batch ="fsda"
order by students_marks desc
limit 3 ) as top);
select students_marks from ineuron_students
where student_batch ="fsda" order by students_marks desc limit 3
select * from ineuron_students
select student_id , student_batch , student_stream,students_marks ,
row_number() over(order by students_marks) as 'row_number' from ineuron_students
select * from (select student_id , student_batch , student_stream,students_marks ,
row_number() over(partition by student_batch order by students_marks desc) as 'row_num'
from ineuron_students ) as test where row_num = 1
select student_id , student_batch , student_stream,students_marks ,
row_number() over(partition by student_batch order by students_marks desc ) as 'row_num'
from ineuron_students
select student_id , student_batch , student_stream,students_marks ,
row_number() over(order by students_marks desc) as 'row_number',
rank() over(order by students_marks desc ) as 'row_rank'
from ineuron_students
select * from (select student_id , student_batch , student_stream,students_marks ,
row_number() over(partition by student_batch order by students_marks desc) as 'row_number',
rank() over(partition by student_batch order by students_marks desc ) as 'row_rank'
from ineuron_students ) as test where row_rank = 1
select * from (select student_id , student_batch , student_stream,students_marks ,
row_number() over(partition by student_batch order by students_marks desc) as 'row_number',
rank() over(partition by student_batch order by students_marks desc ) as 'row_rank',
dense_rank() over( partition by student_batch order by students_marks desc) as 'dense_rank'
from ineuron_students ) as test where `dense_rank` = 3