I want get the 2nd and 3nd largest of each category? How to do this ? Sorry my bad English
I have table tin_tuc like this
||id || id_cat ||some information
||1 || 1 ||
||2 || 1 ||
||3 || 1 ||
||4 || 1 ||
||5 || 2 ||
||6 || 2 ||
||7 || 3 ||
||8 || 3 ||
||9 || 3 ||
Now i want to get two record each category that have value large 2nd and 3th(top 3 except largest value).
the out put i want
||id || id_cat ||some information
||2 || 1 ||
||3 || 1 ||
||5 || 2 ||
||6 || 2 ||
||8 || 3 ||
||9 || 3 ||
The following query will return top 2nd and 3rd song based on category from a table:
set @num := 0, @category := '';
select
songID, `categoryId`, hits
from
(
select songID, categoryid, hits,
@num := if(@category = `categoryid`, @num + 1, 1) as row_number,
@category := `categoryid` as dummy
from songs
where categoryid is not null
order by `categoryid`, hits desc
) as temptable
where temptable.row_number=3 or temptable.row_number=2;
here i am sorting all records by category and hits(my priority for example you can select your highest point holding field) in sub-query and then giving each record an incrementing number starting from 1 and resetting when category changes then finally i select 2nd and 3rd record in outer query.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments