i try this query to get row number in selected rows, but the output comes like 23,56,78,.... i need to get 1,2,3 to every selected row. please help me
SET @row=0;
SELECT `table1`.`col1`,`table1`.`col2`,@row:=@row+1
FROM `table1`
LEFT OUTER JOIN `table2` ON `table1`.`col1` = `table2`.`col5`
WHERE `table2`.`col5` IS NOT NULL
GROUP BY `col1` ORDER BY `table1`.`col7` DESC LIMIT 0,10
Move the query with the ORDER BY clause into a subquery.
SET @row = 0;
SELECT col1, col2, @row := @row+1
FROM (SELECT table1.col1, table1.col2
FROM table1
LEFT JOIN table2 ON table1.col1 = table2.col5
WHERE table2.col5 IS NOT NULL
GROUP BY col1
ORDER BY table1.col7 DESC
LIMIT 0, 10) AS subquery
You can try using an inline view. Use a query that gets the rows you want to return, and then wrap that in parens, and reference that query in the FROM clause of an outer query.
Something like this:
SELECT v.`col1`
, v.`col2`
, @row := @row + 1 AS `rn`
FROM ( SELECT `table1`.`col1`
, `table1`.`col2`
FROM `table1`
...
ORDER BY ...
LIMIT 10
) v