phpmyadmin Question posted in
The official documentation can be found here.

Select the first two items in each month for each person – Phpmyadmin

I want to get the first two items of each month for each person. The idea is if the item have the same date, it should return the largest amount instead. I am having trouble writing a simple logical condition for it.

This is a screenshot of the table:
table datasource

The code should look something like this

SELECT MONTH(date) as month, name,  item (WHERE Top 3 MONTH(date))
FROM table
GROUP BY MONTH(date), name,  item

The expected output should look something similar to this:
expected output

(Edits): I’m sorry this is not for SQL server which I mislead the question with the hashtag. I am using phpmyadmin for SQL querying. Sorry for the misunderstanding.

Tags:

4

Answers


  1. 0

    You can use analytical function as follows:

    select * from
(SELECT MONTH(date) as month, name, item, 
        row_number() 
          over (partition by MONTH(date), name order by date, quantity desc) 
        as rn
   FROM table) t
where rn <= 2
    Login or Signup to reply.
  2. 0

    Use ROW_NUMBER:

    WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY name, MONTH(date)
                                 ORDER BY date, quantity DESC) rn
    FROM yourTable
)

SELECT date, name, item, quantity
FROM cte
WHERE rn <= 2;
    Login or Signup to reply.
  3. 0

    The faster way to write this:

    select top 2 with ties *
from [table]
order by row_number() over (partition by MONTH(date) as month, name order by date, quantity desc)
    Login or Signup to reply.
  4. 0

    When working with dates, you should always take the year into account, unless you explicitly want to combine data from multiple years into one row. Nothing in your question suggests that the ultimate goal is to combine data from different time periods. So I would suggest:

    SELECT YEAR(date) as year, MONTH(date) as month, name, item 
FROM (SELECT t.*,
             ROW_NUMBER() OVER (PARTITION BY YEAR(date), MONTH(date) ORDER BY date, quantity DESC) as seqnum
      FROM table t
     ) t
WHERE seqnum <= 2
ORDER BY MIN(date), name, item;

    You could also explicitly limit the data to a single year.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search