var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: How to use order by with group by for two tables?
What i am trying is, fetching data from two tables.
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,
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.
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
JOIN ( SELECT table1_srno
, MAX(srno) AS max_t2_srno
BY table1_srno ) AS m
ON m.table1_srno = t1.srno
JOIN table2 AS t2
ON t2.table1_srno = m.table1_srno
AND t2.srno = m.max_t2_srno
What is the error message you're getting?
Try putting the GROUP statement before the ORDER statement, i.e.
P.S. You should try not to use SELECT * as it is very inefficient. Only SELECT the fields that you will use.
FROM table1 a, table2 b
WHERE a.srno = b.table1_srno
GROUP BY b.table1_srno
ORDER BY a.srno
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.
i was getting this error,
Originally Posted by
#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
thnks all.. m getting the solution now...
n sorry i was on leave for some days... so could not reply... thnks for ur help...