CODEWITHSUNDEEP
mysql
Benson OO
Benson OO

Reputation: 517

Get Top Three Records from a Table based on month field

I have a table in my mysql database whose structure is shown below

Name  | Price | Month
ABC   | 1000  | Jan2k20
Cde   | 100   | Mar2k20
efh   | 20000 | Jan2k20
lmn   | 200   | Feb2k20
opq   | 3000  | Mar2k20
rst   | 29999 | Feb2k20

what i want to do is get top two names price-wise from each given month. my result should look like

Jan2k20 | Feb2k20 | Mar2k20
efh     | rst     | opq
ABC     | lmn     | Cde

i tried using if function mysql against each month but all in vain

   SELECT package_price,IF(bill_month='Jan2k20',name,'') AS Jan2k20
   FROM info ORDER BY package_price DESC LIMIT 2

Upvotes: 0

Views: 38

Answers (1)

RusArtM
RusArtM

Reputation: 1300

OK. Let me introduce a strange solution :)

Creating an example. I've added some more values for Jan2k20 to make example better:

CREATE TABLE info (name VARCHAR(100), Price INT, Month VARCHAR(100));
INSERT INTO info VALUES
("ABC", 1000, "Jan2k20"),
("ABC2", 2000, "Jan2k20"),
("ABC3", 100, "Jan2k20"),
("Cde", 100, "Mar2k20"),
("efh", 20000, "Jan2k20"),
("lmn", 200, "Feb2k20"),
("opq", 3000, "Mar2k20"),
("rst", 29999, "Feb2k20");

And here is a magic query :)

SELECT 
    month,
    SUBSTRING_INDEX(val, ',', 2) names
FROM (
    SELECT 
        i1.`month`,
        GROUP_CONCAT(i1.name ORDER BY i1.price DESC) as val= i1.month) as val
    FROM info i1
    GROUP BY i1.month
) sq
ORDER BY month;

Which gives this result:

month       names
Feb2k20     rst,lmn
Jan2k20     efh,ABC2
Mar2k20     opq,Cde

Upvotes: 1

Related Questions