Pretty tricky SQL select

[Daniel Techen]

Hi folks,

today I spent a full hour trying to write a "simple" SQL SELECT:sick: . There is a table that has a column "position". The table looks like this:

SELECT RowNum, Position FROM myTable

RowNum Position
--------------------------
1 2a
2 2a
3 2a
4 2b
5 3a
6 3a
...


What I need is a SQL Select that does not retrieve a consecutive rownum, but a "rownum per position". The result table should look like this:

Position "RowNum"
--------------------------
2a 1
2a 2
2a 3
2b 1
3a 1
3a 2

I hope you got my point ;-). Can anybody give me a hint how to achieve this?

TIA,
Daniel

BTW. The DBMS is an Oracle.

[chrisp]

GROUP BY Position

-or-

ORDER BY Position

should do the trick ... I think that's what you are looking for.

Chris

[hspc]

From the resultset that Daniel sent, It does not require grouping
This query should work:
SELECT Position,RowNum FROM myTable
order by Position

[JeffB]

Just to add a little thing, I would add a second order by: ;)

SELECT Position,RowNum FROM myTable
order by Position,RowNum

JeffB

[cjard]

no, i think he actually wants a running total...

see his positions go up to 6, but he wants a incrementing counter for each distinct position

i can only offer this for calculating new records:


insert into table1
select count(*)+1, '2a' from table1 where pos = '2a'


count(*)+1 where pos = '2a' returns tha counter of all 2a, plus one. if 2a exists 10 times, this expression returns 11
the other literal text in there is '2a', which will literally be inserted..
this is oracle syntax

other rdbms may use select into

[cjard]

ps; i thought about doing it using a running total style query (triangluar cartesian join) but couldnt get anything out of it..