How to Create VIEW With TOP and ORDER BY Clause in SQL
In this article I am going to explain how to create VIEW statement with TOP and ORDER BY clause in SQL.
Introduction
View in SQL is a virtual table and this table is created on the result set of SQL statement. View is similar to a normal table and also have rows and columns. View may be combination of some rows and columns of two or more than two tables. You can use SQL function and WHERE clause with View in SQL statement.
Here I am going to explain how to create VIEW statement with TOP and ORDER BY clause in SQL.
A Statement that create mcninvoices table
create table mcninvoices
(
invoiceid int not null identity primary key,
vendorid int not null,
invoiceno varchar(15),
invoicetotal money,
paymenttotal money,
creadittotal money
)
|
Statements that insert data in mcninvoices table
insert into mcninvoices values (20,'e001',100,100,0.00)
insert into mcninvoices values (21,'e002',200,200,0.00)
insert into mcninvoices values (22,'e003',500,0.00,100)
insert into mcninvoices values (23,'e004',1000,100,100)
insert into mcninvoices values (24,'e005',1200,200,500)
|
A Statement that is used to fetch data from mcninvoices table
A Statement that create a view using top clause in SQL
create view topinview
as
select top 5 invoiceid,vendorid,invoicetotal
from mcninvoices
order by invoicetotal desc
|
A Statement that show data in view