DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to find the top & bottom 5 rows in a table?

The following Microsoft SQL Server 2008 T-SQL database script in SSMS Query Editor demonstrates the use of the TOP function to get both TOP and BOTTOM of a table in a single query.

-- T-SQL find top & bottom 5 rows in a table applying a single query

-- Find head and tail of table

USE AdventureWorks2008;

GO

 

SELECT   OrderDate = convert(DATE,OrderDate,111),

         sod.*

FROM     Sales.SalesOrderDetail sod

         INNER JOIN Sales.SalesOrderHeader soh

           ON soh.SalesOrderID = sod.SalesOrderID

WHERE    SalesOrderDetailID IN (SELECT   TOP 5 SalesOrderDetailID

                                FROM     Sales.SalesOrderDetail

                                ORDER BY SalesOrderDetailID)

          OR -- Descending sort will give the BOTTOM even though TOP is used

             SalesOrderDetailID IN (SELECT   TOP 5 SalesOrderDetailID

                                    FROM     Sales.SalesOrderDetail

                                    ORDER BY SalesOrderDetailID DESC)

ORDER BY SalesOrderID

GO

/*

OrderDate   SalesOrderID      SalesOrderDetailID

2001-07-01  43659             1

2001-07-01  43659             2

2001-07-01  43659             3

2001-07-01  43659             4

2001-07-01  43659             5

2004-07-31  75122             121313

2004-07-31  75122             121314

2004-07-31  75123             121315

2004-07-31  75123             121316

2004-07-31  75123             121317

*/

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE