Home>

ruby on rails - i want to avoid displaying duplicate data for each shop

When outputting data with sql, I want to avoid displaying duplicate data for each shop.

The problem i am having

select * from table name;

id | shop | name | price
1 | a | Curry | 100
2 | a | Curry | 120
3 | a | Soba | 100
4 | a | Udon | 100
5 | a | Set meal | 100
6 | b | Curry | 130
7 | b | Soba | 140
8 | b | Katsudon | 100
..
..
..


When there was a table called
I want to output as follows.

id | shop | name | price
1 | a | Curry | 100
3 | a | Soba | 100
4 | a | Udon | 100
5 | a | Set meal 100 |
6 | b | Curry | 130
7 | b | Soba | 140
8 | b | Katsudon | 100
..
..
..

I want to retrieve duplicate data for each shop with the same value.
There were two names called curry in the id of a, so I want to make one, but I want to keep the curry in the table of b.

Since it is only necessary to narrow down the curry value of name to one, there is no condition such as wanting to extract the cheaper curry.

I'm at a loss because I don't know how to write SQL statements ...

Supplementary information (FW/tool version, etc.)

Rails 6.0.3.4
sqlite3

rubyonrails sql
  • Answer # 1

    Well, I think this is fine.

    create table Test (id integer, shop varchar (1), name varchar (10), price int);
insert into Test (id, shop, name, price) values ​​(1, "a", "curry", 100);
insert into Test (id, shop, name, price) values ​​(2, "a", "curry", 120);
insert into Test (id, shop, name, price) values ​​(3, "a", "soba", 100);
insert into Test (id, shop, name, price) values ​​(4, "a", "udon", 100);
insert into Test (id, shop, name, price) values ​​(5, "a", "set meal", 100);
insert into Test (id, shop, name, price) values ​​(6, "b", "curry", 130);
insert into Test (id, shop, name, price) values ​​(7, "b", "soba", 140);
insert into Test (id, shop, name, price) values ​​(8, "b", "Katsudon", 100);
select * from Test where id in (select min (id) from Test group by shop, name)
    id shop name price
1 a curry 100
3 a buckwheat 100
4 a udon 100
5 a set meal 100
6 b curry 130
7 b buckwheat 140
8 b Katsudon 100

Related articles