Advertisements

Archive

Archive for the ‘Sql Server’ Category

How to use Variable in MySQL Like Clause ?

October 24, 2018 Leave a comment

Here is an example to use SQL variable in the mysql select query like clause

SET @name = 'advancewebsoftware';

SELECT * from `user` WHERE name LIKE CONCAT('%', @name, '%');

Hope this help!

 

Advertisements
Categories: Sql Server Tags: , ,

MySQL : The host localhost does not support SSL connections


When I moved database MySql.Data 7.0.7 to 8.0.8. I got following error

The host localhost does not support SSL connections.

So, here is the solution for this error ,Just add SslMode=none in connection string

server=localhost;user id=roor;password=xyz;persistsecurityinfo=True;port=123;database=TestDB; SslMode=none

Hope it helps !

Categories: ASP.NET, Sql Server

SQL : Paging in Stored Procedure

November 1, 2017 Leave a comment

Create simple Student table using following script

CREATE TABLE [dbo].[Student]( [ID] [int] NOT NULL, [Name] [varchar](50) NOT NULL) ON [PRIMARY]GO

The OFFSET-FETCH clause is used to fetch specif records. OFFSET-FETCH can be used only with the ORDER BY clause.

Stored Procedure :

SP

Limitations in Using OFFSET-FETCH

  • ORDER BY is mandatory to use OFFSET and FETCH clause.
  • OFFSET clause is mandatory with FETCH. You can never use, ORDER BY … FETCH.
  • TOP cannot be combined with OFFSET and FETCH in the same query expression.
  • The OFFSET/FETCH rowcount expression can be any arithmetic, constant, or parameter expression that will return an integer value. The rowcount expression does not support scalar sub-queries.
Categories: Sql Server Tags: , ,

MYSQL : How to skip first 10 records from a SELECT query and take next 10?

October 4, 2017 Leave a comment

We know there are skip and take operators available in linq to skip and take

The Take operator is used to return a given number of rows from a object list and the Skip operator skips over a specified number of rows in object list.

var employee = (from emp in employeeContext.EMPLOYEEs

where emp.SALARY < 1300

select emp).Skip(2).Take(5);

Is there a way in MySQL to achieve the same like have the first 10 result from a SELECT query skipped and select next 10?

Answer : Yes we can use LIMIT with two parameter

Introduction to MySQL LIMIT clause

The LIMIT clause is used in the SELECT statement to constrain the number of rows in a result set. The LIMIT clause accepts one or two arguments. The values of both arguments must be zero or positive integers.

The following illustrates the LIMIT clause syntax with two arguments:

Let’s examine the LIMIT clause parameters:

  • The offset specifies the offset of the first row to return. The offset of the first row is 0, not 1.
  • The count specifies the maximum number of rows to return.

When you use the LIMIT clause with one argument, this argument will be used to determine the maximum number of rows to return from the beginning of the result set.

SELECT *  FROM table LIMIT count;

Use LIMIT with two parameters. For example, to return results 11-60 (where result 1 is the first row), use:

SELECT * FROM foo LIMIT 10, 50
Hope this help !

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!

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!