Q
Problem solve Get help with specific problems with your technologies, process and projects.

Row number in a SELECT statement

How can you get the record number in a select statement? For example, I want to do something like this --

`select recordnumber, * from table`

so that the result returns a numbered list.

Short answer: apply the row number to the rows in the result set in your application program. This provides the flexibility to change the sequence of the query, or the fields it's selecting on, with no further intervention.

Longer answer: you can do it in SQL, but it isn't terribly efficient. It's called a ranking query.

Here's a sample table:

```PERSONS
fname lname  age  IQ
mary  smith  24   109
jane  doe    17   123
bill  adams  32   111
fred  brown  28   132
tom   white  31   125```

To start, let's just list these people by increasing age (youngest first) --

``` select fname, lname, age, IQ
from persons
order by age

fname lname  age  IQ
jane  doe    17   123
mary  smith  24   109
fred  brown  28   132
tom   white  31   125
bill  adams  32   111```

Now here's the ranking query by increasing age --

```select (select count(*) from persons)
- count(*) + 1 as rank
, t1.fname, t1.lname, t1.age, t1.IQ
from persons as t1
, persons as t2
where t1.age <= t2.age
group by t1.fname, t1.lname, t1.age, t1.IQ
order by 1

rank fname lname  age  IQ
1   jane  doe    17   123
2   mary  smith  24   109
3   fred  brown  28   132
4   tom   white  31   125
5   bill  adams  32   111```

How does the ranking query work? For every row, it counts all the rows that have an equal or greater age, and subtracts the result from the number of rows in the table plus 1, which for our example table is 5 + 1 = 6. In the case of Mary Smith, there are 4 rows that have an equal or greater age (including Mary's row itself), so the rank is 2.

Let's try another example. Let's rank them by decreasing IQ (smartest first) --

```select (select count(*) from persons)
- count(*) + 1 as rank
, t1.fname, t1.lname, t1.age, t1.IQ
from persons as t1
, persons as t2
where t1.IQ >= t2.IQ
group by t1.fname, t1.lname, t1.age, t1.IQ
order by 1

rank fname lname  age  IQ
1   fred  brown  28   132
2   tom   white  31   125
3   jane  doe    17   123
4   bill  adams  32   111
5   mary  smith  24   109```

Notice that this time, the join condition is reversed, because we're after the rank of a descending number. However, the ORDER BY is still on rank, increasing.

This type of join is called a theta join (joining a variable number of rows based on an inequality). As you can imagine, the database has to have a good optimizer for it to perform efficiently for large tables. Hence the short answer: do it in your application program if you can. It's simpler, too, because you don't have to keep re-thinking which way the inequality is supposed to go.

For More Information

• Dozens more answers to tough SQL questions from Rudy Limeback are available here.
• The Best SQL Web Links: tips, tutorials, scripts, and more.
• Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
• Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.

SearchDataManagement

• Informatica takes Customer 360 master data management to cloud

Updated MDM service benefits from integrations with the broader cloud-native Informatica platform that is built on top of a ...

• Graph database vs. relational database: Key differences

Relational databases and graph databases both focus on the relationships between data but not in the same ways. Here are some key...

• ScyllaDB NoSQL database to improve with Project Circe

ScyllaDB Project Circe sets out to help improve consistency, elasticity and performance for the open source NoSQL database.

SearchBusinessAnalytics

• Key differences in uses of DataOps vs. DevOps

As with DevOps, DataOps hinges on cooperation between teams and breaking down silos within an organization with the focus of ...

• Data storytelling a key part of Yellowfin analytics platform

Data storytelling remains a focal point for Yellowfin. Stories, the vendor's narrative generation tool, features heavily in both ...

• Assent Compliance automates text analytics with AWS

With more than 20,000 documents to review each month, Assent Compliance, a supply chain data management vendor, turned to AWS to ...

SearchSAP

• SAP Commerce Cloud dresses up digital storefronts

SAP Commerce Cloud is designed to help companies launch digital commerce sites, which may be useful for large enterprises and ...

• SAP Business Network, S/4HANA critical for SAP in 2021

SAP's 2021 will be a mix of familiar challenges such as moving customers off legacy systems to S/4HANA and new opportunities such...

• SAP in 2020: COVID-19, Klein takes command and Qualtrics IPO

From leadership changes to the massive disruptions caused by the COVID-19 pandemic, it was a year like no other for SAP. Here are...

SearchSQLServer

• SQL Server database design best practices and tips for DBAs

Good database design is a must to meet processing needs in SQL Server systems. In a webinar, consultant Koen Verbeeck offered ...

• SQL Server in Azure database choices and what they offer users

SQL Server databases can be moved to the Azure cloud in several different ways. Here's what you'll get from each of the options ...

• Using a LEFT OUTER JOIN vs. RIGHT OUTER JOIN in SQL

In this book excerpt, you'll learn LEFT OUTER JOIN vs. RIGHT OUTER JOIN techniques and find various examples for creating SQL ...

TheServerSide.com

• What's in a name? What developers can expect in Jakarta EE 9

What Oracle called the Java EE API is now called Jakarta EE API under the Eclipse Foundation. Here's what developers can expect ...

• Test your knowledge of variable naming conventions

What's the difference between snake case and camel case? Or kebab case and pascal case? Try this 10-question variable naming quiz...

• Why GitHub renamed its master branch to main

The GitHub master branch is no more. Developers used to think it was untouchable, but that's not the case. Here's why GitHub made...

SearchDataCenter

• Get a template to estimate server power consumption per rack

Admins can struggle with power consumption estimation as infrastructure gets more complex. The kW-per-rack metric can help ...

• When the chips are down, Intel turns to VMware's Pat Gelsinger

Newly appointed Intel CEO Pat Gelsinger brings technical chops to the top of the giant chipmaker, but it may take a while to ...

• Intel CEO Bob Swan to be replaced by VMware's Pat Gelsinger

Facing delays of critical products along with superior offerings by AMD and Nvidia the past couple of years, Intel CEO is ...

SearchContentManagement

• 5 steps to a successful ECM implementation

Implementing an ECM system is a major undertaking. Check out these steps to get on the right track before you jump into the ...

• How to develop an ECM strategy and roadmap

Without an ECM roadmap, an organization's strategy can get muddled and disorganized. Understand the steps and strategies to ...

• How to achieve Teams integration with SharePoint sites

Depending on the complexity of a business's SharePoint sites, a Teams integration can benefit organizations by being ...

SearchHRSoftware

• Overview of SAP SuccessFactors HXM Suite

SAP SuccessFactors HXM is the next iteration of SuccessFactors HCM and is meant to help HR departments manage the entire employee...

• Workday adds vaccine management for 45M to its platform

COVID-19 vaccine management is getting the attention of HR vendors. Workday announced its vaccine tool, which integrates with the...

• Democrats push for AI bias testing

Lawmakers in states and Congress are showing increasing interest in guarding against AI bias. Legislation in California and New ...

Close