For each Category or Group Select FIRST N rows, TOP N rows, LAST N rows, BOTTOM N rows, Random data

Please visit my new Web Site https://coderstechzone.com

First of all i want to tell you one truth that SQL SERVER never guranteed physical sequence of data. You can retrieve/obtain data logically. Another one is its a bit difficult for SQL SERVER 2000 but easy for 2005. Initially i tried to start with simple example so that beginner can track the post easily:

Focus Area:
1. TOP N
2. Bottom N
3. Random Records
4. Group or Category wise

To follow the below examples it will be better to create two tables & put relevant datas into those tables. Then you can directly run the below example queries by copying from this page. Table structure & few data for rest of the examples is as follows:



SELECT TOP N:

SELECT TOP 3 * FROM Product ORDER BY Name

Specify your required no of record after TOP you will get specified no of record from Product table ordered by Name. The ORDER BY Clause is used to SORT data. Bydefault its returned Ascending data.

Let a bit complex that you have a requirement that find first 3 active products. So the query will be:

SELECT TOP 3 * FROM Product WHERE [Continue]=1

SELECT LAST or BOTTOM N:
There is no built in function to get LAST or BOTTOM n no of data. But we can achieve by TOP & ORDER BY Clause combination. Let now you have a requirement like Find Last 3 products from product table. So the query will be:

SELECT TOP 3 * FROM Product ORDER BY Name DESC

Now a bit complex let you have a requirement that find Last 3 products from product table those name prefix is 'L'. So the query will be:

SELECT TOP 3 * FROM Product WHERE SUBSTRING(Name,1,1)='L' ORDER BY Name DESC

Getting Random Records:

SELECT TOP 2 * FROM Product ORDER BY NEWID()

Now run the above sql to get random records. Run twice you will get diffierent value.

Getting Group or Category wise N Rows:
Let you have a requirement like: Find all products highest price. SO that in this case Group concept will be applied for Product and Order By Clause will be applied for price to get each product highest price. Look at the below SQL:

SELECT P.Name,V.Price FROM Product P
LEFT JOIN Variant V ON P.ID=V.ProductID
WHERE V.ID IN
(SELECT TOP 1 ID FROM Variant WHERE ProductID=V.ProductID ORDER BY Price DESC)

The output will be:


Change TOP 1 to N from the above SQL subquery to get N no of highest Price for each product group. The speciality of the above SQL is you can run this query in both SQL SERVER 2000 & SQL SERVER 2005.

SQL SERVER 2005 provided us a method ROW_NUMBER() which we can use to filter group wise value from database:

SELECT P.Name,V.Price FROM Product P
INNER JOIN (
SELECT ROW_NUMBER() OVER(PARTITION BY V.ProductID ORDER BY V.ProductId DESC) AS RowNumber,
ProductID,Price
FROM Variant V)
V ON P.ID=V.ProductID
WHERE V.RowNumber<2

So keep experimenting.