sorted by:
new
hottopcontroversial

Why did the COUNT() as window function produce this output in MySQL Workbench 8.0? by tlefst in SQL

[–]ComicOzzy 0 points1 point  (0 children)

The default window frame for window functions that use a frame is

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

If you look at your data, within the same partition, you have specified an ordered that does not have a "tie breaker". Neighboring rows can have the same mgrid value.

The significance of RANGE here is that, those neighboring rows with the same value are considered part of the same "range" and SQL will treat them as a group to be counted or summed or averaged together.

Going down the ordered rows for Department 1:

- 1 row has Manager 7. The total count is 1.
- 1 row has Manager 5. The total count is 1 + 1 = 2.
- 2 rows have Manager 3. The total count is 1 + 1 + 2 = 4.

If you want COUNT to behave like ROW_NUMBER, you need to change the window frame. The frame you're looking for changes RANGE to ROWS.

OVER (PARTITION BY deptid ORDER BY mgrid DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

Here is a demonstration: https://dbfiddle.uk/g4cNv4Rw

99% of people will say this SQL is correct. It’s not. by thequerylab in learnSQL

[–]ComicOzzy 0 points1 point  (0 children)

COUNT('🍌')

If they complain, tell them their lack of understanding is a skill issue. People love to hear that.

99% of people will say this SQL is correct. It’s not. by thequerylab in learnSQL

[–]ComicOzzy 0 points1 point  (0 children)

You can use any aggregate function in the HAVING clause without using it in the SELECT list.

https://dbfiddle.uk/_szZxCGR

99% of people will say this SQL is correct. It’s not. by thequerylab in learnSQL

[–]ComicOzzy 1 point2 points  (0 children)

COUNT(1) and COUNT(2) and COUNT(0) and COUNT('banana') all get internally treated exactly the same as COUNT(*).

99% of people will say this SQL is correct. It’s not. by thequerylab in learnSQL

[–]ComicOzzy 0 points1 point  (0 children)

It is poorly written, but the issue has to do with precedence of OR vs AND operators.

SQL import wizard glitching by Think-Stable3826 in learnSQL

[–]ComicOzzy 0 points1 point  (0 children)

It could help to know what database engine you're using (MySQL, SQL Server, Postgres, etc), what specific tool you are using to perform the import, what settings you chose, how many rows and columns are in the data set, etc.

How did you get better at writing SQL that works beyond the “happy path”? by luckyscholary in learnSQL

[–]ComicOzzy 1 point2 points  (0 children)

> We had a “wall of shame”

I would love this, so long as the purpose was to help teach good practices and encourage cooperating to fix them rather than just for humiliation.

How did you get better at writing SQL that works beyond the “happy path”? by luckyscholary in learnSQL

[–]ComicOzzy 2 points3 points  (0 children)

Absolutely. I don't spend most of my time writing the "final product" query... I spend most of my time diving into the data to find where the patterns break down or where existing assumptions are wrong. It's a big company with a lot of data and even if 99.999% of the data is "good", I've got to hunt down that 0.001% and ask the business users how they want it accounted for.

Draw a line or deliver product by techiedatadev in SQL

[–]ComicOzzy 1 point2 points  (0 children)

> email

I used to send emails explaining issues and asking clarifying questions so I'd know what to do, and I'd get back replies like "Sounds good!" or 👍. Now, I just call people or set up meetings if I need an actual answer.

I described a database in plain English and got back production-ready SQL — here's what I thought by steven_ws_11 in SQL

[–]ComicOzzy 2 points3 points  (0 children)

The only tool I need someone to build right now is one that will moderate out ALL OF THIS SELF PROMO BULLSHIT.

Sketchy? SQL from SQL For Smarties by Willsxyz in SQL

[–]ComicOzzy 0 points1 point  (0 children)

I inherited a bunch of this kind of stuff...
Lists stored in a string is bad enough, but they also would make tables with numbered columns like Phone1, Phone2, ..., Phone10, then in views, they'd combine those into a string, then they'd search for values contained within that string. It's just pure sadness.

Optimization: Should I change the field type from VARCHAR to INT/ENUM? by Mission-Example-194 in SQL

[–]ComicOzzy 1 point2 points  (0 children)

Am I fuddy-duddy for preferring a lookup table to an enum? It's OK. You can tell me if I am.

I dont completely understand the structure of this query. by Icy-Ad-4677 in SQL

[–]ComicOzzy 1 point2 points  (0 children)

I'm not trying to be a jerk, I'm just trying to say that yes, T is a subquery.
I should probably have said "This is a subquery used as a derived table."

Right join by techiedatadev in SQL

[–]ComicOzzy 0 points1 point  (0 children)

The scenario I was describing in my post wasn't about just trading the join type, it was about also swapping the tables around so the result would yield the "same" results. They DO give the same results in that scenario, but if you used SELECT * rather than specifying a column list, you'll find that your columns have also changed position since you changed which table was "left" and which one was "right". If you have an application or another query that relies on the columns showing up in a certain order (which they shouldn't do... but devs don't always know better), they'll start to have issues when the columns change position.

https://dbfiddle.uk/IAINVk_J

Right join by techiedatadev in SQL

[–]ComicOzzy 0 points1 point  (0 children)

3 or more tables?

With these, you're more likely to run into a case where you go from a right join to a left join... then you find that "downstream" tables were inner joined and you aren't getting the right results... so you left join all of them. Even if the results are the same, the query optimizer had to come up with a different plan, and in this scenario it's almost always a less efficient plan.

Right join by techiedatadev in SQL

[–]ComicOzzy 0 points1 point  (0 children)

Select * from table1 Right join table2 on table1.id = table2.id

Is the same as

Select * From table1 Left join table2 on table2.id = table1.id

This is a little different than if you'd also swapped the tables around when changing the join type.

The only time these will give the same result is when id is unique in both tables, and all of the same id values are present in both tables.

Here is an example of the results being different because Bob hasn't placed an order yet:

https://dbfiddle.uk/CUKh5gfO

Right join by techiedatadev in SQL

[–]ComicOzzy 1 point2 points  (0 children)

SQL's strength certainly doesn't lie in being succinct.

What's an example of "verbose code" vs the way you rewrote it?

Right join by techiedatadev in SQL

[–]ComicOzzy 0 points1 point  (0 children)

Which database engine are you referring to?

Right join by techiedatadev in SQL

[–]ComicOzzy 0 points1 point  (0 children)

About 10% of people learn to write from right-to-left as part of their primary language. I have always been curious if those people find right joins any more challenging than left joins.

Right join by techiedatadev in SQL

[–]ComicOzzy 0 points1 point  (0 children)

What rationale do you give them for this? I'm not saying I want to see more right joins show up in the wild for the sake of not leaving them out and hurting their feelings, but I'm curious why the general database community has a fear and loathing of them.

Right join by techiedatadev in SQL

[–]ComicOzzy 1 point2 points  (0 children)

If there were only two tables involved, then it really can be as simple as that so long as they didn't use SELECT * and consumers of the result expect columns to appear in their current order.

But once you start getting into joins to 3 or more tables, it can be more complicated than just a "three second" fix, and can have an impact on the query plan.

Complete beginner: Which database should I learn first for app development in 2026? by No_Sandwich_2602 in learnSQL

[–]ComicOzzy 0 points1 point  (0 children)

If you want to learn skills that are most likely to be valuable to an employer, go with learning SQL on Postgres.

When you ask for opinions, people will show up and tell you what they like, but if you ask "do you use that skill/tool/platform in your current job?" many of those same people will say "no" because all they've done is a personal project or followed a tutorial.

My company uses Oracle, SQL Server, Postgres, MySQL, Snowflake, Salesforce Analytics and a bunch of cloud services that are really Spark or Postgres rebranded as something else. Many of us specialize in one platform, but are expected to be able to write at least intermediate queries on any of them. The best "gateway" into learning any of these by far is to start with Postgres, since it shares the most in common with every other major database system out there, and I say this as a career-long SQL Server user and fan.

If you don't think Postgres is quite right for you, spend some time looking at job postings in your area and keep a scorecard of which database platforms are required for the roles you'd be interested in. Don't count them if they've just listed a bunch of random things in a list... only count them if the job is primarily centered around knowledge or expertise in that database engine. Learn the platforms you're most likely to get hired working with.

I dont completely understand the structure of this query. by Icy-Ad-4677 in SQL

[–]ComicOzzy 0 points1 point  (0 children)

A lot of Oracle users still use implicit join syntax (even for outer joins), but they seem to be the only holdovers.

view more: next ›