Rank SQL query Result

I was playing with mysql client,suddenly found that it would be really great if I can rank the query results.Lets say we have a table like below:

Now we want to rank  the students based on their Roll No.To do it,we have to define a variable with the initial value of 0 and in next query we will show it by as one of the resulted field  query

like below:


set @counter=0;

select @counter:=@counter+1 as Rank,LastName,Roll_no as Roll  from Students order by Roll_no ASC;

and in next query we will show it by as one of the resulted field ,that results:

Check mysql docs from here to know details about user defined variable in mysql

Advertisements

Reset Mysql Root password in Ubuntu

For last couple of months when I tried to access my mysql,I sadly discover that I forgot the root password.
And google a lot to know the reset details.

I know in future I will need the steps again as why thought It would be better to write it down.
Its very simple,all I did.

1.Stop running Mysql


service mysql stop

or using

sudo /etc/init.d/mysql stop

2.Starting the mysql configuration ,login as a root in mysql and update the root user’s password.

sudo mysqld --skip-grant-tables &

Access to mysql using

mysql -u root mysql

And the type update the root user password using following command

UPDATE user SET Password=PASSWORD('new pass') WHERE User='root'; FLUSH PRIVILEGES; exit;

Then you will see “OK” confirmation like below:

Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

Query OK, 0 rows affected (0.00 sec)
Bye

Then you can access to your mysql using following command

mysql -u root -p

Thats it.

Check out mysql doc for details