is there a query to fetch first two items associated with an id MYSQL

Question

I extracted a temporary table by using the following query in mysql select player_id, obj_category from table order by player_id;

the output is as shown, each id is associated with multiple object categories

Current output

player_id	obj_category	created_time
19855.	Electronics	2021-09-21 18:02:17
19855.	House.	2021-09-21 18:03:20
19855.	Car.	2021-09-21 18:03:54
19855.	Toys.	2021-09-21 18:04:17
19855	Sweets.	2021-09-21 18:05:13
19907	Business	2021-09-21 15:02:17
19907	Books.	2021-09-21 15:02:40
19907	House.	2021-09-21 15:04:14
19907	Books	2021-09-21 15:05:34
19908	Toys	2021-09-21 14:04:17
19908	Mobile	2021-09-21 14:07:19
19908	Sports	2021-09-21 14:08:43
19908	Electronics.	2021-09-21 14:02:17

In this sample, i have 3 ids,(19855,19907,19908). I want to create a query where I would retrieve only the first 2 obj_category associated by each of the ids. They would be ordered according to the created time of each of the objects per each id

Expected output:

player_id	obj_category
19855.	Electronics
19855.	House.
19907	Business
19907	Books
19908	Toys
19908	Mobile

I tried multiple codes such as

, however i was not able to reach that output and i couldn't find any guidance to help me.

thank you in advance

define "first". First inserted? First when ordered alfbetically, first based on some date ? — Ivo P, Dec 14 '21 at 23:14
a table is by design unsorted so without another cloumn that determines which is the order there is no first — nbk, Dec 14 '21 at 23:19
**Try writing something yourself** and then if it doesn't work, show us specifically what you did so we can help you along. You start it, and then we help. We don't write it for you. Show us the actual code that you've tried, and then describe what happened and what's not right, and then we can help you from there. Chances are you'll get pretty close to the answer if you just try it yourself first. — Andy Lester, Dec 14 '21 at 23:38
@AndyLester thank you Andy. This is my first time posting on Stackoverflow and i am also a bit new to sql and I am still learning. I will do so the next time — Eman, Dec 15 '21 at 08:05
@IvoP right. I added a column in my temporary table query that identifies the order of the categories. Thank you for pointing this out — Eman, Dec 15 '21 at 08:21
@nbk thank you for pointing this. I added a column that determines the order so that I can work based on that order. thank you — Eman, Dec 15 '21 at 08:22
@EmanFarahat good, but you didn't update your question with it — nbk, Dec 15 '21 at 11:26

score 0 · Answer 1 · answered Dec 14 '21 at 23:19

You need to decide what determines which one of the n items related to an ID get selected as "the first two". When you do that, you can try a ROW_NUMBER() function combined with a common table expression (CTE):

WITH data AS
(
select player_id, obj_category, ROW_NUMBER() OVER (PARTITION BY player_id ORDER BY obj_category) as row_priority from table 
)
SELECT * FROM data WHERE row_priority <= 2;

ROW_NUMBER() OVER (PARTITION BY <foo> ORDER BY <bar>) will generate an index order unique for each (in your case that's the player ID; each ID will get row_priority 1, 2, 3, 4 and so on) where the order of that index will be determined by predicate, in my example that's just an alphabetical order of category names. So for example for player id 19855 you will get Car (1), Sweets (2), Toy (3). Then at the end, you just filter for either 1 or 2 (less than or equal 2).

you made it clear. Thank you so much @MarcinJ – Eman Dec 15 '21 at 08:23 — Eman, Dec 15 '21 at 08:23

score 0 · Accepted Answer · answered Dec 15 '21 at 15:44

MYSQL 8 provides the window function ROW_NUMBER for that purpose.

If you use an older version you have to use

CREATE TABLE players (
  `player_id` INTEGER,
  `obj_category` VARCHAR(12),
  `created_time` DATETIME
);

INSERT INTO players
  (`player_id`, `obj_category`, `created_time`)
VALUES
  ('19855.', 'Electronics', '2021-09-21 18:02:17'),
  ('19855.', 'House.', '2021-09-21 18:03:20'),
  ('19855.', 'Car.', '2021-09-21 18:03:54'),
  ('19855.', 'Toys.', '2021-09-21 18:04:17'),
  ('19855', 'Sweets.', '2021-09-21 18:05:13'),
  ('19907', 'Business', '2021-09-21 15:02:17'),
  ('19907', 'Books.', '2021-09-21 15:02:40'),
  ('19907', 'House.', '2021-09-21 15:04:14'),
  ('19907', 'Books', '2021-09-21 15:05:34'),
  ('19908', 'Toys', '2021-09-21 14:04:17'),
  ('19908', 'Mobile', '2021-09-21 14:07:19'),
  ('19908', 'Sports', '2021-09-21 14:08:43'),
  ('19908', 'Electronics.', '2021-09-21 14:02:17');

SELECT 
`player_id`, `obj_category`
FROM
(SELECT
`player_id`, `obj_category`, `created_time`,
ROW_NUMBER() OVER(PARTITION BY `player_id` ORDER BY `created_time` ASC) rn FROM players) t1
WHERE rn <= 2

player_id | obj_category
--------: | :-----------
    19855 | Electronics 
    19855 | House.      
    19907 | Business    
    19907 | Books.      
    19908 | Electronics.
    19908 | Toys

db<>fiddle here

is there a query to fetch first two items associated with an id MYSQL

2 Answers2