suds - 1 year ago 53

SQL Question

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()`

`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`

`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 Source

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.