Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2003
    Posts
    4

    Question Unanswered: using GROUP BY and LIMIT ?

    Hi all,
    I' m stuck in this problem :

    I have a Query with 2 Fields : ForumID, PostID.
    I want with only One SQL command (MySQL), it can do : "with every ForumID, show LITMIT 5 of PostID", for example :

    ForumID PostID
    1 17
    1 12
    1 8
    1 7
    1 4

    3 20
    3 15
    3 11

    4 16
    4 14

    Please guide me to write that SQL (MySQL), I wrote one but it cannot LIMIT PostID in every ForumID :

    SELECT forumID, postID GROUP BY forumID, postID ORDER BY forumID, postID DESC

    thank you very much,

  2. #2
    Join Date
    Oct 2003
    Location
    Denver, Colorado
    Posts
    137

    Re: using GROUP BY and LIMIT ?

    You can use a query with a temporary table:

    CREATE TEMPORARY TABLE tempforums (
    autoid INT NOT NULL AUTO_INCREMENT,
    forumid INT NOT NULL,
    postid INT NOT NULL,
    PRIMARY KEY (forumid, postid)
    );

    INSERT INTO tempforums(forumid, postid)
    SELECT forumid, postid FROM your_table
    ORDER BY postid DESC;

    SELECT forumid, postid FROM tempforums
    WHERE autoid <= 5
    ORDER BY forumid, postid DESC;

    Originally posted by comart
    Hi all,
    I' m stuck in this problem :

    I have a Query with 2 Fields : ForumID, PostID.
    I want with only One SQL command (MySQL), it can do : "with every ForumID, show LITMIT 5 of PostID", for ...

  3. #3
    Join Date
    Nov 2003
    Posts
    4

    Re: using GROUP BY and LIMIT ?

    thanks for your reply
    but it want GROUP BY ForumID, then its PostID after that, like :

    ForumID PostID
    1 7
    1 6
    1 4
    3 5
    3 2
    3 1

    your SQL just return all mixed ForumID, PostID

    any better idea ? pls help me

  4. #4
    Join Date
    Oct 2003
    Location
    Denver, Colorado
    Posts
    137

    Re: using GROUP BY and LIMIT ?

    I did make a mistake in the SQL I gave to you, but that mistake shows me that you did not even try the code. The CREATE TABLE statement should be

    CREATE TEMPORARY TABLE tempforums (
    autoid INT NOT NULL AUTO_INCREMENT,
    forumid INT NOT NULL,
    postid INT NOT NULL,
    PRIMARY KEY (postid, forumid, autoid)
    );

    MySQL gives errors when you run the create statement I gave you. Try this one. It will return what you want, without using GROUP BY.

    Originally posted by comart
    thanks for your reply
    but it want GROUP BY ForumID, then its PostID after that, like :

    ForumID PostID
    1 7
    1 6
    1 4
    3 5
    3 2
    3 1

    your SQL just return all mixed ForumID, PostID

    any better idea ? pls help me

  5. #5
    Join Date
    Oct 2003
    Location
    Denver, Colorado
    Posts
    137

    Re: using GROUP BY and LIMIT ?

    You are confused on the use of GROUP BY. If you have a query with only two fields and you use GROUP BY on both of them, you are telling the DBMS not to do anything with grouping. If you say "GROUP BY forumid" and leave it at that, you will only get one line per forumid. GROUP BY is used to specify how to generate summaries. If you had specified

    GROUP BY forumid, postid DESC

    You would have not needed to use the ORDER BY clause because MySQL automatically orders the columns used in a GROUP BY clause.

    Originally posted by comart
    thanks for your reply
    but it want GROUP BY ForumID, then its PostID after that...
    your SQL just return all mixed ForumID, PostID

    any better idea ? pls help me

  6. #6
    Join Date
    Nov 2003
    Posts
    4

    Re: using GROUP BY and LIMIT ?

    thank you aus,
    in fact I 've not tried your code yet when I replied you cause my lack of experience, sorry :-)
    your code worked well and help me understand much about GroupBy and other things, thank you again, good luck to you ;-)

  7. #7
    Join Date
    Nov 2003
    Posts
    4

    Re: using GROUP BY and LIMIT ?

    hi, last words to someone who stucked like me :-)
    aus is right with his code, but a small thing should be correct :

    CREATE TEMPORARY TABLE tempforums ( ... , PRIMARY KEY (forumid, autoid)) => don't have "postid" in primary key.

    I tried it successfully with MySQL, thanks again aus :-)

Posting Permissions

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