Professional Web Applications Themes

One query and problem LIMIT - PHP Development

Hi all, I have 2 tables: articles and categories articles table: ------------ articleid categoryid name categories table: --------------- categotyid name description I want to get the last entred 5 articles for each category with one quey: like this: categoryid | articleid 1 | 29 1 | 12 1 | 11 1 | 10 1 | 2 2 | 25 2 | 20 2 | 16 .. .. .. N | 15 N | 9 N | 7 N | 6 N | 5 As you see: For categoryid=1 I get 5 articles because there are more than 5 articles for ...

  1. #1

    Default One query and problem LIMIT

    Hi all,

    I have 2 tables: articles and categories

    articles table:
    ------------
    articleid
    categoryid
    name

    categories table:
    ---------------
    categotyid
    name
    description


    I want to get the last entred 5 articles for each category with one quey:

    like this:

    categoryid | articleid
    1 | 29
    1 | 12
    1 | 11
    1 | 10
    1 | 2

    2 | 25
    2 | 20
    2 | 16
    ..
    ..
    ..
    N | 15
    N | 9
    N | 7
    N | 6
    N | 5

    As you see:
    For categoryid=1 I get 5 articles because there are more than 5 articles for
    this category in the DB
    For categoryid=2 I get only 3 articles because there are only 3 articles for
    this category in the DB

    How can I do this ?

    THX.


    rbaba Guest

  2. #2

    Default Re: One query and problem LIMIT

    rbaba wrote: 

    Try something like this:

    SELECT t1.name, t2.name
    FROM category t1
    LEFT JOIN article t2 ON t1.categoryid = t2.categoryid
    LEFT JOIN article t2_2 ON t1.categoryid = t2.categoryid
    WHERE t2.name <= t2_2.name OR t2.categoryid IS NULL
    GROUP BY t1.categoryid, t1.name, t2.name
    HAVING count(*) <= 5;

    Regards
    Stefan
    Stefan Guest

  3. #3

    Default Re: One query and problem LIMIT

    Thanks Stefan,

    It works when modified (the 2nd. JOIN):

    SELECT t1.categoryid, t2.articleid
    FROM category t1
    LEFT JOIN article t2 ON t1.categoryid = t2.categoryid
    LEFT JOIN article t2_2 ON t1.categoryid = t2_2.categoryid
    WHERE t2.articleid <= t2_2.articleid OR t2.categoryid IS NULL
    GROUP BY t1.categoryid, t2.articleid
    HAVING count(*) <= 5;

    THX


    "Stefan Rybacki" <net> a écrit dans le message de news:
    net... 
    >
    > Try something like this:
    >
    > SELECT t1.name, t2.name
    > FROM category t1
    > LEFT JOIN article t2 ON t1.categoryid = t2.categoryid
    > LEFT JOIN article t2_2 ON t1.categoryid = t2.categoryid
    > WHERE t2.name <= t2_2.name OR t2.categoryid IS NULL
    > GROUP BY t1.categoryid, t1.name, t2.name
    > HAVING count(*) <= 5;
    >
    > Regards
    > Stefan[/ref]


    rbaba Guest

Similar Threads

  1. SELECT query with negative LIMIT?
    By antoniog in forum MySQL
    Replies: 4
    Last Post: March 7th, 11:26 PM
  2. Replies: 4
    Last Post: December 20th, 04:02 PM
  3. Query Param Limit with SQL Server
    By dsbrady_98 in forum Coldfusion Database Access
    Replies: 1
    Last Post: March 14th, 09:34 PM
  4. How to limit # of rows returned from query
    By Steve Grosz in forum Macromedia ColdFusion
    Replies: 7
    Last Post: May 2nd, 07:35 PM
  5. DESC LIMIT 5 problem
    By Spam sucks in forum PHP Development
    Replies: 1
    Last Post: October 10th, 10:07 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139