Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
What did you try yourself? –  Colin 't Hart Apr 25 at 10:51
    
answered (partially), but i missed that sequence. which database engine you use ? –  user503207 Apr 25 at 11:00
    
Which DBMS do you use? –  ypercube Apr 25 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 –  cgoddard Apr 25 at 12:22
    
@Colin'tHart Nothing, I have no idea where to start on such a thing –  cgoddard Apr 25 at 12:22

2 Answers 2

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

SELECT *,
    ROW_NUMBER() OVER (
        PARTITION BY GrpID
        ORDER BY PKID
    ) AS Idx
FROM YourTable;
share|improve this answer

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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