0

Hi i have this sql code i want to query in phpmyadmin

SELECT DISTINCT `unit`,`location` FROM `myasset` ORDER BY `unit` asc

It is possible to add custom number in my sql result

I tried something like below..its not working ..i received message row_number function does not exist

  SELECT DISTINCT Row_Number(),`unit`,`location` FROM `myasset` ORDER BY `unit` 



 SELECT Row_Number() DISTINCT`unit`,`location` FROM `myasset` ORDER BY `unit`
blackrx
  • 81
  • 8
  • Why do you need that custom number? – Carl Binalla Jun 15 '17 at 06:23
  • 1
    If you are using MySQL then you get the error message because `ROW_NUMBER` does not exist in MySQL. You can use variables instead. – Giorgos Betsos Jun 15 '17 at 06:24
  • 1
    Possible duplicate of [MySQL - Get row number on select](https://stackoverflow.com/questions/2520357/mysql-get-row-number-on-select) – Mohammad Jun 15 '17 at 06:24
  • you can use variable like this select @rownum:=@rownum+1 No,`unit`,`location` FROM `myasset` , (SELECT @rownum:=0) r ORDER BY `unit` ; – JYoThI Jun 15 '17 at 06:26

2 Answers2

0

You could do 

select @rownum:=@rownum+1 No,DISTINCT unit,location FROM myasset ORDER BY unit, (SELECT @rownum:=0);
Rahul
  • 2,374
  • 2
  • 9
  • 17
  • Static analysis: 4 errors were found during analysis. Unrecognized keyword. (near "No" at position 26) Unexpected token. (near "," at position 28) Unrecognized keyword. (near "DISTINCT" at position 29) Unexpected token. (near "unit" at position 38) – blackrx Jun 15 '17 at 06:48
  • remove r from the end – Rahul Jun 15 '17 at 06:49
0

First you have to set a value like

SET @rnum = 0;

then

SELECT @rnum:=@rnum+1 AS row_num, DISTINCT unit,location FROM myasset ORDER BY unit asc

Hope it will solve your issue.

Thanks

Josmy Jose
  • 36
  • 3
  • I received this error Static analysis: 3 errors were found during analysis. An expression was expected. (near "DISTINCT" at position 34) Unrecognized keyword. (near "DISTINCT" at position 34) Unexpected token. (near "unit" at position 43) – blackrx Jun 15 '17 at 06:47
  • Please copy paste to a notepad and try again. – Josmy Jose Jun 15 '17 at 12:45