|
Execute the following
SQL Server Transact-SQL script in Management Studio Query Editor to demonstrate the listing of the bottom page.
-- SQL bottom page - SQL common table expression - cte - SQL inner join
-- SQL subquery - SQL top function - SQL IN operator
USE AdventureWorks;
DECLARE @PageSize INT
SET @PageSize = 20;
WITH cteIndividualCustomer
AS (SELECT i.[CustomerID],
c.[FirstName],
COALESCE(c.[MiddleName],'') AS MiddleName,
c.[LastName],
c.[Phone],
c.[EmailAddress],
c.[EmailPromotion],
AT.[Name] AS [AddressType],
a.[AddressLine1],
a.[AddressLine2],
a.[City],
[StateProvinceName] = sp.[Name],
a.[PostalCode],
[CountryRegionName] = cr.[Name],
i.[Demographics]
FROM [Sales].[Individual] i
INNER JOIN [Person].[Contact] c
ON c.[ContactID] = i.[ContactID]
INNER JOIN [Sales].[CustomerAddress] ca
ON ca.[CustomerID] = i.[CustomerID]
INNER JOIN [Person].[Address] a
ON a.[AddressID] = ca.[AddressID]
INNER JOIN [Person].[StateProvince] sp
ON sp.[StateProvinceID] = a.[StateProvinceID]
INNER JOIN [Person].[CountryRegion] cr
ON cr.[CountryRegionCode] = sp.[CountryRegionCode]
INNER JOIN [Person].[AddressType] AT
ON ca.[AddressTypeID] = AT.[AddressTypeID]
WHERE i.[CustomerID] IN (SELECT [Sales].[Customer].[CustomerID]
FROM [Sales].[Customer]
WHERE UPPER([Sales].[Customer].[CustomerType]) = 'I'))
SELECT *
FROM (SELECT TOP ( @PageSize ) *
FROM cteIndividualCustomer
ORDER BY CustomerID DESC) a
ORDER BY CustomerID
/* Partial results
CustomerID FirstName MiddleName LastName
29464 Eugene Gao
29465 Roy Gill
29466 Lance Jimenez
29467 Monica J Mehta
29468 Jacqueline H Morris
29469 Dominique M Saunders
29470 Nathan Roberts
29471 Dana Ortega
29472 Lacey M Sharma
29473 Carmen J Subram
29474 Jaime B Raje
29475 Jared A Ward
29476 Elizabeth Bradley
29477 Neil N Ruiz
29478 Darren D Carlson
29479 Tommy L Tang
29480 Nina W Raji
29481 Ivan Suri
29482 Clayton Zhang
29483 Jésus L Navarro
*/ |
