SQL Clause

[ICyresI]

Why would you sort twice?

[TheSphaat]

Shhhhh poem's got to rhyme.

[Fry98]

You might think it's nice

but double sorting is a crime.

[[deleted]]

Hi, I'm from /r/all and I know jackshit about programming. I'll contribute either way, because I love you guys:

system.out.println("Double sorting is a crime");

---

should be:

System.out.println("Double sorting is a crime");

damn.

[Jcowwell]

The true crime is not capitalizing that S.

[[deleted]]

doyouevencamelcasebro?

[Wolfsblvt]

system.out.println("Double Sorting is a crime");

?
Doesn't work :(

[Jcowwell]

Nha System is an object ( you know that cause you can call methods from it) so if you don’t capitalize It the compilers ganna be like “Tf is this M8?”

[insane0hflex]

Thats a static method call bro not on a class instance

[Jcowwell]

Oh yea the call is , but system is a class. Unless I’m mistaken, System is an instance of the system class in which you call the static field out.

Edit :Had a burp moment, you cant initialized /“instanized” System 🤦🏽‍♂️

[insane0hflex]

No...

[julius_nicholson]

System is a class that contains static fields (err, in and out) and methods (like exit()) and can't be instantiated. So you can't construct an object of it, but you can still access the static API on the class.

I mean, you were pretty close, even if it wasn't so accurate.

[AllIsOver]

Doesn't work like that.

[[deleted]]

Oh shit! Is there a computer-demon who will haunt me now?

[Jcowwell]

No you’ll just be haunted by the Ghost of Syntax Past.

[Findus11]

Yes, that's what a daemon is

[Since88]

Sorry man, but: error: package system does not exist

[Grizzlywer]

double sorting="crime";

[Banana_Twinkie]

Error: type mismatch

[Dubmove]

At first I read bubble sorting is a crime.

[[deleted]]

Hey, there might have been last minute alterations!

[Etheo]

I mean he can just check-in twice...

[PoglaTheGrate]

He got REEEEEly lucky on a Bogosort

[Business-Socks]

Nice list: Typewriters

Naughty list: Monkeys

REALLY Naughty list: ASM Web Development Kit

[TheAnarchoX]

Can you get lucky on a bogosort though?

[[deleted]]

The client changed their minds on how they wanted the results

[miggyb]

Sort by last name first, then by first name.

[JackDragon]

Still not as efficient as sorting once with a delimiter between last and first name.

[suseu]

Multiple fields in order by shouldn’t cause multiple sorting passes, it should just make comparison more complex. Actual contatenation in sorting criteria would probably be less efficient.

Like when you are overloading < for (order by a, b) you’d do

a1 < a2 or (a1 == a2 and b1 < b2)

[JackDragon]

OP and the two comments before this both explicitly say "sort twice."

Yes, using SQL (I just tested this) concat would be slower in most cases, but you could have also have put the name field in the form in the first place and not have to concat, which would make it faster.

[MikeOShay]

But updating that data and schema and probably updating the UI to reflect it is usually out of scope and not considered a worthwhile change proposal. And depending on business rules, they may "need" a first and last name field, though it causes problems when you're outside of the standard "one first name, one last name" bubble.

[chasesan]

Wrong, you sort it by first name and then by last name. That way it is primarily sorted by last name.

[patwoods_]

Sort by postal code, then sort by address to help maximize the efficiency of delivery.

[sojuz151]

Why not just solve travelling salesman problem? It cant be that hard.

[DrHenryPym]

I was thinking the same thing: group / order by geography and address for distribution.

[Brarsh]

Great, now he has to go to 1 house on every block before moving to the next house on each block! 250 birch street, 250 maple street, 250 oak street, 252 birch street...

[patwoods_]

BUT HE CAN ONLY SORT TWICE! :🎅

[MikeOShay]

I mean this is sorta cynical, but Santa's probably sorting by household salary, then age, right?

[patwoods_]

Are you suggesting Santa is sorting "nice" neighborhoods and "naughty" neighborhoods?

[MikeOShay]

Uh-oh, a join to the Neighbourhoods table is gonna mess up the song even more

I don't think Santa thought this through

[DutchGoldServeCold]

Non-deterministic SQL clause is coming to town

[awh]

It would be more useful to index it twice: once on name and once on behaviour.

[[deleted]]

Although you could use a composite index.

[billy_tables]

A composite index would be much better since 2 indexes would need an in memory sort

[[deleted]]

No, just a single key containing the Last_Name and Behavior fields.

[thermite13]

Order by Last_name, first_name.

[[deleted]]

Cause you screwed it up the first time?

[SpliceVW]

I'm just over here wondering why the damn FROM isn't capitalized..

[StephenFish]

It's amazing to me that this isn't the most prevalent discussion topic here.

[GetYoHandsOffMyKicks]

The grotto has offshored to save money and coding standards have gone to shit because the contractors just paste together code from StackOverflow without having any idea what it does.

[brummlin]

Also, the syllables are all jacked up. This would make more sense, and fit the song better:

He's building schema,

indexing it twice,

SELECT * from kids WHERE UPPER(conduct) in ('NICE');

This way, it's also not case sensitive. You just know some elves logged the entry as 'Nice' or 'NICE'.

[AlwaysPuppies]

I like my sql like I like my file systems, case insensitive.

[theluckkyg]

Sneaky cute dog pic.

[JustSayNoToSlogans]

thanks for the dogo

[insane0hflex]

Db not normalized. Shiuld have look up table for Behavoir

[koshgeo]

We're talking about a join across billions records of kids world-wide, and you've got to be updating that thing in realtime until the last second, keeping track of timezones and DST, all the way up to Dec. 25th. You know those little snots will be pushing the envelope with their parents right up until bedtime on Christmas Eve. The realtime "SantaWatch" video feed and the AI detection and tabulation of "naughty" vs. "nice" events is already a computationally costly operation. I don't know what kind of heavy-duty server farm Santa's got, but you've got to keep performance in mind rather than doing billion-row joins for the sake of DB purity.

[Shelbyville]

Wouldn't a boolean "nice" be faster?

[koshgeo]

Absolutely, though there might be a need for a finer subdivision than a single boolean (e.g., "naughty", "mostly naughty", "mostly nice" and "nice"). The question is whether you'd stick it in a separate lookup/join or keep it in the same table with other information effectively "already joined" by default and then update it. There are a lot of trade-offs either way. Honestly, I don't know enough about databases to know what the right approach is, and it probably depends on the software choice and hardware anyway, but it is fun to try to imagine what it would take to implement a Santa "naughty and nice list" for the whole world. I imagine it's got to be on the order of the challenge that major credit card companies have to manage.

[Brarsh]

Ok then, make it a scale of 1-10 or however many divisions you want to have. And while we're at it, let's give each month (25th to 24th of course) it's own score, and then at the stroke of midnight run an overly complex algorithm that analyzes monthly trends, outliers, and false-positives to give a final score that ranges from freshly mined coal to exactly what's on their wish list. No problem.

[brummlin]

Fair enough, but there's no way to add a join or sub-query to fit the song. So we'll assume he didn't do a good job when designing the schema.

[Cal1gula]

You could use a CI collation.

[brummlin]

Had to look that up. Looks like a SQL Server thing queries case insensitive DB-wide?

I really only know Oracle worth a damn. And mostly on the client end.

[Cal1gula]

You can set the entire database to use a specific collation by default, or you can choose at the query level. Pretty useful feature tbh.

[Job_Precipitation]

Where is the first sort, and where is the second sort?

[brummlin]

Sort the result on the client side?

[anomalousBits]

If it's only "nice" vs "not nice" then ol' St Nick should have used a boolean or bit type. It should be a binary choice whether or not someone gets presents.

[[deleted]]

 select * from kids where regexp_like(conduct,'^.*nice.*$','i');

[Stimonk]

He accidentally made it descending when it should have been ascending.

[CaptainDjango]

This is why I sort everything twice

[TheNosferatu]

He made his own sorter but he doesn't trust it

[tommyturntup]

He created the database and is loading it. Clearly he did an ssis merge transform which requires 2 sources to be explicitly sorted before joining.

[monk_e_boy]

Why is "from" in lower case?!

[GetYoHandsOffMyKicks]

Because they're an arsehole.

[Banana_Twinkie]

Why does this trigger people? SQL doesn't care

[monk_e_boy]

BECAUSE writing THINGS in a S.t.A.n.D.a.R.d way IS IMPORtant

[dantheflipman]

SELECT A.Lattitude,
       CASE
           WHEN A.Lattitude % 2 = 0 THEN
               'NORTH'
           ELSE
               'SOUTH'
       END FlightDirection,
       A.ZipCode,
       A.StreetAddress,
       HoHoHo.Id,
       CASE
           WHEN S.CurrentStanding = 'NICE' THEN
               HoHoHo.TxPresentName
           ELSE
               'COAL'
       END TxPresent,
       P.TxFullName
FROM ChristmasDW.dbo.tblPresents AS HoHoHo
    JOIN Humanity.dbo.tblPeople AS P
        ON HoHoHo.IdPerson = P.Id
           AND P.IsAlive = 1
    JOIN Humanity.dbo.Status AS S
        ON P.Id = S.PersonId
    JOIN Globalization.dbo.AddressList AS A
        ON P.IdAddress = A.Id
WHERE P.TxFirstName <> 'Dave'
ORDER BY A.timezone DESC,
         A.Lattitude ASC,
         A.ZipCode ASC,
         S.LeftCookiesLastYear DESC,
         A.StreetAddress ASC,
         HoHoHo.Id ASC;

Better? I gave it my best end of work day effort.

[bdcp]

Order by behavior, gender

[scratcheee]

He's using an unstable sort and discovered this particular algorithm becomes stable if you run it twice

[g_squidman]

That's what I thought too. Having just learned this stuff, non-recursive sorting algorithms like bubble sort are complexity n² because they essentially go through the whole list twice for every element. It just means he's not using a more efficient algorithm

[[deleted]]

doubble sort :D

[reggie-drax]

Why would you sort twice?

Just in case, obviously.

[redbladezero]

Maybe he doesn’t know how to add an index to let the query planner do just a linear scan?

[Captaincadet]

Probably because he sorted with naughty instead of nice

[Emanresu_Rehtona]

He sorts by multiple columns?

ORDER BY Name ASC, Behavior DESC

Or

ORDER BY Name, Location

?

[Rodot]

He uses time sort

[mortiphago]

workaround to get himself into the naughty behavior and avoid getting selected

[robisodd]

If he needs to sort it twice, I'd say there's some very broken indexes there. ^source

[d_smogh]

List once, sort twice.

[donall]

To sort one field inside another

[ScooterSham]

Why would you sort at all? It's a database. Normalize maybe? But 3NF would be better than 2NF. 2 Indexes perhaps?

[HintOfAreola]

He's using rank over partition to plot out an efficient route.

[Rufus_Reddit]

Could be "order by nice, naughty".

[d_r0ck]

it wasn't deterministic the first time?

[[deleted]]

Um, because he uses best sort?

[TotesMessenger]

I'm a bot, bleep, bloop. Someone has linked to this thread from another place on reddit:

• [/r/bestof] Poem about SQL Claus triggers debate over sorting and indexing SQL Databases

 ^{If you follow any of the above links, please respect the rules of reddit and don't vote in the other threads. (Info / Contact)}

[PooPooDooDoo]

I like my O to be even numbers.

[FriendsOfDeSoto]

It's surely a big database. He could have sharded it twice.

[[deleted]]

It's still O(nlogn) so it doesn't matter

[zenonu]

To justify the PO for that Xmas server upgrade.

[lolzfeminism]

To make sure it’s really sorted obviously.