Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2010
    Posts
    3

    Exclamation Unanswered: How to use order by with group by for two tables?

    Hello!

    What i am trying is, fetching data from two tables.

    table1(srno,col1,col2)
    table2(srno,table1_srno,col1,col2)


    Now table2 contain records which has repeated table1_srno (as foreign key).

    I want that the maximum srno should be return from table2 for each srno in table1.

    i tried this,

    select *
    from table1 a, table2 b
    where a.srno = b.table1_srno order by a.srno group by b.table1_srno

    But i got an error! Please suggest me a right syntax.

    I m stuck in this problem since last 2 days. & i Hope you understand what i said above, or i will give more explanation.

    Thank You,
    - Kiran.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT t1.srno AS t1_srno
         , t1.col1 AS t1_col1
         , t1.col2 AS t1_col2
         , t2.srno AS t2_srno
         , t2.col1 AS t2_col1
         , t2.col2 AS t2_col2
      FROM table1 AS t1
    INNER
      JOIN ( SELECT table1_srno
                  , MAX(srno) AS max_t2_srno
               FROM table2
             GROUP
                 BY table1_srno ) AS m
        ON m.table1_srno = t1.srno
    INNER
      JOIN table2 AS t2
        ON t2.table1_srno = m.table1_srno
       AND t2.srno = m.max_t2_srno
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2010
    Location
    New Zealand
    Posts
    15
    What is the error message you're getting?

    Try putting the GROUP statement before the ORDER statement, i.e.
    Code:
    SELECT *
    FROM table1 a, table2 b
    WHERE a.srno = b.table1_srno
    GROUP BY b.table1_srno
    ORDER BY a.srno
    P.S. You should try not to use SELECT * as it is very inefficient. Only SELECT the fields that you will use.

  4. #4
    Join Date
    Jun 2008
    Location
    pakistan
    Posts
    109
    you have to select the columns then it will get nice result as you want * means that all the data of both of tables how it do dude.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by don_log View Post
    how it do dude.
    how it do to you too dude

    yes, the dreaded, evil "select star" should be avoided, but at least it does produce the expected results

    however, GROUP BY for this problem is wrong, because it doesn't necessarily pull in the correct column values

    please read this for further information about why -- GROUP BY and HAVING with Hidden Columns

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jun 2010
    Posts
    3
    Quote Originally Posted by ProphetX View Post
    What is the error message you're getting?

    Try putting the GROUP statement before the ORDER statement, i.e.
    Code:
    SELECT *
    FROM table1 a, table2 b
    WHERE a.srno = b.table1_srno
    GROUP BY b.table1_srno
    ORDER BY a.srno
    P.S. You should try not to use SELECT * as it is very inefficient. Only SELECT the fields that you will use.
    i was getting this error,

    #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'group by r.srno

  7. #7
    Join Date
    Jun 2010
    Posts
    3
    thnks all.. m getting the solution now...

    n sorry i was on leave for some days... so could not reply... thnks for ur help...

Posting Permissions

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