Are you over 18 and want to see adult content?
More Annotations
![Double Your Freelancing - Courses And In-Depth Guides For Freelancers](https://www.archivebay.com/archive/4ad3608d-c373-4612-b483-c664ec11935f.png)
Double Your Freelancing - Courses And In-Depth Guides For Freelancers
Are you over 18 and want to see adult content?
![College Affordability Guide | Learn More, Pay Less](https://www.archivebay.com/archive/bb1fb44e-9131-4378-8cd1-f2535b6ae125.png)
College Affordability Guide | Learn More, Pay Less
Are you over 18 and want to see adult content?
![تعمیر کامپیوتر - با سرویس رایگان - 09122654736](https://www.archivebay.com/archive/606c12b6-c564-49c8-9f2a-943dad99817d.png)
تعمیر کامپیوتر - با سرویس رایگان - 09122654736
Are you over 18 and want to see adult content?
![Kids Magazines - Children's Magazines & Books | Highlights](https://www.archivebay.com/archive/ddbf4168-6436-4875-a890-9920dc49f1b6.png)
Kids Magazines - Children's Magazines & Books | Highlights
Are you over 18 and want to see adult content?
![RNI – Radio Northsea International – Your Good Music Station](https://www.archivebay.com/archive/fa7be5ec-6385-495f-aa38-63c320504e64.png)
RNI – Radio Northsea International – Your Good Music Station
Are you over 18 and want to see adult content?
![Fotografías e imágenes de retratos, paisajes y viajes del fotógrafo Fernando Sánchez](https://www.archivebay.com/archive/80f4acaf-93fe-47cd-b590-672687b7afca.png)
Fotografías e imágenes de retratos, paisajes y viajes del fotógrafo Fernando Sánchez
Are you over 18 and want to see adult content?
![A complete backup of egyyfast.blogspot.com](https://www.archivebay.com/archive/5f40cd07-d6df-4c73-99eb-73ed304040a5.png)
A complete backup of egyyfast.blogspot.com
Are you over 18 and want to see adult content?
Favourite Annotations
Text
EXPLAIN EXTENDED
I'm spending this New Year holiday in sunny Florida. One of its most beautiful places is the Everglades: the endless sea of grass, extending to the horizon, as far as the eye can see and beyond, and teeming with life.. There are all kinds of animals there. Herons, egrets, anhingas; fish, turtles, snakes; otters, skunks, small rodents; and of course, the king of Florida's wetlands, the American NOT IN VS. NOT EXISTS VS. LEFT JOIN / IS NULL: ORACLE ATSEE MORE ONEXPLAINEXTENDED.COM
IN VS. JOIN VS. EXISTS: ORACLE AT EXPLAIN EXTENDEDSEE MORE ONEXPLAINEXTENDED.COM
ANALYTIC FUNCTIONS IN MYSQL: FIRST_VALUE, LAST_VALUE, LEAD This query selects the first and the last month of each row's season. In this example above, January is the first month and December is the last month of winter, as January, 2009 comes earlier than December, 2009. In MySQL, we can emulate FIRST_ROW() with the following construction:. SELECT *, @r AS `FIRST_VALUE(month) OVER (PARTITION BY season ORDER BY id)` FROM ( SELECT m.* ANALYTIC FUNCTIONS: SUM, AVG, ROW_NUMBER In one of the previous articles I wrote about emulating some of analytic functions in MySQL.. Now, I'd like to cover this question more extensively. A quick reminder: an analytic function is a function that behaves like an aggregate function with one exception: aggregate function returns one last row for each aggregated set, while an analytic function returns intermediate results too. EFFICIENT DATE RANGE QUERY: SQL SERVER AT EXPLAIN EXTENDED From Stack Overflow:. I have a table A that has a startDate and an endDate as 2 DATETIME columns besides some more other columns.. I have another table B that has one DATETIME column (call it dates column).. This is in SQL Server 2005.. How to best set up the indexes etc to get the following: SELECT * FROM a, b WHERE a.startDate >= b.dates ANDa.endDate < b.dates
ANALYTIC FUNCTIONS: OPTIMIZING SUM AND ROW_NUMBER Join the main table so that the grouping_set = current_grouping_set AND ordering_set = b.dates AND a.endDate < b.dates INNER JOIN VS. CROSS APPLY AT EXPLAIN EXTENDED Summary: While most queries which employ CROSS APPLY can be rewritten using an INNER JOIN, CROSS APPLY can yield better execution plan and better performance, since it can limit the set being joined yet before the join occurs. Written by Quassnoi. July 16th, 2009 at 11:00 pm. Posted in SQL Server. « Oracle: OR on multiple EXISTS clauses. NVL VS. COALESCE AT EXPLAIN EXTENDED From Stack Overflow:. Are there non obvious differences between NVL and COALESCE in Oracle?. The obvious differences are that COALESCE will return the first non-NULL item in its parameter list whereas NVL only takes two parameters and returns the first if it is not NULL, otherwise it returns the second.. It seems that NVL may just be a base case version of COALESCE. ANALYTIC FUNCTIONS: OPTIMIZING SUM AND ROW_NUMBER Join the main table so that the grouping_set = current_grouping_set AND ordering_set = b.dates AND a.endDate < b.dates IN VS. JOIN VS. EXISTS: ORACLE AT EXPLAIN EXTENDEDSEE MORE ONEXPLAINEXTENDED.COM
NOT IN VS. NOT EXISTS VS. LEFT JOIN / IS NULL: SQL SERVERSEE MORE ONEXPLAINEXTENDED.COM
ANALYTIC FUNCTIONS: OPTIMIZING SUM AND ROW_NUMBER Join the main table so that the grouping_set = current_grouping_set AND ordering_set = b.dates AND a.endDate < b.dates IN VS. JOIN VS. EXISTS: ORACLE AT EXPLAIN EXTENDEDSEE MORE ONEXPLAINEXTENDED.COM
NOT IN VS. NOT EXISTS VS. LEFT JOIN / IS NULL: SQL SERVERSEE MORE ONEXPLAINEXTENDED.COM
ANALYTIC FUNCTIONS: OPTIMIZING SUM AND ROW_NUMBER Join the main table so that the grouping_set = current_grouping_set AND ordering_set = b.dates AND a.endDate < b.dates ADJACENCY LIST VS. NESTED SETS: POSTGRESQL AT EXPLAIN EXTENDED The nested set model the fastest for the first query ( 0.05 s ), however, adjacency list shows good performance and is very fast too (selecting 200,000 rows is a matter of less than 0.1 second ). For the second query, adjacency list is much faster, however, the nested sets are still usable. Finally, for the third query, nested sets modelshows
SQL SERVER: EFFICIENT DISTINCT ON DATES SQL Server's optimizer lacks ability to skip distinct records in an index. That's why DISTINCT is often slow in SQL Server. To work around this we can create a list of distinct dates from MIN to MAX using a CTE, then check for existence of a record within each date. POSTGRESQL: OPTIMIZING DISTINCT PERFORMANCE AT EXPLAIN In PostgreSQL (as of 8.3, at least), performance of DISTINCT clause in SELECT list is quite poor.. Probably because DISTINCT code in PostgreSQL is very, very old, it always acts in same dumb way: sorts the resultset and filters out the duplicate records.. GROUP BY that can be used for the same purpose is more smart, as it employs more efficient HashAggregate, but its performance is still poor SQL SERVER: EXCEPT ALL AT EXPLAIN EXTENDED SQL Server supports EXCEPT clause which returns all records present in the first table and absent in the second one. But this clause eliminates duplicates and cannot be used as a subject to a DML operation. ANSI SQL standard describes EXCEPT ALL which returns all records from the first table which are not present in the secondtable, leaving
SQL SERVER: PERFORMANCE OF UNIQUE INDEX A UNIQUE index, contrary to popular opinion, does not have any performance drawbacks on DML compared to a plain, non- UNIQUE index. When the engine tries to insert a new row into the indexed table, being the index UNIQUE or not, it performs the following steps: While traversing, see if the inserted key does match an existing key. Failon UNIQUE
MYSQL: GROUP BY IN UNION AT EXPLAIN EXTENDED MySQL 's documentation says: Use of ORDER BY for individual SELECT statements implies nothing about the order in which the rows appear in the final result because UNION by default produces an unordered set of rows. Therefore, the use of ORDER BY in this context is typically in conjunction with LIMIT, so that it is used to determine the subset SQL SERVER: AGGREGATE BITWISE OR AT EXPLAIN EXTENDED From Stack Overflow:. I am creating a script for merging and deleting duplicate rows from a table. The table contains address information, and uses an integer field for storing information about the email as bit flags (column name value).For example, if bit 1 is set in value, that means the record is a primary address.. There are instances of the same email being entered twice, but sometimes TOP 5 XKCD COMICS WHICH CAN ILLUSTRATE PROGRAMMING As many of you know, I actively participate on Stack Overflow, the leading Q/A website for software developers.. Kind folks on Stack Overflow have made their data open for examination, and anyone can query their database using this web interface at data.stackexchange.com.. Many of the questions and answers there are illustrated with links to XKCD, the web comics created by RandallEXPLAIN EXTENDED
How to create fast database queriesHAPPY NEW YEAR!
with one comment
In my New Year posts I usually try to recap and summarize the past year. It won't take long this time: FUCK YOU, CORONAVIRUS! Now that I've gotten that off my chest, I have to think of something to write about in this New Year's post. So I was thinking, why not put a face to the name we all hate so much? Let's use SQL to do some ray tracing and draw a 3D picture of thedreaded virus.
By now, I believe we are all familiar with the picture of the virus. It looks like a ball covered with spikes. The spikes look something like the solar corona, which is what gave the virus its name. They have this distinct triangular shape. We'll create a sphere covered with several dozens of spikes. Every spike will be a small pyramid, with an equilateral triangle as a base and isosceles triangles as lateral faces. This means it will be aright pyramid.
The pyramids will be "standing" on their apexes, upside-down. The height of every pyramid will be perpendicular to the sphere surface and continue the sphere's radius at the apex. Then we will implement the pinhole camera model and use ray tracing algorithms to calculate the lighting of the sphere and the spikes.TYPES
3D modeling heavily uses vector algebra. Of course pure SQL offers enough math functions to get around. But functions and routines are not first class citizens in SQL, which means we would have to copy-paste the bulky vector manipulation formulas every time we will need them, which would make our query unwieldy really fast. This is a good chance to get familiar with PostgreSQL's rich system of custom types and custom operators. It lets users define their own types, create functions to work with them and even overload theoperators.
Read the rest of this entry »SHARE THIS:
*
Written by Quassnoi
December 31st, 2020 at 11:00 pm Posted in PostgreSQL Tagged with 3D , ASCII art, coronavirus
, intersection
, Möller Trumbore
, Phong
, ray tracing
, reflection
, SQL
HAPPY NEW YEAR!
with one comment
I'm spending this New Year holiday in sunny Florida. One of its most beautiful places is the Everglades : the endless sea of grass, extending to the horizon, as far as the eye can see and beyond, andteeming with life.
There are all kinds of animals there. Herons, egrets, anhingas; fish, turtles, snakes; otters, skunks, small rodents; and of course, the king of Florida's wetlands, the American alligator. The alligator is a well-oiled killing machine. It's motionless and extremely energy efficient when resting, but deadly fast when hunting. When the alligator is hunting, its eyes instantly track the faintest motion — and a fierce jump in any direction will follow immediately. I was on a guided tour, and the tour guide mentioned that the alligators have binocular vision. The fields of view of their two eyes overlap, giving the predator the ability to estimate the direction and the distance to its prey more accurately. We humans also have binocular vision. It allows us to see the world in three dimensions. When we are looking at an object with our two eyes, each eye sees it at a slightly different angle. The closer the thing is to us, the more the difference. This effect is called binocular parallax, and our brain can use it to estimate the distance to theobject.
There are ways to trick the brain into believing something is 3D while it's not. To do this, we need a way to project a different image into each eye. There are lots of ways to do that: think holograms, polarized glasses, tilt cards, and many more. Most of those methods require special equipment and materials, either to see or to produce the image. Maybe even both. However, there is a way to see a three-dimensional image even on a simple piece of paper (or a plain LCD monitor without any 3D capabilities). It is technically called an autostereogram but most people know them as Magic Eye pictures. A picture like this looks like repeating patterns of random dots or characters. The frequency of the patterns encodes the three-dimensional image: the close is the part of the image to the observer, the more frequent are the patterns. It takes some effort to see the depth in what at first seems to be a random dot pattern. Not everyone can do that on the first try. There are lots of resources online which teach how to do that. The good thing is it's like riding a bicycle: once you got it right for the first time, there's no going back, it's always there with you. Ever since I was a kid, I have been fascinated by the stereograms. So the moment I heard the words "binocular vision" from the tour guide, I instantly knew what would this New Year post be about. Let's make a stereogram in PostgreSQL! Read the rest of this entry »SHARE THIS:
*
Written by Quassnoi
December 31st, 2019 at 11:00 pm Posted in PostgreSQLHAPPY NEW YEAR!
Comments enabled. I *really* need your comment As readers of my blog know, SQL is a wonderful tool for graphics processing. You can use it to draw Mandelbrot sets, table game
board s and
even snowflakes
, all in a
single query.
As I was preparing this year's entry, I found myself all out of ideas. What image shall we be generating this year? A cat? A pig? A winter scenery? It's all doable in SQL (or course), however I couldn't make myself pick anything in particular. And frankly speaking, even with all the power of SQL at my hands, I'm a lousy artist. Then an idea struck me. Why try and create art when there's so many excellent artists out there on the Internet, whose work I could just steal put to fair use? And my phone camera makes better pictures than I could ever aspire to create myself. Images that come out of the camera or from the Internet are all digital and compressed. Digital is of course a good thing when it comes to computer processing, but compressed is a challenge. And challenges is something I like. So this year, we will be creating a GIF decoder in SQL. GIF is one of the earliest compressed image storage formats, famous for its early adoption by the World Wide Web and for being named with an acronym no one can agree how to pronounce correctly. At its core lies LZW, a lossless compression algorithm which uses dictionary tables to encode repeating patterns of data. GIF is not the best format out there, of course, and it has fallen out of use in the last years (or even decades). Its algorithm, however, is not particularly memory or CPU intensive and as the image compression algorithms go, its implementation is quite simple. In other words, it makes a perfectSQL exercise.
To become familiar with GIF, I used an excellent GIF tutorial calledWhat's in a GIF?
by
Eric. S Raymond and Mike Flickinger. This tutorial is built around explanation of contents of a tiny sample GIF file: and this file is what we will be using during the first part of ourjourney.
Let's get started!
Read the rest of this entry »SHARE THIS:
*
Written by Quassnoi
December 31st, 2018 at 11:00 pm Posted in PostgreSQLHAPPY NEW YEAR!
with one comment
One of the best New Year presents I've ever got was a copy of the German-style board game, The Settlers of Catan.
This game has brought me and my friends many an hour of goodentertainment.
The game is played on a hexagon field with 19 hexagon tiles (3 + 4 + 5 + 4 + 3), which have to be randomly put into appropriate places. In addition, 18 of those tiles have a score token on it, which has to be put there, also randomly, albeit with some limitations. Finally, 9 more pieces (harbors) have to be randomly put to their places, which are printed on the game field. Today, we'll be implementing the Almanac Variable Catan setup usingSQL.
Read the rest of this entry »SHARE THIS:
*
Written by Quassnoi
December 31st, 2017 at 11:00 pm Posted in PostgreSQLHAPPY NEW YEAR!
with 5 comments
Here is a riddle I read a long time ago in _Nauka i Zhizn_, a popular Russian science magazine. > THE SULTAN'S RIDDLE>
> Once upon a time there was a Sultan who was looking for a vizier to > help him rule his country. It became known to him that among the > multitudes of his loyal subjects that populated his glorious empire, > two were regarded as the most wise and sharp in mind. Their names > were Ali-ibn-Wali and Wali-ibn-Ali. The Sultan summoned the men to > his palace and ordered them to stand in front of him.>
> "It has come to my attention that you, Ali, and you, Wali, are the > smartest men of all the people of Faith. Is that right?", asked the > Sultan, sipping his sharbat. "We do know a thing or two of the > beasts of the land and the fish of the sea and stars of the sky, > indeed, but your sheer wisdom, o Great Sultan, outshines whatever > puny bits of knowledge we might have and makes words coming from our > mouths sound like child's babbling", said the wise men, kneeling > before the Sultan (as they were truly wise and knew how to talk to a > man of high power).>
> "Good, good," said the Sultan with a sneer, "I see you are good with > words but are you as good with numbers? Let me test your knowledge.">
> "A diviner once came to my palace and revealed two numbers to me," > continued the Sultan, "one being my lucky number, and another one > being my unlucky number. Each of these numbers is more than one and > less than a hundred. I never tell these numbers to anyone as this > could put the fate of my empire in enemy's hands. But I am going to > multiply those numbers and secretly tell their product to you, Ali, > and then I am going to add those numbers and secretly tell their sum > to you, Wali. If you are as wise as they say, you will have no > problem figuring out those numbers.">
> And then he ordered the men to approach him and kneel before him, > and he whispered the numbers to their ears.>
> Once Ali raised from his knees, he stood there for a moment, > silently moving his lips, and then said: "Unfortunately, о the > Brightest One, I cannot tell you those numbers.">
> "This is true," confirmed Wali, stroking his beard.>
> "Thank you, most esteemed Wali," said Ali, his face brightening with > joy. "Now I can tell those numbers.">
> "Thank you too, most esteemed Ali," answered Wali. "Now I can tell> them too."
>
> And they whispered the numbers to the ear of the astonished Sultan, > and they turned out right. And they both were appointed viziers.>
> Can you tell those numbers? At first glance, this riddle seems impossible to solve. However, if we carefully read and parse what did Ali and Wali say, we can easily solve this riddle too. And to make things even more fun, we'll do thatin SQL.
Read the rest of this entry »SHARE THIS:
*
Written by Quassnoi
December 31st, 2016 at 11:00 pm Posted in PostgreSQLHAPPY NEW YEAR!
with one comment
It's Yuletide again and today I'd like to tell about an old tradition in Russia. Its origins can be traced back to pre-Christian, pagan rites of the ancient Slavic tribes. Each year on the Christmas eve, when it is freezing cold outside and the Grandfather Frost comes out to inspect his vast domain, young girls gather in an old, poorly lit bathhouse and try to look into thefuture.
There are many ways to do a Christmas divination: some prefer candle wax, some go with cats or mirrors, some use a good old deck of cards. But the old women can tell you of another way to look into the future. It requires skill and patience, but it is said an intrepid soul brave enough to look the eternity in the eye can unveil the mystery of the future and see the good and the bad that the next year will bring. To do this, you need a large enough checkered board and a bag of dried beans, black and white. Carefully arranging the black beans into letters three squares wide and five squares tall, you write your question on the board. When ready, you exclaim the magic words: "RENDRAG! YAWNOC! NNAMEUN NOV!" and begin the magic rite. Those of you who can spell backwards can scroll down now. Those who are not familiar with Conway's Game of Lifeand don't
believe in Christmas magic, can google for it(do it, if
you never have, it's fun). And I will go on and explain the rules to the rest of my readers who do believe in magic pagan rites. First, you check how many black beans surround each tile on the board. If it is surrounded by exactly two black beans, it stays the same next turn, for the two is the number of the true love; if it is surrounded by exactly three, it gets the bean next turn, for the three is the number of new life; any other number means the tile is empty the next turn, for empty is the world devoid of life and love. You use the white beans to mark the tiles which are to get or to lose the bean on the next turn, for white means change. If you are close to the edge of the board and don't have adjacent tiles from either side, wrap around to another side, that is top to bottom, left to right or vice versa. This symbolizes the old belief that our world was bagel-shaped. Singing an ancient song, you do the divination turn by turn, moving the beans around. With time, certain patterns emerge on the board. Some resemble a crane, some resemble a beehive, other just seems chaotic. When you do as many turns as there are days in the new year, you look at the patterns and interpret them. Now, let's try to do this in SQL. We'll be using PostgreSQL 9.4 as it supports some very nice features. Read the rest of this entry »SHARE THIS:
*
Written by Quassnoi
December 31st, 2015 at 11:00 pm Posted in PostgreSQLHAPPY NEW YEAR!
with 5 comments
My previous New Year posts were about drawing pictures in SQL:snowflakes , clocks
, fractals
and even our planet Earthas seen from above.
But contrary to the popular belief, SQL is not just about graphics processing. You can use it for all kinds of things. Today we'll use SQL to create music. Remember those polyphonic ringtones in early 2000's cell phones? Instead of downloading MP3's (which were a pain to download and even more pain to upload them to the phone), you could type in the notes and their values, and the phone would play them for you. Most phones played tunes in a very straightforward way: for each note, they calculated its frequency and generated a pure sine waveform of this frequency. Polyphonic phones could play several notes at once: this would require two or more superimposed waveforms. Math behind that are very simple: channel amplitude is a sine function of time and frequency, and all the channel amplitudes are added together. Let's do this too and play some New Year music with PostgreSQL. Read the rest of this entry »SHARE THIS:
*
Written by Quassnoi
December 31st, 2014 at 11:00 pm Posted in PostgreSQL SQL SERVER: DELETING WITH SELF-REFERENTIAL FOREIGN KEY, HANDLING LOOPSwith 2 comments
From comments to one of my previous posts: SQL Server: deleting with self-referential FOREIGN KEY > In my case the query goes to infinite loop.>
> If I use OPTION (MAXRECURSION 365) or OPTION (MAXRECURSION 3267), it > fails when reaching highest recursion value.>
> If I use 0 (which is infinite) the query runs for an hour with no> output.
>
> I understand it is a data related issue which is causing infinite > loop, but I cannot control the data being sent to the DB.>
> Any other way we can handle it?>
> Thanks,
> Jay
That's a common situation, and we would sometimes need to clean the mess up (i. e. remove the whole loop). To do this, we would just need to add a little check condition to therecursive CTE.
We just record the topmost entry (or better say the entry we first use in the loop, as there is obviously no topmost entry), and check if we ever come across it again as we do the recursion. Once we have it on next iteration, we know it's a loop and it's timeto stop.
Here's how we do this:?
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
CREATE TABLE TestComposite (id INT NOT NULL,
siteUrl NVARCHAR(255) NOT NULL, name NVARCHAR(MAX) NOT NULL,parentId INT NULL,
PRIMARY KEY (id, siteUrl), FOREIGN KEY (parentId, siteUrl) REFERENCES TestComposite (id, siteUrl))
GO
INSERT
INTO TestComposite VALUES (1, 'site1', 'name1', NULL), (2, 'site2', 'name2', 4), (3, 'site2', 'name3', 2), (4, 'site2', 'name4', 3), (5, 'site5', 'name5', NULL), (6, 'site5', 'name6', 5)GO
WITH q (start, id, siteUrl) AS(
SELECT id, id, siteUrl FROM TestCompositeWHERE id = 2
AND siteUrl = 'site2'UNION ALL
SELECT start, tc.id, tc.siteUrlFROM q
JOIN TestComposite tc ON tc.parentID = q.id AND tc.siteUrl = q.siteUrl WHERE tc.id <> q.start)
DELETE
FROM TestCompositeOUTPUT DELETED.*
WHERE EXISTS
(
SELECT id, siteUrlINTERSECT
SELECT id, siteUrlFROM q
)
We had a loop on 2 -> 3 -> 4 which is gone after the query. See this query on SQL Fiddle .SHARE THIS:
*
Written by Quassnoi
July 10th, 2014 at 9:00 pm Posted in SQL Server TOP 5 XKCD COMICS WHICH CAN ILLUSTRATE PROGRAMMING QUESTIONSwith 6 comments
As many of you know, I actively participate on Stack Overflow , the leading Q/A website for softwaredevelopers.
Kind folks on Stack Overflow have made their data open for examination, and anyone can query their database using this web interface at data.stackexchange.com . Many of the questions and answers there are illustrated with links to XKCD , the web comics created by Randall Munroe. So I decided to see which of those comics best illustrate quirks and oddities we keyboard warriors have to deal with in our daily routine. The query itself is quite simple:?
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
SELECT link, cnt, id , scoreFROM (
SELECT link, id, score, COUNT(*) OVER (PARTITION BY link) cnt, ROW_NUMBER() OVER (PARTITION BY link ORDER BY score DESC) rnFROM (
SELECT id, score,
'http' +
SUBSTRING(start, 1, PATINDEX('%/%', SUBSTRING(start, 13, 30)) + 12)FROM (
SELECT id, score,
SUBSTRING(body,
PATINDEX('%://xkcd.com/%', body), 20) startFROM (
SELECT
TOP 400000
id,
body, score
FROM
posts
ORDER
BY
id
DESC
UNION
SELECT
TOP 4000000
postId,
text, score
FROM
comments
ORDER
BY
id
DESC
) q
WHERE body LIKE
'%://xkcd.com/%'
) q
) q (id, score, link)) q
WHERE rn = 1
ORDER BY
cnt DESC
The TOP clauses are there to limit the query size, as their hosting provider does now allow fulltext indexing, and without them the query would just time out. This query only searches recent comments and posts, so some famous and top-voted posts might not be present here. Still, it's a good sample. You can see all results here,
and we'll just discuss the top 5 entries. #5. RANDOM NUMBER (10 LINKS) Of course the questions which link to it deal with random number generators of any kind and fallacies in their implementation. Read the rest of this entry »SHARE THIS:
*
Written by Quassnoi
July 9th, 2014 at 8:00 pm Posted in MiscellaneousHAPPY NEW YEAR!
with 5 comments
In one of my previous New Year's postswe drew
snowflakes in PostgreSQL. The algorithm we used to create the snowflakes is an implementation of an L-system , which is an example of a fractal. There are many more beautiful objects we can see in the winter: frozen trees, frost patterns on windows, cracks on ice etc., all of them being fractals. Today we will be constructing escape-time fractals. To build such a fractal, one would need to run a certain function for each point on the plane iteratively and see how many iterations does it take for the function to overflow: the more it takes, the brighter is the point'scolor.
I won't go deep into fractal theory now, just show that they can be constructed with SQL relatively easily. For instance, Mandelbrot set , one of the best known escape-time fractals, is almost a one-liner in PostgreSQL: Read the rest of this entry »SHARE THIS:
*
Written by Quassnoi
December 31st, 2013 at 11:00 pm Posted in PostgreSQLOlder Entries
SUBSCRIBE
Subscribe in a readerSubscribe by email
CONTACTS
ASK ME A QUESTION
DROP ME A LINE
SHOULD I?
Yes. Feel free to ask questions and write me. An interesting question is a pleasure to answer, and I really enjoy receiving feedbackRECENT ARTICLES
* Happy New Year!
* Happy New Year!
* Happy New Year!
* Happy New Year!
* Happy New Year!
CALENDAR
June 2021
M
T
W
T
F
S
S
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
Dec
ARCHIVES
* December 2020
* December 2019
* December 2018
* December 2017
* December 2016
* December 2015
* December 2014
* July 2014
* December 2013
* October 2013
* September 2013
* August 2013
* July 2013
* June 2013
* May 2013
* March 2013
* January 2013
* December 2012
* December 2011
* June 2011
* April 2011
* March 2011
* February 2011
* December 2010
* November 2010
* October 2010
* September 2010
* August 2010
* July 2010
* June 2010
* May 2010
* April 2010
* March 2010
* February 2010
* January 2010
* December 2009
* November 2009
* October 2009
* September 2009
* August 2009
* July 2009
* June 2009
* May 2009
* April 2009
* March 2009
* February 2009
CATEGORIES
* Meta
* Miscellaneous
* MySQL
* Oracle
* PostgreSQL
* SQL Server
* Uncategorized
STACK OVERFLOW
The Journalist template by Lucian E. Marin — Built for WordPressWrite a Comment...
Email (Required) Name (Required) WebsiteLoading Comments...
Comment
×
Details
Copyright © 2024 ArchiveBay.com. All rights reserved. Terms of Use | Privacy Policy | DMCA | 2021 | Feedback | Advertising | RSS 2.0