Extract records by their record number

Extract records by their record number

SQL Server, and the SQL language in general, doesn’t support record numbers, so you can’t extract a set of records if you know their position in the resultset. This missing capability would be extremely convenient when displaying pages of data in an ASP program. For example, if each page contains 10 records, then displaying the 8-th page would mean displaying records from position 71 to position 80, where position depends also on the current sort order.

Even if the SQL language doesn’t support this feature, you can simulate it – to an extent and for tables not too large, by using two nested SELECTs with the TOP clause, as in the following example based on the Titles table in the Pubs database:

— get records 10-12 from table Titles, sorted on priceSELECT TOP 3 * FROM Table WHERE title_id IN   (SELECT TOP 12 title_id FROM Titles ORDER BY price)   ORDER BY price DESC

The above query returns records from 10 to 12 in the Titles table, when the table is sorted by price. The problem with this query is that the resultset is returned in reversed order, so you have to make it straight when processing the data. For example, if you’re creating an HTML table, you must start by building the last row of cells and progress towards upper rows. If working with a reversed resultset is a problem – for example, when binding the result to a grid – you must resort to three nested SELECTs:

SELECT * FROM Titles WHERE title_id IN    (SELECT TOP 3 title_id FROM titles WHERE title_id IN       (SELECT TOP 10 title_ID FROM titles ORDER BY price)     ORDER BY price DESC)    ORDER By price

This technique is usually faster than reading the first N records until you get to the records you want to display, yet you can’t use it for very large tables.

Share the Post:
XDR solutions

The Benefits of Using XDR Solutions

Cybercriminals constantly adapt their strategies, developing newer, more powerful, and intelligent ways to attack your network. Since security professionals must innovate as well, more conventional endpoint detection solutions have evolved

AI is revolutionizing fraud detection

How AI is Revolutionizing Fraud Detection

Artificial intelligence – commonly known as AI – means a form of technology with multiple uses. As a result, it has become extremely valuable to a number of businesses across

AI innovation

Companies Leading AI Innovation in 2023

Artificial intelligence (AI) has been transforming industries and revolutionizing business operations. AI’s potential to enhance efficiency and productivity has become crucial to many businesses. As we move into 2023, several

data fivetran pricing

Fivetran Pricing Explained

One of the biggest trends of the 21st century is the massive surge in analytics. Analytics is the process of utilizing data to drive future decision-making. With so much of

kubernetes logging

Kubernetes Logging: What You Need to Know

Kubernetes from Google is one of the most popular open-source and free container management solutions made to make managing and deploying applications easier. It has a solid architecture that makes

ransomware cyber attack

Why Is Ransomware Such a Major Threat?

One of the most significant cyber threats faced by modern organizations is a ransomware attack. Ransomware attacks have grown in both sophistication and frequency over the past few years, forcing

data dictionary

Tools You Need to Make a Data Dictionary

Data dictionaries are crucial for organizations of all sizes that deal with large amounts of data. they are centralized repositories of all the data in organizations, including metadata such as