Why Can't I Group by 1 When It's Ok to Order by 1

What does SQL clause GROUP BY 1 mean?

It means to group by the first column of your result set regardless of what it's called. You can do the same with ORDER BY.

sql order by not working with group by only

Remember if a column that doesn't belongs to the grouping key is being referenced without any sort of aggregation so such statement is impossible.
So remember a little formula to came our this problem.

SELECT * FROM 
    (
     SELECT * FROM `table`
     ORDER BY AnotherColumn
    ) t1
    GROUP BY SomeColumn
    ;

Modify your query like this and hope it will work fine!!!.

SELECT * FROM(
SELECT DISTINCT(item_id),balance
FROM `stock_activity` 

ORDER BY(activity_id) DESC
) t1
GROUP BY (item_id)

What is the purpose of Order By 1 in SQL select statement?

This:

ORDER BY 1

...is known as an "Ordinal" - the number stands for the column based on the number of columns defined in the SELECT clause. In the query you provided, it means:

ORDER BY A.PAYMENT_DATE

It's not a recommended practice, because:

1. It's not obvious/explicit
2. If the column order changes, the query is still valid so you risk ordering by something you didn't intend

Is there a [straightforward] way to order results *first*, *then* group by another column, with SQL?

Select a,b from (select a,b from table order by b) as c group by a;

Can't fix this: Cannot group by an aggregate

You should do this

SELECT company_name, SUM(clicks) as clicks
FROM table1   
WHERE code = 'ES'  
GROUP BY company_name 
ORDER BY clicks DESC 
LIMIT 100;

Your first query is correct, not sure why are you getting the error. Your second query is however incorrect as you cannot group by second column, which you have derived by aggregation.

Using numbers in group by clause, while looks neater, it actually adds to the confusion. Hence try to omit them completely. Use proper column alises and use them in group by and order by to avoid confusion.

SQL GROUP BY 1 2 3 and SQL Order of Execution

My understanding is because it's ordinal notation and for the SELECT statement to pass syntax validation you have to have at least selected a column. So the 1 is stating the first column in the select statement since it knows you have a column selected.

EDIT:

I see people saying you can't use ordinal notation and they are right if you're using SQL Server. You can use it in MySQL though.

Select multiple columns from a table, but group by one

I use this trick to group by one column when I have a multiple columns selection:

SELECT MAX(id) AS id,
    Nume,
    MAX(intrare) AS intrare,
    MAX(iesire) AS iesire,
    MAX(intrare-iesire) AS stoc,
    MAX(data) AS data
FROM Produse
GROUP BY Nume
ORDER BY Nume

This works.

Select first row in each GROUP BY group?

On databases that support CTE and windowing functions:

WITH summary AS (
    SELECT p.id, 
           p.customer, 
           p.total, 
           ROW_NUMBER() OVER(PARTITION BY p.customer 
                                 ORDER BY p.total DESC) AS rank
      FROM PURCHASES p)
 SELECT *
   FROM summary
 WHERE rank = 1

Supported by any database:

But you need to add logic to break ties:

  SELECT MIN(x.id),  -- change to MAX if you want the highest
         x.customer, 
         x.total
    FROM PURCHASES x
    JOIN (SELECT p.customer,
                 MAX(total) AS max_total
            FROM PURCHASES p
        GROUP BY p.customer) y ON y.customer = x.customer
                              AND y.max_total = x.total
GROUP BY x.customer, x.total

How to select the first row for each group in MySQL?

rtribaldos mentioned that in younger database versions, window-functions could be used.

Here is a code which worked for me and was as fast as Martin Zwarík's substring_index-solution (in Mariadb 10.5.16):

SELECT group_col, order_col FROM (
  SELECT group_col, order_col
  , ROW_NUMBER() OVER(PARTITION BY group_col ORDER BY order_col) rnr 
  FROM some_table
  WHERE <some_condition>
) i
WHERE rnr=1;

---

Related Topics