Why do we use Group by 1 and Group by 1,2,3 in SQL query?

In SQL queries, we do use Group by clause to apply aggregate functions.

• But what is the purpose behind using numeric value instead of column

name with Group by clause? For example: Group by 1.

This is actually a really bad thing to do IMHO, and it's not supported in most other database platforms.

The reasons people do it:

• they're lazy - I don't know why people think their productivity is improved by writing terse code rather than typing for an extra 40 milliseconds to get much more literal code.

The reasons it's bad:

-
it's not self-documenting - someone is going to have to go parse the SELECT list to figure out the grouping. It would actually be a little more clear in SQL Server, which doesn't support cowboy who-knows-what-will-happen grouping like MySQL does.

-
it's brittle - someone comes in and changes the SELECT list because the business users wanted a different report output, and now your output is a mess. If you had used column names in the GROUP BY, order in the SELECT list would be irrelevant.

SQL Server supports ORDER BY [ordinal]; here are some parallel arguments against its use:

• https://sqlblog.org/2009/10/06/bad-habits-to-kick-order-by-ordinal

StackExchange Database Administrators Q#86609, answer score: 51