CODEWITHSUNDEEP
mysqlsqlnumbersrows
John Nuñez
John Nuñez

Reputation: 1830

Display Rownum issue MySQL

I have this Query:

SET @row_num = 0;
SELECT
(SELECT @row_num := @row_num + 1) AS itempurchase_code,
(SELECT supplier_code FROM qa_items_purchases a WHERE a.item_invoicecodesupplier = b.item_invoicecodesupplier GROUP BY supplier_code ORDER BY COUNT(*) DESC LIMIT 1) AS supplier_code, 
(SELECT user_code FROM qa_items_purchases a WHERE a.item_invoicecodesupplier = b.item_invoicecodesupplier GROUP BY user_code ORDER BY COUNT(*) DESC LIMIT 1) AS user_code,
22 AS status_code,
item_invoicecodesupplier AS item_invoicecodesupplier,
(SELECT itempurchase_date FROM qa_items_purchases a WHERE a.item_invoicecodesupplier = b.item_invoicecodesupplier GROUP BY itempurchase_date ORDER BY COUNT(*) DESC LIMIT 1) AS itempurchase_date
FROM qa_items_purchases b 
GROUP BY (item_invoicecodesupplier) 
ORDER BY itempurchase_code;

I get this Result: enter image description here

If you look there is no (2) itempurchase_code column, What i can do to show the numbers in sequence?

Upvotes: 3

Views: 3557

Answers (1)

Mark Byers
Mark Byers

Reputation: 837946

You must add the row number in an outer query if your query contains a GROUP BY.

SET @row_num = 0;
SELECT (SELECT @row_num := @row_num + 1) AS itempurchase_code, *
FROM
(
    SELECT ... -- your original query goes here
) AS T1
ORDER BY itempurchase_code

Upvotes: 3

Related Questions