Limiting Rows in SQL

         In this article I am going to explain how limit the rows in the result in a SELECT query.

Top Clause

        TOP clause is used to limit the result set with N number of rows or X percentage of rows.

We can use the TOP clause in four ways, below I have given the syntax for each of them.

Syntax

    1. SELECT TOP (N) <column_list> FROM table_name

       This will return top N number of rows from the table

    2. SELECT TOP (X) PERCENT <column_list> FROM table_name

       This will return top X percentage of rows from the table

    3. SELECT TOP (N) WITH TIES  <column_list> FROM table_name

        We will see more detail about the 'WITH TIES' later in this post.

    4. SELECT TOP (X) PERCENT WITH TIES <column_list> 

          FROM talbe_name ORDER BY  <column_list>

Explanation

         TOP (N) will return the top n number of rows. It will take records from the top of the table.

Let's create and populate a table to see how TOP clause works.

CREATE TABLE People(

    NAME VARCHAR(50)

    ,Age TINYINT

    ,City VARCHAR(50)

)

GO

 

INSERT INTO People

VALUES('Rajesh',23,'Delhi')

,('Kumar',21,'Mumbai')

,('Joe',22,'Chennai')

,('Kathir',22,'Mumbai')

,('Ram',24,'Chennai')

,('Angel',22,'Chennai')

,('Jessy',20,'Chennai')

,('Litsa',22,'Delhi')

      WITH TIES will return all the rows which contains the last value returned by the ORDER BY clause

      

      The above query with 'WITH TIES' returned 6 rows instead of 3 rows.Because when you use 'WITH TIES' it will select the top 3 rows (in this case), then it will look in to the last value of the column specified in the ORDER BY clause. 

       In this example I have used 'Age' in the ORDER BY clause. So the 3rd row has the age value 22, then the SQL Server return all the other rows which have Age 22 even the number of rows exceed than we specified in the TOP clause

 Note: ORDER BY clause is mandatory to use WITH TIES

  

Converting Row Value as Column Name

Converting Row Value as Column Name

      Hi, This is my first article in this blog. I would like to share the problem I faced in my work and the things I find differently, interestingly in my technical life... :)

Problem

       In some real time scenario, we may have the column name and its value in the same row, But when we want to display the data, we would like to display a row value as column and display the value for that column.

Ex: Here we have some student records.

Here we have a column 'ColumnName' which contain the values to be converted as the column.
Column 'Value' contains the respective value for the column.

Expected output is : 

Query

   Below is the query I used to get the above output.
       

 SELECT ID,MIN(CASE ColumnName WHEN 'Name' THEN ISNULL(Value,'') END) AS Name

  ,MIN(CASE ColumnName WHEN 'Age' THEN ISNULL(Value,'') END) AS Age

 FROM StudentRecords

 GROUP BY ID

Explanation
    
     We are checking all the possible ColumnName values in the case statement and display the value, we are using the ColumnName in the alias name.

Full Script used in this article is below...

CREATE TABLE StudentRecords(

    ID INT

    ,ColumnName VARCHAR(50)

    ,Value VARCHAR(50)

)

GO

INSERT INTO StudentRecords

VALUES(1,'Name','Joe')

INSERT INTO StudentRecords

VALUES(1,'Age','21')

INSERT INTO StudentRecords

VALUES(2,'Name','Ram')

INSERT INTO StudentRecords

VALUES(2,'Age','25')

INSERT INTO StudentRecords

VALUES(3,'Name','Satheesh')

INSERT INTO StudentRecords

VALUES(3,'Age','22')

INSERT INTO StudentRecords

VALUES(4,'Name','Kathir')

INSERT INTO StudentRecords

VALUES(4,'Age','12')

GO

SELECT ID,MIN(CASE ColumnName WHEN 'Name' THEN ISNULL(Value,'') END) AS Name

  ,MIN(CASE ColumnName WHEN 'Age' THEN ISNULL(Value,'') END) AS Age

 FROM StudentRecords

 GROUP BY ID