5

TL;DR - I would like examples of where using a GROUP BY (example shown below) using columns not in the SELECT list can be used to resolve SQL challenges in a [practical | elegant | powerful] way. I mean in a general way - the example demonstrated below is interesting because it demonstrates the principle (but it doesn't work!). I want working examples where use of this technique can be used to achieve something "significant".

Following up to this question, which required the counting of routes from point_x to point_y and vice-versa, taking routes between the same points to be equivalent - i.e. A -> B is the same for the count purposes as B -> A. A working general solution to that question is given there.

However, one poster (SQLRaptor) showed a solution which solved the problem for the (presumably) subset of data shown in the question, but this poster also said that their solution wasn't general and asked the OP could they see why? SQLRaptor also said this was an example of a GROUP BY using columns not in the SELECT list and that this could be an elegant (powerful) solution to some SQL challenges!

SQLRaptor's solution (using my own notation from answer) was:

SELECT  MIN(origin) AS point_1, 
        MAX(destination) AS point_2,
        COUNT(*) AS journey_count
FROM route
GROUP BY ASCII(origin) * ASCII(destination)
ORDER BY point_1, point_2 

I thought the non-generality might be something to do with the multiplication (duplicates), but had no concrete proof. This transpired to be the case - see my proof here and here.

Unfortunately, this example of using a GROUP BY using columns not in the SELECT list doesn't work very well - what I would like are examples of where this technique can work well?

  • Not sure that it counts, but a useful method for GAPS and ISLAND problem is to derive a group_number from the difference between row_number() over different partitions, where one partition is a subset of the other. The group_number is used in the group by, but not in the select – Lennart Jun 5 '18 at 3:42
2

I have used this technique (grouping on a column not used in the SELECT list) to combine an ordered list of values (which has gaps) into ranges of contiguous values.

First I do a rownum() on the ordered set of values. Then we diff the rownum() and the value. By grouping on the diff, we get the ranges.

CREATE TABLE #TMP (ID INT)

INSERT INTO #TMP 
SELECT 1 UNION 
SELECT 2 UNION 
SELECT 3 UNION 
SELECT 4 UNION 
SELECT 6 UNION 
SELECT 8 UNION 
SELECT 9 UNION 
SELECT 10 UNION 
SELECT 15 UNION 
SELECT 16 UNION 
SELECT 17 UNION 
SELECT 18 UNION 
SELECT 19 UNION 
SELECT 20



WITH RN AS 
(
    SELECT 
        ROW_NUMBER() OVER (ORDER BY ID) AS RN, ID 
    FROM 
        #TMP
),
SRC AS 
(
    SELECT 
        RN, 
        ID, 
        ID-RN DIFF 
    FROM 
        RN
)
SELECT 
    MIN(ID) RANGE_START, 
    MAX(ID) RANGE_END, 
    COUNT(*) CNT_VALUES_IN_RANGE 
FROM 
    SRC 
GROUP BY 
    DIFF
  • 1
    Yep. This is typically utilized when you need to group by the result of a calculation, typically involving other rows, hence commonly used together with window functions. I will post another example later. – SQLRaptor Jun 4 '18 at 17:09
2

Simple example that the query fails:

CREATE TABLE Routes
( ID INT NOT NULL,
  ORIGIN VARCHAR(2) NOT NULL,
  DESTINATION VARCHAR(2) NOT NULL
);

 INSERT INTO Routes
   ( ID, ORIGIN, DESTINATION )
 VALUES 
   ( 1, 'A', 'T' ),
   ( 2, 'F', 'N' ) ;
GO
2 rows affected
SELECT  MIN(origin) AS point_1, 
        MAX(destination) AS point_2,
        COUNT(*) AS journey_count
FROM routes
GROUP BY ASCII(origin) * ASCII(destination)
ORDER BY 1, 2;
GO
point_1 | point_2 | journey_count
:------ | :------ | ------------:
A       | T       |             2

db<>fiddle here


Explanation of the failure:

Letter ASCII code math
A 65 = (5 * 13)
T 84 = (6 * 14)

F 70 = (5 * 14)
N 78 = (6 * 13)


AT --> 65 * 84 = (5*13) * (6*14) = (5*14) * (6*13) = 70 * 84 <-- FN

  • Or, by modifying this fiddle slightly to include an IN (and not = 1 element) to get the set off all ASCII(char_x) * ASCII(char_y) = ASCII(char_a) * ASCII(char_b) where x, y, a and b are all distinct and all are ε [A-Z]. There are 5 such quadruplets, (A, T, F, N) being one! This can be seen here. It's a particular case of the proof I outlined in the question. p.s. how do you get that deadly "hover" thing to work? :-) – Vérace Jun 5 '18 at 9:09
1

I think the example you give is funny, but not reallly useful.

I am not sure if this is what you want.

Here is a query that I see somtimes here in stackoverflow.

select first_name, last_name, avg(grade)
from grades
Group by first_name, last_name
/

but instead one should use

select first_name, last_name, avg(grade)
from grades
Group by first_name, last_name,student_id
/

Of Course in most situations it it would make sense to query

select Student_id, first_name, last_name, avg(grade)
from grades
Group by first_name, last_name,student_id
/

But if you want to print an anonyous Report the n you have to user

select avg(grade)
from grades
Group by student_id
/

How many exercisees did the students have already solved in average?

select avg(no_solved_exercises
)
from 
(select Count(*) no_solved_exercises
from exercises
where solved_date is not null
Group by student_id
) ex
0

IMHO this solution only works with CHAR(1) columns.

As far as ASCII(character_expression) only returns first character ascii value, you cannot use it as a general form.

This is my example:

CREATE TABLE Routes
( ID INT NOT NULL,
  ORIGIN VARCHAR(2) NOT NULL,
  DESTINATION VARCHAR(2) NOT NULL
);

 INSERT INTO Routes
   ( ID, ORIGIN, DESTINATION )
 VALUES 
   ( 1, 'AY', 'BW' ),
   ( 2, 'CA', 'DA' ),
   ( 3, 'BW', 'AY' ),
   ( 4, 'CA', 'AY' ),
   ( 5, 'DA', 'CA' ),
   ( 6, 'AZ', 'BW' ),
   ( 8, 'BW', 'AZ' );

And this is the (wrong) result using this solution:

SELECT  MIN(origin) AS point_1, 
        MAX(destination) AS point_2,
        COUNT(*) AS journey_count
FROM routes
GROUP BY ASCII(origin) * ASCII(destination)
ORDER BY 1, 2;
GO
point_1 | point_2 | journey_count
:------ | :------ | ------------:
AY      | BW      |             4
CA      | AY      |             1
CA      | DA      |             2

db<>fiddle here

  • Yes, I did think of that, but it struck me as a bit of a trivial (not in the insulting sense :-) ) solution. I went more for the idea that you could encode routes 1 - 26 using a single letter (maybe legacy system for a small company?), but you're correct in that this is another flaw in the oversimplification of using the ASCII(blah) * ASCII(blah) "trick". However, it doesn't really answer my question about when would such a technique (and I don't just mean ASCII^2) be useful in the general sense - i.e. other scenarios with other functions. That's more what I was driving at! Thanks for replying – Vérace Jun 4 '18 at 15:07
  • @Vérace you are correct. I used the ASCII trick for (over)simplification, but you can use the same technique with fancier expressions, such as converting the string to BINARY and then performing a XOR, which will work for more than one character, and solve the multiplication duplicates. It does force a binary collation as a side effect though. I'll look later for a nice challenge that uses this group by technique in my training materials, and will post an example as an answer here. – SQLRaptor Jun 4 '18 at 16:53
  • @Vérace decided to open a separate thread for the challenge here: dba.stackexchange.com/questions/208731/… – SQLRaptor Jun 4 '18 at 19:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.