Advertisements

Archive

Posts Tagged ‘MySQL’

Get Row Number in MySQL


SQL Server

To return a row number with record we have ROW_NUMBER() in SQL which  returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

Syntax:

ROW_NUMBER ( )
OVER ( [ PARTITION BY value_expression , … [ n ] ] order_by_clause )

MY SQL

Here I am explaining how to return row number in my sql

For Example we have following table

CREATE TABLE tblSales(
  id     INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  fruit  VARCHAR(32),
  amount DECIMAL
);

INSERT INTO tblSales
VALUES 
(NULL, 'apple', 12.75), 
(NULL, 'orange', 1.89), 
(NULL, 'pear', 19.23),
(NULL, 'banana', 4.25), 
(NULL, 'cherry', 123.75), 
(NULL, 'plum', 23.15);
SELECT fruit, amount FROM tblSales ORDER BY amount DESC;

If we run this query it will result like
+--------+--------+
| fruit  | amount |
+--------+--------+
| cherry |    124 |
| plum   |     23 |
| pear   |     19 |
| apple  |     13 |
| banana |      4 |
| orange |      2 |
+--------+--------+


Now here is the query with ranking

SET @rank=0;
SELECT @rank:=@rank+1 AS rank, fruit, amount FROM tblSales
 ORDER BY amount DESC;

+------+--------+--------+
| rank | fruit  | amount |
+------+--------+--------+
|    1 | cherry |    124 |
|    2 | plum   |     23 |
|    3 | pear   |     19 |
|    4 | apple  |     13 |
|    5 | banana |      4 |
|    6 | orange |      2 |
+------+--------+--------+
Hope it helps!
Advertisements

Age calculation in mysql Select query


Following is the way to calculate age based on date in mysql select query

SELECT name, 
birth, 
CURDATE(), 
TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age 
FROM 
TableName; 

+----------+------------+------------+------+ 
| name | birth | CURDATE() | age | 
+----------+------------+------------+------+ 
| Fluffy | 1993-02-04 | 2003-08-19 | 10 | 
| Claws | 1994-03-17 | 2003-08-19 | 9 |
| Buffy | 1989-05-13 | 2003-08-19 | 14 | 
| Fang | 1990-08-27 | 2003-08-19 | 12 | 
| Bowser | 1989-08-31 | 2003-08-19 | 13 | 
| Chirpy | 1998-09-11 | 2003-08-19 | 4 | 
| Whistler | 1997-12-09 | 2003-08-19 | 5 | 
| Slim | 1996-04-29 | 2003-08-19 | 7 | 
| Puffball | 1999-03-30 | 2003-08-19 | 4 | 

+----------+------------+------------+------+ 
Hope this is helpful !


Function to find dates in current week in mysql


MySQL YEARWEEK() returns year and week number for a given date.

Syntax: YEARWEEK(date_value,Mode)

Parameters or Arguments

date_value
A date or datetime value from which to extract the year and week.
mode
Optional. It is used to specify what day the week starts on. It can be one of the following:

mode Explanation Week Value
0 First day of the week is Sunday 0-53
1 First day of the week is Monday and the first week has more than 3 days 0-53
2 First day of the week is Sunday 1-53
3 First day of the week is Monday and the first week has more than 3 days 1-53
4 First day of the week is Sunday and the first week has more than 3 days 0-53
5 First day of the week is Monday 0-53
6 First day of the week is Sunday and the first week has more than 3 days 1-53
7 First day of the week is Monday 1-53

Example : select YEARWEEK(now());

The following statement will return the year and week number of the date 2016-05-19.

SELECT YEARWEEK(‘2016-05-19’);

 

mysql-yearweek-function

 

Hope this help!

Comma separated values in mysql


MY SQL:

GROUP_CONCAT function concatenates values within each group defined by GROUP BY clause.

Syntax GROUP_CONCAT([DISTINCT] exp [ORDER BY sorting] [SEPARATOR ‘sep’])
Quick Example SELECT GROUP_CONCAT(city) FROM cities GROUP BY state;
Separator Comma (,) by default, ” eliminates separator
NULL Values Skipped
Max Length 1024 by default, specified by group_concat_max_len system variable

Sample:

CREATE TABLE cities
   (
     city VARCHAR(70),
     state CHAR(2)
   );
 
   INSERT INTO cities VALUES ('San Francisco', 'CA');
   INSERT INTO cities VALUES ('San Diego', 'CA');
   INSERT INTO cities VALUES ('Los Angeles', 'CA');
 
   INSERT INTO cities VALUES ('Austin', 'TX');
   INSERT INTO cities VALUES ('Houston', 'TX');

   SELECT state, GROUP_CONCAT(city)
   FROM cities
   GROUP BY state;
The result:
CA San Francisco,San Diego,Los Angeles
TX Austin,Houston
SQL Server:
SELECT state,
STUFF((SELECT ',' + city FROM cities WHERE state = c.state FOR XML PATH('')),1 ,1 ,'')
FROM cities c
GROUP BY state

Hope this help !
Rahul Bhatia

MySQL Error: The user specified as a definer does not exist

October 17, 2014 Leave a comment

Error :

the user specified as a definer (‘root’@’%’) does not exist

 

Solution :

grant all on *.* to ‘root’@‘%’ identified by ‘password’ with grant

 option;
Categories: Sql Server Tags: , , ,

Cannot drop database because it is currently in use.


This is a very common error when DROP Database is command is executed and the database is not dropped.

The following commands will raise above error:
USE AdventureWorks;
GO
DROP DATABASE AdventureWorks;
GO

Solution:
The following commands will not raise an error and successfully drop the database:

USE Master;
GO
DROP DATABASE AdventureWorks;
GO

If you are still getting the error after you try using
use master
go
drop database (databaseName)
go

Close SQL Server Management Studio completely. Open it again and connect as normal. Now you will be able to drop the database with
use master
go
drop database (databaseName)
go

Hope it helps!