12

I have a table with records and it has a row called category. I have inserted too many articles and I want to select only two articles from each category.

I tried to do something like this:

I created a view: 

CREATE VIEW limitrows AS 
   SELECT * FROM tbl_artikujt ORDER BY articleid DESC LIMIT 2

Then I created this query:

SELECT * 
FROM tbl_artikujt 
WHERE 
   artikullid IN
   (
      SELECT artikullid
      FROM limitrows
      ORDER BY category DESC
   )
ORDER BY category DESC;

But this is not working and is giving me only two records?

Wes Crow
  • 2,971
  • 20
  • 24
AXheladini
  • 1,776
  • 6
  • 21
  • 42

3 Answers3

12

LIMIT only stops the number of results the statement returns. What you're looking for is generally called analytic/windowing/ranking functions - which MySQL doesn't support but you can emulate using variables:

SELECT x.*
  FROM (SELECT t.*,
               CASE 
                 WHEN @category != t.category THEN @rownum := 1 
                 ELSE @rownum := @rownum + 1 
               END AS rank,
               @category := t.category AS var_category
          FROM TBL_ARTIKUJT t
          JOIN (SELECT @rownum := NULL, @category := '') r
      ORDER BY t.category) x
 WHERE x.rank <= 3

If you don't change SELECT x.*, the result set will include the rank and var_category values - you'll have to specify the columns you really want if this isn't the case.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
  • Can i create view in some way from this select, i tried but it says : 1349 - View's SELECT contains a subquery in the FROM clause – AXheladini Jun 30 '10 at 09:04
  • 1
    @AXheladini: Sorry, MySQL won't allow it for a few reasons - the subquery, using variables... MySQL views are extremely restricted, I'm afraid - they list the restrictions in the CREATE VIEW documentation: http://dev.mysql.com/doc/refman/5.1/en/create-view.html – OMG Ponies Jun 30 '10 at 15:04
  • for me this seems to select as top 3 from every category as if it was ordered as `t.category ASC, t.articleid ASC` but how would you do it to select it as `t.category ASC, t.articleid DESC`? – Timo Huovinen Jul 02 '14 at 11:21
0
SELECT * FROM (   
    SELECT  VD.`cat_id` ,  
       @cat_count := IF( (@cat_id = VD.`cat_id`), @cat_count + 1, 1 ) AS 'DUMMY1', 
       @cat_id := VD.`cat_id` AS 'DUMMY2',
       @cat_count AS 'CAT_COUNT'   
     FROM videos VD   
     INNER JOIN categories CT ON CT.`cat_id` = VD.`cat_id`  
       ,(SELECT @cat_count :=1, @cat_id :=-1) AS CID  
     ORDER BY VD.`cat_id` ASC ) AS `CAT_DETAILS`
     WHERE `CAT_COUNT` < 4

------- STEP FOLLOW ----------  
1 . select * from ( 'FILTER_DATA_HERE' ) WHERE 'COLUMN_COUNT_CONDITION_HERE' 
2.  'FILTER_DATA_HERE'   
    1. pass 2 variable @cat_count=1 and  @cat_id = -1  
    2.  If (@cat_id "match" column_cat_id value)  
        Then  @cat_count = @cat_count + 1    
        ELSE @cat_count = 1      
    3. SET @cat_id = column_cat_id    

 3. 'COLUMN_COUNT_CONDITION_HERE'   
    1. count_column < count_number    

4. ' EXTRA THING '
   1. If you want to execute more than one statement inside " if stmt "
   2. IF(condition, stmt1 , stmt2 )
      1. stmt1 :- CONCAT(exp1, exp2, exp3) 
      2. stmt2 :- CONCAT(exp1, exp2, exp3) 
   3. Final "If" Stmt LIKE 
      1. IF ( condition , CONCAT(exp1, exp2, exp3) , CONCAT(exp1, exp2, exp3) )    
share
Jhamman Sharma
  • 147
  • 1
  • 2
-5

Use group by instead of order by.

san
  • 1