1

I have 2 tables. 1. news table 2. category table.

in news table i have id, title, category id in category table i have id and category name.

in news table there are many news items where multiple news items for each category.

what i need was to select 2 record per each category from news table, also i need to join those two tables, so that i can get the name of the category from category table. i tried using below query 

SELECT * FROM (
    SELECT
            news.id, news.fk_lookups_category, lookups_category.name, news.title, news.description,news.datetime,lookups_category.priority
        FROM news
            JOIN lookups_category ON news.fk_lookups_category=lookups_category.id
        WHERE
            news.isPublished='1' and news.datetime >= ('today' - INTERVAL 7 day) order by datetime DESC
) as newitem order by priority
Alex
  • 626
  • 7
  • 16
Fazil
  • 11
  • 3
  • 1
    Can you show an example of 2 tables – Alex Jan 21 '16 at 09:36
  • My news table has the fields 1 id 2 title 3 description 4 datetime 5 fk_lookups_category 6 isPublished category table has 1. id 2. name 3. priority – Fazil Jan 21 '16 at 09:44
  • Can you select some rows and edit your question please. @Fazil – Alex Jan 21 '16 at 09:46
  • see my query.. from this query i am getting all the news items for seven days according to their priority...but i am confused with how to select only 2 items per each category @alex – Fazil Jan 21 '16 at 09:47
  • Possible duplicate of [Get top n records for each group of grouped results](http://stackoverflow.com/questions/12113699/get-top-n-records-for-each-group-of-grouped-results) – Shadow Jan 21 '16 at 09:49
  • can some one explain me using my query.. to select 2 items per each category.. – Fazil Jan 21 '16 at 09:53
  • I gave you a link. Have you checked that out? – Shadow Jan 21 '16 at 11:38

0 Answers0