0

I have a query that returns something like this:

Id | Value
1  | Hi,
1  | I'm
2  | just
2  | an
2  | example
3  | message.

What I want to do is number the rows based on the id. So with the example above, I want to return something like this:

Id | Value    | Number
1  | Hi,      | 1
1  | I'm      | 2
2  | just     | 1
2  | an       | 2
2  | example  | 3
3  | message. | 1

Is there a simple way to do this in the same query as the one you would use in the first example?

noClue
  • 958
  • 1
  • 13
  • 34
  • 3
    How do we determine the order of your data? `'just'`, `'an'` and `'example'` are numbered 1, 2 and 3 respectively, however, ordering them alphabetically would number then 3, 1 and 2 respectively. i can't see a way to get that order based on the data we have, – Thom A Dec 19 '18 at 11:34
  • 1
    in the query that produces the initial result, are there ANY other columns available that would help the ordering of results? – Paul Maxwell Dec 19 '18 at 11:40
  • There wasn't a specific order, at least not for this specific example, the idea was simply to number the rows based on the order you receive them in and the id they have. I do have a column to sort them in a specific way, but I left it out for the sake of brevity. – noClue Dec 19 '18 at 13:44
  • Possible duplicate of [SQL RANK() versus ROW\_NUMBER()](https://stackoverflow.com/questions/7747327/sql-rank-versus-row-number) – JeffUK Feb 13 '19 at 11:36

4 Answers4

1

The problem is there is NOTHING in that table that guarantees the order of rows, so the result you want cannot be guaranteed.

select *
   , row_number() over(partition by id order by (select 1)) as Number
from yourtable
Paul Maxwell
  • 33,002
  • 3
  • 32
  • 51
0

You can make use of the rownumber function.

SELECT ROW_NUMBER OVER (PARTITION BY ID ORDER BY (SELECT 1)) AS RowNumb
SQL_M
  • 2,455
  • 2
  • 16
  • 30
  • 2
    ***If*** this works, it works by luck. The order of data is never guaranteed without a reliable `ORDER BY`. – Thom A Dec 19 '18 at 11:35
  • It seems UsedByAlready has the same solution. Care to elaborate as to why you think any luck is involved here? – SQL_M Dec 19 '18 at 11:36
  • 1
    The the reason i stated: *"The order of data is never guaranteed without a **reliable** `ORDER BY`"*. `ORDER BY (SELECT 1)` effectively means, to the RDBMS, "Return the data in what ever order you choose." – Thom A Dec 19 '18 at 11:37
  • @Larnu the order is created with the partitions. That's what the op asked. In this situation, this always works and is fast. – SQL_M Dec 19 '18 at 11:37
  • 1
    The partition split is on ID. How does `ORDER BY (SELECT 1)` stop `'an`' being sorted **before** `'just'`, which is the value of the column `value`? – Thom A Dec 19 '18 at 11:38
  • I think that's completely irrelevant to the op. They just want new rows per partition. Otherwise, it's not clear from the question. – SQL_M Dec 19 '18 at 11:39
  • 1
    I doubt that that assumption is a safe one. The query above, however, has no guarantee of producing the results in the OP's expected data. – Thom A Dec 19 '18 at 11:41
0

You Can Use the ROW_NUMBER() Function and Partition by Id. Like this

DECLARE @MyTable AS TABLE
(
    Id INT,
    [Value] VARCHAR(50)
)

INSERT INTO @MyTable
(
    Id,
    [Value]
)
SELECT '1','Hi,' UNION
SELECT '1','I''m' UNION
SELECT '2','just' UNION
SELECT '2','an' UNION
SELECT '2','example' UNION
SELECT '3','message.'

SELECT
    *,
    Number = ROW_NUMBER() OVER(PARTITION BY Id ORDER BY Id)
    FROM @MyTable

Result

Id          Value                                              Number
----------- -------------------------------------------------- --------------------
1           Hi,                                                1
1           I'm                                                2
2           an                                                 1
2           example                                            2
2           just                                               3
3           message.                                           1
Jayasurya Satheesh
  • 7,826
  • 3
  • 22
  • 39
  • `PARTITION BY ID ORDER BY ID`? So order by the same value? So, what order do you put `2`, `2`, and `2` in? :) – Thom A Dec 19 '18 at 11:44
  • You Can Partition and Order by the Same value. That's not an Issue. The only thing that is mandatory is that, you should have an Order By statement in the Row_Number(). So You can even use Order by Value – Jayasurya Satheesh Dec 19 '18 at 11:45
  • Yes, you can use the some expression for the `PARTITION BY` and `ORDER BY` clauses, however, my point is that as every row is being ordered by the same value, what guarantees that the result set order is what you have in your Resultset? Data in tables are stored in HEAPs, specifically **unordered** HEAPs. – Thom A Dec 19 '18 at 11:53
  • the same problem exists for all responses, there is no **guarantee** the row numbering will match the expectation – Paul Maxwell Dec 19 '18 at 12:11
0

Please try following script.

DECLARE @MyTable AS TABLE
(
    Id INT,
    [Value] VARCHAR(50)
)

INSERT INTO @MyTable
(
    Id,
    [Value]
)
SELECT '1','Hi,' UNION
SELECT '1','I''m' UNION
SELECT '2','just' UNION
SELECT '2','an' UNION
SELECT '2','example' UNION
SELECT '3','message.'

select *, dense_rank()over(partition by Id order by  Value) as Number from @MyTable
/*
Id          Value                                              Number
----------- -------------------------------------------------- --------------------
1           Hi,                                                1
1           I'm                                                2
2           an                                                 1
2           example                                            2
2           just                                               3
3           message.                                           1
*/

Best Regards,

Rachel

Rachel_Wang
  • 57
  • 3