CODEWITHSUNDEEP
mysql
whitesiroi
whitesiroi

Reputation: 2843

How to OFFSET for specific row with the same id in it - MySQL?

I have a table that looks like this:

id    a.id  b.id    b.volume
1     1     2605    9900
2     1     345     8100
3     1     4359    3600
4     1     5589    1300
5     1     765     0
6     2     1438    22200
7     2     5320    18100
8     2     7185    9900
9     3     1045    8100
10    3     1928    8100
11    3     4616    3600
12    3     4990    1900
13    4     5841    12100
14    4     109     590
15    5     6530    6600
16    6     2202    1830000
17    6     5014    450000
18    6     2885    368000
19    6     3974    201000
20    6     4793    135000

How can I get every record that has more than 2 same A.ID with the lowest B.VOLUME

I want to get results like:

id    a.id  b.id    b.volume
3     1     4359    3600
4     1     5589    1300
5     1     765     0
8     2     7185    9900
11    3     4616    3600
12    3     4990    1900
18    6     2885    368000
19    6     3974    201000
20    6     4793    135000

I want to get rid of every 2 records with the same A.ID with the highest B.VOLUME

Sorry, it's hard to explain, so maybe this table will help. Records that should be removed are in brackets [].

    id    a.id  b.id    b.volume
[   1     1     2605    9900     ]
[   2     1     345     8100     ]
    3     1     4359    3600
    4     1     5589    1300
    5     1     765     0
[   6     2     1438    22200    ]
[   7     2     5320    18100    ]
    8     2     7185    9900
[   9     3     1045    8100     ]
[   10    3     1928    8100     ]
    11    3     4616    3600
    12    3     4990    1900
[   13    4     5841    12100    ]
[   14    4     109     590      ]
[   15    5     6530    6600     ]
[   16    6     2202    1830000  ]
[   17    6     5014    450000   ]
    18    6     2885    368000
    19    6     3974    201000
    20    6     4793    135000

Upvotes: 2

Views: 62

Answers (2)

Blank
Blank

Reputation: 12378

I suspect this sample data is middle data which means may contain more than one table, here I give you a solution for this sample data:

select t2.`id`, t2.`a_id`, t2.`b_id`, t2.`b_volume`
from (
    select
        t1.*, @rowno := case when @grp = t1.a_id then @rowno + 1 else 1 end as rowno, @grp := t1.a_id as dummy
    from (
        select * from middletable order by a_id, id
    ) t1
    join (select @rowno := 0, @grp := null) t
) t2
join (
    select max(b_volume) as b_volume, a_id from middletable group by a_id
) t3 on t2.a_id = t3.a_id and t2.b_volume <> t3.b_volume
where t2.rowno > 2

Demo Here

Upvotes: 2

Arulkumar
Arulkumar

Reputation: 13237

The query below will give your expected result:

set @num := 0, @group := '';

select id, aid, bid, bvolume from 
(
   select id, aid, bid, bvolume,
      @num := if(@group = aid, @num + 1, 1) as row_number,
      @group := aid as dummy
  from TestingTable
  order by aid, bvolume DESC
) as x 
where x.row_number > 2;

Please find the Working DEMO

Upvotes: 2

Related Questions