Hello I am new here and I would appriciate your help
I have 2 tables for example region (id,name) and state(id,R_id,name)
And I would like in a quey to build another columns for each state so it will be identity within it region
Thanks
Sandu

I assume that what you are asking for is something that will build the R_ID column in the state table... the bad news is, can't be done... there's nothing in either table that would allow you to do that... if there were, you wouldn't need the R_ID column in the state table to begin with. I'm afraid this is going to be a manual assignment... the good news is, there's only 50 states :)

--Jeff Moden

Hi Thank for your quick reply.
Well not exactly
Lets say that Region has the following Records (I wont fill them al)
E East
S South
And States has the following Records:
NC E North Carolina
SC E South Carolina
GA E Georgia
AR S Arkansas
MS S Mississippi
AL S Alabama

I would like to produce a query that will be a join of the two tables plus a column which will be the order of the state in region
For example
E East 1 GA North Carolina
E East 2 NC South Carolina
E East 3 SC Georgia
S South 1 AL Alabama
S South 2 AR Arkansas
S South 3 MS Mississippi
The bold is what I am looking for
And if it was for the sates I would build it my self manually but this is only for an example of master detail where i need to use such a query
sandu

Why didn't you say so in the first place? :D

Haven't tested it but this should be pretty close...

 SELECT r.ID,r.Name,
        RegionCount = (SELECT COUNT(*)
                         FROM State s1
                        WHERE s1.R_ID = s.R_ID
                          AND s1.ID <= s.ID)
        s.ID,s.Name
   FROM Region r,
        State s
  WHERE r.ID = s.R_ID
  ORDER BY s.R_ID, s.ID

--Jeff Moden

10x