Example Code:
Note: Code explained below
WITH temp_table AS
(
SELECT TOP (SELECT COUNT(*) FROM [table_1])
firstname, lastname, mobile, email,
ROW_NUMBER() OVER (ORDER BY zip_code) AS row_no
FROM
[table_1]
ORDER BY zip_code
)
SELECT
first_name,
last_name,
email
FROM
temp_table
WHERE
row_no BETWEEN 1 AND 10
(
SELECT TOP (SELECT COUNT(*) FROM [table_1])
firstname, lastname, mobile, email,
ROW_NUMBER() OVER (ORDER BY zip_code) AS row_no
FROM
[table_1]
ORDER BY zip_code
)
SELECT
first_name,
last_name,
FROM
temp_table
WHERE
row_no BETWEEN 1 AND 10
Code Explanation:
WITH temp_table AS
--create a temporary table named temp_table
(
SELECT TOP (SELECT COUNT(*) FROM [table_1])
(
SELECT TOP (SELECT COUNT(*) FROM [table_1])
--table_1 is the original table
--top is an essential keyword when you use orderby keyword in this sub query else you can ignore TOP keyword
-- I have fetched the rows count to fetch the entire rows, this means no difference when considered as a independent query but just to obey the inner query Syntax, that is TOP keyword should be used while using order by in the sub query
firstname, lastname, mobile, email,
firstname, lastname, mobile, email,
--just fetching some fields
ROW_NUMBER() OVER (ORDER BY zip_code) AS row_no
ROW_NUMBER() OVER (ORDER BY zip_code) AS row_no
--generate row number with a custom field in the temp_table for the rows fetched from the original table
FROM
[table_1]
FROM
[table_1]
--original table
ORDER BY zip_code
ORDER BY zip_code
--order by inside sub query is the complex part. I remind, use TOP keyword when using order by keyword.
)
SELECT
first_name,
last_name,
email
)
SELECT
first_name,
last_name,
--simple select query from the temp_table
FROM
temp_table
WHERE
row_no BETWEEN 1 AND 10
FROM
temp_table
WHERE
row_no BETWEEN 1 AND 10
--now we can set between values to fetch records. Hope this is self explanatory, you can set 11 and 20 to fetch the next set of 10 records and so on.
Please share your comments and suggestions. You may also post your questions and doubts here.
No comments:
Post a Comment
Share your comments