SQL exercises
Language 
February 25, 16:49 MSK
Login:

Password:

forgot password?
Registration

Personal page
Developers & Thanks
For employers
References
Feedback
Support SQL-EX.RU



SQL Exercises news letters, latest issue

#699 (2018-02-24)

Week news

§
As the important news can appear and between releases of news letters, I shall use this page for the publication of the information on all changes on the site.
The forum is not so suitable for this purpose since the message there "escapes" and can appear on previous page before it will lose a urgency.
So look in here if you wish to be well informed about last events on the site. The link to this page is in section of news at the main page below a subscription banner.
§
New version of mobile application from Dmitry Ponomarenko is now available for work with site forums and more. Try this.

§
The most popular topics of the forum
Topic        Messages
§
Authors of the week in the forum
Author	Messages
selber  	7
PNA921	6
Kuzaev	5
Kursist	5
qwrqwr  	5
§
Available competitions on query optimization
Exercise	Date	     Author
147	2012-02-23	$erges
159	2012-07-25	anddros
170	2011-07-15	qwrqwr
182	2012-02-16	Baser
192	2012-03-03	Baser
197	2014-11-06	pegoopik
214	2015-06-24	pegoopik
222	2018-02-14	pegoopik
259	2012-07-23	anddros
§
Additional non-optimization competitions
Exercise	Date	Author/Organizer of competition
71	2010-12-26	anddros (closed)
88	2012-07-12	crescent/Pegoopik  (closed)
91	2012-05-31	smog/$erges
102	2016-07-04	GriGrim/pegoopik
121	2012-07-06	ZrenBy/anddros
153	2010-09-21	Ozzy
§
Changes among leaders (solved exercises of third stage):
1. Angellore (228)
4. al29 (228)
6. vlksm (221, 225)
8. qwrqwr (218)
12. alex_v (156, 223)
36. Kursist (204, 210)
63. Gosha (144, 146, 148)
69. vasilyvanc (177)
71. enull (143)
§
Applicants for Top 100 have advanced in rating:
139. aronov.oleg (137, 1664.985)
150. pavel1211 (126, 59.576)
151. hard (126, 371.829)
182. demkse (117, 630.932)
189. mitvol (114, 20.487)
§
Have been certified last week:
Andrey_Mal (A18398359) [BK] - g. Obninsk, Rossiya
§
Statistics:
Number of subscribers - 3031
Number of rating's participants - 12035
Number of second-stage's participants - 3055
Number of third-stage's participants - 127
On the learning stage - 243085
Certified specialists in total - 789

ТОР 20

NoPersonNumber of
Sel_ex
Last_SelNumber of
DML_ex
ScoresDaysDays_2Days_3S_3LastSolvedLastVisit
1Boiko D. (Angellore)12632283364638465236.4073825.1402382018-02-202018-02-23
2Krasovskij E.A. (pegoopik)1262228336432749209.94313.8142352018-01-162018-02-22
3Kreslavskij O.M. (Arcan)1262228336433999269.462210.7962352018-01-132018-02-23
4Kostomarov A.V. (al29)42422283160221272435.5752198.7702292018-02-202018-02-22
5Bezhaev A.Yu. (Baser)1257225316292636199.875177.3102272017-07-202018-02-20
6Karasyova N.V. (vlksm)1259225336334301290.336241.6822252018-02-212018-02-23
7Dubinskij A.V. (_velial)1257693363129842793.5243368.3692252018-01-142018-02-23
8Kurochkin P.A. (qwrqwr)1254221336193025254.872163.3602112018-02-212018-02-23
9Filippkin D.V. (_dimon_)1244115295972737194.554127.9092022016-07-202016-09-29
10Movlyanov A.P. (Oct)7244102958813072353.7232448.8471932017-01-232017-04-03
11Sal'nikov S.A. ($erges)122720625551253317.50514.4981812015-02-022018-01-23
12Vyazovetskov A.S. (alex_v)12226156235214276152.89769.6461632018-02-212018-02-22
13Kukushkin S.A. (smog)92251632952723681054.5621122.1811532017-10-032018-02-05
14Doschenko V.N. (mcrain)142091953350723281864.246349.4231392018-02-152018-02-23
15Yatsuk A.A. (Faust_zp)1186255244591984144.150108.8401372012-07-122012-07-30
16Kuznetsov V.S. (herrRo)1171260234181167478.51173.3351222012-02-082013-06-18
17Grinkevich D.L. (Dmitrij Grinkevich)1320410274672059367.128932.3431092017-05-312018-01-11
18Drozdkov A.N. (anddros)51921624445224412.34111.0911082014-07-212015-01-29
19Shindin A.V. (AlShin)114725223366928408.505777.457992011-05-052014-05-18
20Timofeeva E.V. (Umrikhina)12051031479338150.34747.237962017-07-272018-02-19

Best results for last week

Nosurnamen_selsel_allsel_scoresdml_scoresscoresratinglast_visit
1>Polyakov A.I. (Alexander Green)5671171844623 Feb 2018
2Malyshkin (Andrey_Mal)7701151651222 Feb 2018
3Mityurin V. (mitvol)51141001018922 Feb 2018
4Pantileenko D. (iriswind1)7531001086222 Feb 2018
5Shkarednyj I.N. (Gosha)31639096322 Feb 2018
6>Klinkova A.A. (AnnaKlinkova)717909316323 Feb 2018
7Karpeshin E.N. (Zealdequar)48580834922 Feb 2018
8Volkov S. (VolkovSA)336808117423 Feb 2018
9>G N.E. (gana)448808119823 Feb 2018
10Chesnokov M.S. (VinniL)715808226122 Feb 2018
11darkematta (Darkematta)66808552122 Feb 2018
12Kurochkin P.A. (qwrqwr)2254606823 Feb 2018
13Vyazovetskov A.S. (alex_v)32266061222 Feb 2018
14>Pusev D.V. (Pusev_Denis)39160638423 Feb 2018
15M (HummerSmith)419606187222 Feb 2018
16Azbel' E.G. (enull)11465057122 Feb 2018
17>Yurin D.I. (Diman9425)412505443123 Feb 2018
18SQLTest S.S. (BertokMo)3341014314622 Feb 2018
19Chikovani (chicovani1981)33404314518 Feb 2018
20Kolesnikov (hard)212635815121 Feb 2018

Let's learn SQL

MySQL. Usage of query variables

E.A. Krasovskiy

People rather often ask whether there are equivalents for analytic (windowing) functions in MySQL. No, there are not. To replace them, self join queries, complex subqueries, etc, are used. Most of such workarounds turn out to be ineffective.

There are no recursive queries in MySQL either. However, a part of problems usually solved by analytic functions or recursive queries can be handled by features available in MySQL.

One of these features is the unique mechanism of processing variables within a SQL query, which is very unusual for other DBMS. In MySQL, you can declare a variable within a query, change its value, and put it into the result set of the SELECT statement for output. And the most notable thing is, the processing order for query rows, and thus the order values are assigned to variables, can be defined by custom sorting!

Note: this article implies expressions within the SELECT statement are processed in order from left to right; however, there is no confirmation of such a processing order in the official MySQL documentation. You need to keep that in mind when switching from one server version to another. To ensure the required evaluation order, a fake CASE or IF statement can be used.

Read more:
Equivalent of recursive CTEs
Equivalents for analytic functions
Performance

Useful links

§ Official communities of the site sql-ex in social networks: SQL Exercises at VK.com and SQL Exercises at LinkedIn.com

§ .
    The resource is positioned as a "handbook" for this site SQL-EX.COM, but can be used irrespective of it also.

§ The following DBMS are used on the site for solving exercises: SQL Server 2012 Express, Oracle Database 11g Express Edition, MySQL 5, PostgreSQL 9.

§ If you like this site, vote for us:
del.icio.us
dzone.com
Digg.com
stumbleupon.com



Home
Week news
Profile
References
Rambler's Top100
Copyright SQL-EX © 2002-2018. All rights reserved.
contact