Writting Bad Query To Achieve Better Query at Waldar's SQLing and Datawarehousing Place

That title is sounding odd I admit it.

In the french forums développez.net there is a regular mistake that I try to emphasize on each time I read it, it's the use of GROUP BY without any aggregate functions to perform a regular DISTINCT operation.

Here's a very simple example (if you're not working on Oracle just get rid of the from dual) :

PLAIN TEXT

WITH GBY AS
(
SELECT 'A' AS col1, 'B' AS col2 FROM dual union ALL
SELECT 'A' , 'B' FROM dual union ALL
SELECT 'A' , 'B' FROM dual
)
SELECT col1, col2
FROM GBY;

COL1 COL2
A B
A B
A B

(I said very simple)

With GROUP BY and DISTINCT, both query gives me the same result :

PLAIN TEXT

SELECT col1, col2
FROM GBY
GROUP BY col1, col2;

SELECT DISTINCT
col1, col2
FROM GBY;

COL1 COL2
A B

That's still the easy part.

The thing I recently used was find how much rows I had in this dataset.
I used the analytic function row_number :

PLAIN TEXT

SELECT DISTINCT
col1, col2,
row_number() over(ORDER BY col1 ASC) AS rk
FROM GBY
ORDER BY rk ASC;

COL1 COL2 RK
A B 1
A B 2
A B 3

Damn it, the row_number is computed before the distinct keyword.
I can use a subquery :

PLAIN TEXT

SELECT col1, col2,
row_number() over(ORDER BY col1 ASC) AS rk
FROM
(
SELECT DISTINCT
col1, col2
FROM GBY
)
ORDER BY rk ASC;

COL1 COL2 RK
A B 1

That's a piece of code for not that much.
But then I remembered that analytic functions are as I said computed before the distinct keyword but also after the group by operations.

Let's try with the bad code :

PLAIN TEXT

SELECT col1, col2,
row_number() over(ORDER BY col1 ASC) AS rk
FROM GBY
GROUP BY col1, col2
ORDER BY rk ASC;

COL1 COL2 RK
A B 1

Well, being the point of this post I suppose you guessed it !
This code is shorter and I'm avoiding an inline view.

Maybe the flaw is that another reader could find it odd, as this post title.

Share and Enjoy: