How to use order by with group by for two tables?

kiran9879 · #1 (**permalink**) 06-01-10, 00:59

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.

r937 · #2 (**permalink**) 06-01-10, 06:23

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

ProphetX · #3 (**permalink**) 06-01-10, 06:50

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.

don_log · #4 (**permalink**) 06-01-10, 08:43

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.

r937 · #5 (**permalink**) 06-01-10, 12:27

Quote:

Originally Posted by don_log

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

kiran9879 · #6 (**permalink**) 06-05-10, 01:58

Quote:

Originally Posted by ProphetX

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

kiran9879 · #7 (**permalink**) 06-05-10, 02:00

thnks all.. m getting the solution now...

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

Internet Services

Web Development

Digital Marketing

Consumer Tech