Thursday, August 23, 2012

SQL - TOP, ROWNUM

SQL TOP clause is used to fetch a TOP N number or X percent records from a table
.
Syntax-

The basic syntax of TOP clause with SELECT statement would be as follows:
SELECT TOP number|percent column_name(s)
FROM table_name
WHERE [condition] 
  
           SQL server   Example:1
Consider CUSTOMERS table is having following records:
+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+
SELECT TOP 3 * FROM CUSTOMERS;

+----+---------+-----+-----------+---------+
| ID | NAME    | AGE | ADDRESS   | SALARY  |
+----+---------+-----+-----------+---------+
|  1 | Ramesh  |  32 | Ahmedabad | 2000.00 |
|  2 | Khilan  |  25 | Delhi     | 1500.00 |
|  3 | kaushik |  23 | Kota      | 2000.00 |
+----+---------+-----+-----------+---------+
 
 Oracle server example:2
SELECT * FROM CUSTOMERS
WHERE ROWNUM <= 3;

+----+---------+-----+-----------+---------+
| ID | NAME    | AGE | ADDRESS   | SALARY  |
+----+---------+-----+-----------+---------+
|  1 | Ramesh  |  32 | Ahmedabad | 2000.00 |
|  2 | Khilan  |  25 | Delhi     | 1500.00 |
|  3 | kaushik |  23 | Kota      | 2000.00 |
+----+---------+-----+-----------+---------+

0 comments:

Post a Comment

SQL BASIC

  • Sql-Overview
  • Sql-Sysntax
  • Sql-Normalization
  • RDBMS Concept
  • Sql-Data Type
  • Sql-Operator
  • Sql-Expression
  • Create database
  • Sql-Delete
  • Sql-Select
  • Sql-Create
  • Sql-Like
  • Sql-Join
  • Sql-Insert
  • Sql-Drop Table
  • Sql-Wild Card
  • Sql-Order By
  • Sql-Group By
  • Sql-Index
  • Not Null Constraints
  • Transaction Control
  • Sql-Transaction
  • Sql-In
  • Sql-Distinct
  • Check Constraint
  • Sql Alias
  • Sql-Primary
  • Sql-Where
  • Sql-Update
  • Sql-Alias
  • Sql-Top-Rownum
  • Primary key vs Unique key
  • SQL Interview Question
  • PL/SQL BASIC

  • Variable
  • Block Structure
  • Function
  • Procedure
  • Nested Blog
  • If Statement
  • While Loop
  • For Loop
  • SEO

  • Introduction Seo
  • Top Social Bookmarking List
  • Directory Submission List
  • Classified Ads
  • Key Word Research
  • Html

  • Introduction Html
  • Introduction Css
  • Introduction Java Script
  • Unix

  • Unix
  • Software Testing

  • Software Testing
  • Computer Network

  • Computer Network
  •