Photoshop Question posted in
The official user guide can be found here.

Show only 2 softwares for each category – Photoshop

I have a table software (id_software, software_name, category)

What SQL query to show only 2 software for each category ?

For example I want to get : 

 |id_software | software_name | category|
-+------------+---------------+---------+-
 |          1 | Photoshop     | 5       |
 |          2 | illustrator   | 5       |
 |          3 | Firefox       | 1       |
 |          4 | I.E           | 1       |
-+--------------------------------------+-
Tags:

2

Answers


  1. 0 
    select * from
(select *
from table1 n
where
( select count(*)
from table1 m
where n.categorie = m.categorie
and n.id_software <= m.id_software) <= 2
order by n.id_software, n.id_software desc) as tn

    Source: http://mindbuffer.wordpress.com/2013/07/09/mysql-get-the-top-2-rows-for-each-category-with-certain-condition/

    Login or Signup to reply.
  2. 0

    You could use the row_number function for this. 

    SELECT *
FROM 
     (SELECT 
          Category,
          id_software,
          software_name,
          [Nth_Software] = ROW_NUMBER() OVER (Partition by Category ORDER BY Id_software)
     FROM
          table
      ) T
WHERE
     T.Nth_Software <=2

    This gives you the first two software entries based on softwareId for every category.

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

Back To Top
Search