The SELECT Query

The SELECT clause selects or returns the number of records from the table. But some times we want to return only some of the records rather than all the records in the table.
To do it , we have the clause SELECT TOP using which we can specify the number of records to return.
The SELECT TOP syntax varies with the Databases. i.e the syntax is different for MySQL, SQL Server and Oracle.

Syntax (For SQL Server)

SELECT TOP number column_name FROM
table_name

Example(For SQL Server)

ID	Name	State	JoinDate	Experience
1	Anoop Sharma	Delhi	2017-06-12 12:04:30.233	3
2	Ankit Pandey	Bihar	2017-06-15 01:03:34.247	4
3	Gaurav Kapoor	Pune	2017-08-21 07:30:00.543	2
4	Arjit Singh	Punjab	2017-07-11 10:03:30.577	7

Suppose we want to return the first two records from the above table.

SELECT TOP 2 * FROM
tbl_Record

Output

ID	Name	State	JoinDate	Experience
1	Anoop Sharma	Delhi	2017-06-12 12:04:30.233	3
2	Ankit Pandey	Bihar	2017-06-15 01:03:34.247	4

If you want the selected number of columns , then you can specify the name of the columns you want to return.

SELECT TOP 2 ID,Name FROM
tbl_Record

Output

ID	Name
1	Anoop Sharma
2	Ankit Pandey

Syntax (For MYSQL Database)

SELECT column_name FROM
table_name LIMIT number

Example(For MYSQL Database)

ID	Name	State	JoinDate	Experience
1	Anoop Sharma	Delhi	2017-06-12 12:04:30.233	3
2	Ankit Pandey	Bihar	2017-06-15 01:03:34.247	4
3	Gaurav Kapoor	Pune	2017-08-21 07:30:00.543	2
4	Arjit Singh	Punjab	2017-07-11 10:03:30.577	7

Suppose we want to return the first two records from the above table.

SELECT * FROM
tbl_Record LIMIT 2

Output

ID	Name	State	JoinDate	Experience
1	Anoop Sharma	Delhi	2017-06-12 12:04:30.233	3
2	Ankit Pandey	Bihar	2017-06-15 01:03:34.247	4

If you want the selected number of columns , then you can specify the name of the columns you want to return.

SELECT ID,Name FROM
tbl_Record LIMIT 2

Output

ID	Name
1	Anoop Sharma
2	Ankit Pandey

Syntax (For Oracle Database)

SELECT column_name FROM
table_name WHERE ROWNUM<=number

Example(For Oracle Database)

ID	Name	State	JoinDate	Experience
1	Anoop Sharma	Delhi	2017-06-12 12:04:30.233	3
2	Ankit Pandey	Bihar	2017-06-15 01:03:34.247	4
3	Gaurav Kapoor	Pune	2017-08-21 07:30:00.543	2
4	Arjit Singh	Punjab	2017-07-11 10:03:30.577	7

Suppose we want to return the first two records from the above table.

SELECT * FROM
tbl_Record WHERE ROWNUM<=2

Output

ID	Name	State	JoinDate	Experience
1	Anoop Sharma	Delhi	2017-06-12 12:04:30.233	3
2	Ankit Pandey	Bihar	2017-06-15 01:03:34.247	4

If you want the selected number of columns , then you can specify the name of the columns you want to return.

SELECT ID,Name FROM
tbl_Record WHERE ROWNUM<=2

Output

ID	Name
1	Anoop Sharma
2	Ankit Pandey

Using WHERE

You can also use WHERE condition with the SELECT TOP clause to filter the records based on specific condition.

Syntax(For SQL Server)

SELECT TOP number column_name FROM
table_name WHERE condition

Example (For SQL Server)

ID	Name	State	JoinDate	Experience
1	Anoop Sharma	Delhi	2017-06-12 12:04:30.233	3
2	Ankit Pandey	Bihar	2017-06-15 01:03:34.247	4
3	Gaurav Kapoor	Pune	2017-08-21 07:30:00.543	3
4	Arjit Singh	Punjab	2017-07-11 10:03:30.577	7

SELECT TOP 2 * FROM tbl_record
WHERE Experience=3

Output

ID	Name	State	JoinDate	Experience
1	Anoop Sharma	Delhi	2017-06-12 12:04:30.233	3
3	Gaurav Kapoor	Pune	2017-08-21 07:30:00.543	3

Using Order BY Clause

We can also use Order By clause with the SELECT TOP clause to specify the order by which the records to be returned.

Syntax(For SQL Server)

SELECT TOP number column_name FROM
table_name ORDER BY asc/desc

Example (For SQL Server)

ID	Name	State	JoinDate	Experience
1	Anoop Sharma	Delhi	2017-06-12 12:04:30.233	3
2	Ankit Pandey	Bihar	2017-06-15 01:03:34.247	4
3	Gaurav Kapoor	Pune	2017-08-21 07:30:00.543	3
4	Arjit Singh	Punjab	2017-07-11 10:03:30.577	7