suds suds - 1 month ago 8
SQL Question

Get a row number on select statement while matching entire row

I am trying to get a row number of the row. Since the table doesn't have any id column, I have used

ROW_NUMBER()
without any order which is shown below.

SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS SNO, *
FROM [table1]


Now the challenge is i need to find a row with a condition which is just a select statement with where clause but with a original row number.

SELECT TOP 1 *
FROM table1
WHERE [Total Sales] = 2555


This statement returns a single record. I have tried to use
INTERSECT
to combine both statements to get result with row number.

SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT 100)) AS SNO, *
FROM [table1]

INTERSECT

SELECT TOP 1 *
FROM table1
WHERE [Total Sales] = 2555


Of course, this throws errors since number of columns are different. So what is the correct way to get the actual row number ?

Answer

When you run this query:

SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS SNO, t.*
FROM [table1] t;

The SNO values are unstable. That means that the same query run multiple times might return different numbers. Sorting in SQL is not stable. That means that identical keys can be in an arbitrary order when the query is run multiple times. Why? SQL tables and result sets represent unordered sets. There is nothing to base a stable sort on.

The simplistic answer to your question is to use a subquery:

SELECT t.*
FROM (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS SNO, t.*
      FROM [table1] t
     ) t
WHERE [Total Sales] = 2555;

However, the real answer is that you should be using multiple columns to create a stable sort, if you want to use this value for more than one query.