News for hackers. Stuff that matters.
Re:Use SQL subqueries to count distinct 50x faster by 6cxs2hd6 on Friday January 24, 2014 @10:46AM (#7115419)
But isn't `DISTINCT` sort of shoving an imperative square peg into the round hole of declaritive set relations?

At least in my time with MSSQL, seeing `SELECT DISTINCT ...` was a red flag. It nearly always meant something needed to be refactored using GROUP BY.

Re:Use SQL subqueries to count distinct 50x faster by schrodinger on Friday January 24, 2014 @11:53AM (#7115883)
FYI, in mssql at least, distinct and group by are entirely equivalent to the query planner. I'm not saying you're wrong though, since group by let's you explicitly pick which columns to go by, whereas distinct uses them all.
Re:Use SQL subqueries to count distinct 50x faster by alinhan on Friday January 24, 2014 @12:52PM (#7116233)
I don't understand what you mean by "group by let's you explicitly pick which columns to go by, whereas distinct uses them all". Can you give an example?
Re:Use SQL subqueries to count distinct 50x faster by schrodinger on Friday January 24, 2014 @03:40PM (#7117835)
select userid, name from users group by userid, name

is equivalent to

select distinct userid, name from users

It's easy to add another column to the "distinct" based query and change the behavior accidentally. With the "group by" query, you'd get an error saying that the new column needs to be either aggregated or added to the group by.

Re:Use SQL subqueries to count distinct 50x faster by pdubs on Friday January 24, 2014 @10:57AM (#7115483)
SELECT COUNT(DISTINCT ...) is a very different thing. It's counting distinct values of a particular column in a result set and totally not a square peg/round hole situation.
Re:Use SQL subqueries to count distinct 50x faster by jeffdavis on Friday January 24, 2014 @11:24AM (#7115672)
I've never heard DISTINCT described as imperative.

It still leaves room to determine how and when to achieve the DISTINCT property, perhaps even doing no work at all.

* It can do a sort+unique or a hash, depending on cardinality, availability of memory, and a few other factors.

* It can push down a distinct or pull up a distinct, resulting in a faster plan.

* If there is another part of the plan that already implies distinct, like a GROUP BY, then it doesn't need to do any work at all. (Or similarly, if you're doing a distinct on a primary key).

Re:Use SQL subqueries to count distinct 50x faster by saosebastiao on Friday January 24, 2014 @12:12PM (#7116006)
How is that imperative in the slightest?