CODEWITHSUNDEEP
mysqlsql
frops
frops

Reputation: 2365

Select conditionally limited data from a single table

There is a table of the form: t_news

Categories are not consistent for each news. That is, it may be first 2 news first category, then a third, then a fifth, etc.

Need to get to 5 news every category, sorted by date.

What is the output should be similar to the following (example, 3 news, Category 3)

id title  cat
1  News1  1
2  News2  1
3  News3  1
4  News4  2
5  News5  2
6  News6  2
7  News7  3
8  News8  3
9  News9  3

Upvotes: 0

Views: 87

Answers (2)

frops
frops

Reputation: 2365

Total code:

SELECT id, title, cat, from_unixtime(date) `date` FROM (
  SELECT id, title, cat, `date`
  FROM
  (
    SELECT id, title, cat, `date`, @r:=IF(@cat = cat, @r+1, 1) AS RowNum , @cat:= category AS Cat2 
    FROM news, (SELECT @cat:= 0) AS cat, (SELECT @r:= 0) AS r
    WHERE hide=0
    ORDER BY cat, `date` DESC, id
  ) t
  WHERE RowNum <= 4 LIMIT 16
) t2 
ORDER BY `date` DESC;

@GarethD, Thank you =)

Upvotes: 0

GarethD
GarethD

Reputation: 69749

In MySQL you can use variables to create a row number column:

SELECT  id, title, cat
FROM    (   SELECT  id,
                    title,
                    cat,
                    @r:=IF(@cat = cat, @r+1, 1) AS RowNum , 
                    @cat:= cat AS Cat2 
            FROM    t_news,
                    (SELECT @cat:= 0) AS cat,
                    (SELECT @r:= 0) AS r
            ORDER BY cat, id
        ) t
WHERE   RowNum <= 5;

The key is at each row if the cat column is the same as the @cat variable (set from the previous row), then the row number increments by one. Otherwise it resets to 0. The order of the increment is set by the order by clause in the subquery (I have used ID since the schema you posted does not include a date column).

Example on SQL Fiddle

Upvotes: 2

Related Questions