What do the numbers refer to in a statement like 'GROUP BY 1' or 'ORDER BY 2'?

Question

What do the numbers refer to in a statement like ‘GROUP BY 1’ or ‘ORDER BY 2’?

Answer

When you see numbers after a GROUP BY or ORDER BY statement, they are referring to the columns that were selected in the query. These are called column reference numbers. The 1 signifies the first column selected, the 2 signifies the second column selected, and so on. Here’s an example:

SELECT alpha, bravo, charlie FROM table GROUP BY 1 ORDER BY 2;

In this query, 1 is referring to alpha and 2 is referring to bravo.

The main reason we use column reference numbers is because sometimes it can be a pain to type out a really long or complex column name. As you can imagine, it’s a whole lot easier typing a number than something like customer_order_number.

6 Likes

Thanks! for your explanation. But, this two query yield different results

SELECT ROUND(watch_duration_in_minutes) AS ‘duration’, COUNT(*) AS ‘count’
FROM watch_history
GROUP BY 1
ORDER BY 1;

SELECT ROUND(watch_duration_in_minutes) AS ‘duration’, COUNT(*) AS ‘count’
FROM watch_history
GROUP BY watch_duration_in_minutes
ORDER BY watch_duration_in_minutes;

So, I’m thinking that exists a reason more stronger than simply avoid complex column name. It’s related to aggregate functions, but I dont know how to get a definition.

Sorry, for my poor english :slight_smile:

The result will be the same.

2 Likes

SELECT ROUND(watch_duration_in_minutes) AS ‘duration’, COUNT(*) AS ‘count’
FROM watch_history
GROUP BY ROUND(watch_duration_in_minutes)
ORDER BY ROUND(watch_duration_in_minutes);

This is the difference, you forgot ROUND in the group and order by.

5 Likes

Referring to the columns by number doesn’t work for me with HAVING. Is there way around it?

To me this is the wrong excercise as it is asking you
to find codeflix in the payments table or have i missed something as you will never find codeflix in the payments as it is not there or is it me thanks Ian.

i think this is the wrong excercise as it is asking you to find codeflix in payments but this impossible or am i missing something thanks

Ian

You are right,Ian.

However, @candid_emile referring to columns by number does work in the exercise (Code Challenge 5, I am assuming). Remember HAVING always comes after GROUP BY.

2 Likes

thanks for reply, appreciated.

Unfortunately it doesn’t work in number comparisons :face_with_hand_over_mouth:

SELECT user_id, SUM(watch_duration_in_minutes) FROM watch_history GROUP BY 1 HAVING 2 > 400;

So I think aliases will help here.