Skip to content
Advertisement

# 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

3 People found this is helpful
Advertisement