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). expected results (if n is 3) The problem is that I have to bring the values of each group as well. If I have

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.

Answer

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

Advertisement

Answer