SQL Server SELECT, SELECT TOP , SELECT DISTINCT Queries

Posted by

SELECT SQL Query is used to retrieved the result data from the SQL Server Database Table stored in the form of rows and columns.

Lets see the different usage of SELECT SQL Query for data retrieval. Consider the below table – oracleappshelpUsers.

CREATE TABLE oracleappshelpUsers
(
    UserId				 INT 				NOT NULL IDENTITY PRIMARY KEY,
    GivenName 			VARCHAR(128) 		NOT NULL,
    MiddelName 			VARCHAR(10)					,
    FamilyName 			VARCHAR(128) 		NOT NULL,
	Age 				INT 				CHECK (Age>=18),
	Status 				VARCHAR(20)			NOT NULL	DEFAULT 'NEW'	
)

Below given is the result set data for the table.

SELECT * 
FROM 
oracleappshelpUsers
UserIdGiveNameMiddleNameFamilyNameAgeStatus
1Mohit Sharma28New
2RohitKumar Jain34Closed
3Snehashish Gupta31Pending
4Rajeev Malhotra34Closed
SELECT UserId, GivenName, FamilyName
FROM 
oracleappshelpUsers
UserIdGivenNameFamilyName
1Mohit Sharma
2Rohit Jain
3Snehashish Gupta
4Rajeev Malhotra

SQL Server Select DISTINCT result data

The use of DISTINCT keyword is applicable when DUPLICATE data rows to be removed. Consider the first example where we have 4 rows and Status -‘Closed’ is repeated twice.

If we need to get the result set from the data table to identify the UNIQUE count of values, then DISTINCT is useful.

SELECT DISTINCT Status 
FROM 
oracleappshelpUsers
New
Closed
Pending

SELECT COUNT(DISTINCT Status) FROM oracleappshelpUsers;
         OR 
SELECT Count(*) AS DistinctStatus
FROM (SELECT DISTINCT Status FROM oracleappshelpUsers);
Ouput - 3

SQL Server TOP SQL Query

The data retrieval could be required in different forms . We might have a requirement to get the data in the specific limit range. SQL Serer TOP statement could be useful in such cases and limits the number of rows to be returned as the result set.

But this does not depict the order of rows stored in the database thus ORDER BY clause is used with the TOP SQL Statement

SELECT TOP 10
From 
oracleappshelpUsers
Order By UserId Desc;