1

So, I have a table that contains numerous entries of text, but each one has a parent context that identifies the entries within a group, such as:

+------+-------+------+
| PKID | GrpID | Body |
+------+-------+------+
| 1    | 1     | a... |
+------+-------+------+
| 2    | 2     | b... |
+------+-------+------+
| 3    | 2     | c... |
+------+-------+------+
| 4    | 1     | d... |
+------+-------+------+
| 5    | 3     | e... |
+------+-------+------+
| 6    | 3     | f... |
+------+-------+------+
| 7    | 2     | g... |
+------+-------+------+
| 8    | 1     | h... |
+------+-------+------+
| 9    | 1     | i... |
+------+-------+------+

Now, I am trying to get a numeric value (calculated based on the rows position within the result, so it could be different each time based on what predicates are used) for the position of each row. So (I have ordered it by GrpID to make it easier to read, but imagine it was ordered naturally):

+------+-----+-------+------+
| PKID | Idx | GrpID | Body |
+------+-----+-------+------+
| 1    | 1   | 1     | a... |
+------+-----+-------+------+
| 4    | 2   | 1     | d... |
+------+-----+-------+------+
| 8    | 3   | 1     | h... |
+------+-----+-------+------+
| 9    | 4   | 1     | i... |
+------+-----+-------+------+
| 2    | 1   | 2     | b... |
+------+-----+-------+------+
| 3    | 2   | 2     | c... |
+------+-----+-------+------+
| 7    | 3   | 2     | g... |
+------+-----+-------+------+
| 5    | 1   | 3     | e... |
+------+-----+-------+------+
| 6    | 2   | 3     | f... |
+------+-----+-------+------+

What query could be performed on such a dataset that would give me such a result?

6
  • What did you try yourself? Apr 25, 2014 at 10:51
  • answered (partially), but i missed that sequence. which database engine you use ? Apr 25, 2014 at 11:00
  • Which DBMS do you use? Apr 25, 2014 at 11:21
  • DBMS agnostic for the moment, I would prefer to stay as close to MySQL as possible, but am willing to use another
    – topherg
    Apr 25, 2014 at 12:22
  • @Colin'tHart Nothing, I have no idea where to start on such a thing
    – topherg
    Apr 25, 2014 at 12:22

2 Answers 2

3

In SQL Server and other systems that support ROW_NUMBER...

SELECT *,
    ROW_NUMBER() OVER (
        PARTITION BY GrpID
        ORDER BY PKID
    ) AS Idx
FROM YourTable;
0

FOR MS SQL 2012 server

select id, grp , grpn = ROW_NUMBER()
                                      OVER (  PARTITION BY [grp]
                                                    ORDER BY [grp]
                                                   )  from list order by grp,id

SEE http://sqlfiddle.com/#!6/8d6b9/1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.