'Can I get the full rows when using group by multiple columns?

If the date, item, and category are the same in the table, I'd like to treat it as the same row and return n rows out of them(ex: if n is 3, then limit 0, 3).

------------------------------------------
id   |  date   | item   | category   | ...
------------------------------------------
101  | 20220201| pencil | stationery | ...    <---
------------------------------------------        |  treat as same result
105  | 20220201| pencil | stationery | ...    <---  
------------------------------------------
120  | 20220214| desk   | furniture  | ...
------------------------------------------
125  | 20220219| tongs  | utensil    | ...    <---
------------------------------------------        |  treat as same
129  | 20220219| tongs  | utensil    | ...    <--- 
------------------------------------------
130  | 20220222| tongs  | utensil    | ...

expected results (if n is 3)

-----------------------------------------------
id   |  date   | item   | category   | ... rank
-----------------------------------------------
101  | 20220201| pencil | stationery | ...  1  
-----------------------------------------------       
105  | 20220201| pencil | stationery | ...  1  
-----------------------------------------------
120  | 20220214| desk   | furniture  | ...  2
-----------------------------------------------
125  | 20220219| tongs  | utensil    | ...  3
-----------------------------------------------
129  | 20220219| tongs  | utensil    | ...  3

The problem is that I have to bring the values of each group as well. If I have only one column to group by, I can compare id value with origin table, but I don't know what to do with multiple columns.

Is there any way to solve this problem?

For reference, I used a user variable to compare it with previous values, I couldn't use it because the duration was slow.

          SELECT 
            *,
            IF(@prev_date=date and @prev_item=item and @prev_category=category,@ranking, @ranking:=@ranking+1) AS sameRow,
            @prev_item:=item,
            @prev_date:= date,
            @prev_category:=category,
            @ranking
          FROM ( SELECT ...

I'm using Mysql 8.0 version and id value is not a continuous number because I have to order by before group by.

mysqlsqlgroup-byaggregationrank


Solution 1:[1]

if I understand correctly, you can try to use dense_rank window function and set order by with your expected columns

if date column can represent the order number I would put it first.

SELECT *
FROM (
    SELECT *,dense_rank() OVER(ORDER BY date, item, category) rnk
    FROM T 
) t1

SQLFIDDLE

Solution 2:[2]

Window functions come in very handy in this situation. But for those of us still using MySQL 5.7, where functions such as row_number don't exist, we have to either resort to using a user variable and resetting the value every time before the main statement, or defining the user variable directly in the statement.
method 1

set @row_id=0; -- remember to reset the row_id to 0 every time before the main query below
select id,date,item,category,rank from testtb join 
    (
    select date,item,category, (@row_id:=@row_id+1) as rank
        from 
            (select date,item,category  from testtb group by date,item,category) t1
    ) t2 
using(date,item,category);

method 2 

select id,date,item,category,rank from testtb join 
    (
    select date,item,category, (@row_id:=@row_id+1) as rank
        from 
            (select date,item,category  from testtb group by date,item,category) t1, (select @row_id := 0) as n
    ) t2 
using(date,item,category);

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 blabla_bingo

Related Questions