Discarding rows using mySQL views

Question

I created a view and could successfully execute the query:

Here is the output.

customer_ID Item_number Frequency
1            53           10
1             64           10
2             82           11
2              64          11
2              11           9

I need to return only the highest frequency rows ie.

customer_ID   Item_number Frequency
1              53          10
1              64          10
2              82          11
2              64          11

Help in this regard is highly appreciated.

I wouldn't start with the view. – Strawberry Jun 14 '18 at 04:17 — Strawberry, Jun 14 '18 at 04:17
wirte condition where frequency >= 10 – Ratan Uday Kumar Jun 14 '18 at 05:05 — Ratan Uday Kumar, Jun 14 '18 at 05:05

score 0 · Answer 1 · answered Jun 14 '18 at 04:41

0

You can select the highest frequency for each customer and make an inline table and join with the main table, that will solve your problem.

For example:

select a.* from your_tab_1 a
join (select customer_id,max(frequency) as frequency from your_tab_1 group by customer_id) b
on a.customer_id = b.customer_id and a.frequency = b.frequency;

answered Jun 14 '18 at 04:41

Vivek

783
5
11

This does not work. However thanks for your help. – Ananth Subramanian Jun 16 '18 at 14:15

Discarding rows using mySQL views

1 Answers1