Click here to Skip to main content
15,943,148 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi

I need a select statement that can select two columns as incremental columns.
the two fields that need this are not columns in the table.
they need to be selecting in a select statement.

eg.
ID |  KeyID | Name | Number | 
1  |   1    | Ivan |  21
2  |   2    | Jake |  45
3  |   2    | Jake |  45
4  |   3    | Will |  74
5  |   3    | Will |  65

the two fields i refer to are the ID and KeyID fields.
the ID fields generates in increments of 1 for each record selected , which I can use the IDENTTY(int,1,1) for.

but the trouble comes in with the KeyID fields.
I need to select a KeyID for each record that shares a name and increment it by one for every new name. as you can see in the example. Again keep in mind this has to be done in a select statement as the KeyID field does not exist.

Any advice on how this can be achieved?

Thank you very much for any reply.

This is the querie:

SQL
SELECT
IDENTITY(INT,1,1) as KeyValueID,
  dense_rank() OVER(ORDER BY [importdata.dbo.sheet2$2011.ITEM]) AS KeyID,importdata.dbo.sheet2$2011.ITEM AS Item,importdata.dbo.sheet2$2011.ITEMDESC,cast('1' as int) as SortOrder
into #bf_keyValues
--(KeyValueID,KeyID,Item,Description,DefaultSortOrder)
from importdata.dbo.sheet1$2011
inner join importdata.dbo.sheet2$2011
on importdata.dbo.sheet1$2011.CUSTOMER =  importdata.dbo.sheet2$2011.CUSTOMER
inner join importdata.dbo.sheet3$2011
on importdata.dbo.sheet1$2011.CUSTOMER =  importdata.dbo.sheet3$2011.CUSTOMER
order by ITEMDESC asc

insert into bf_keyValues
select * from #bf_keyValues

select * from bf_keyValues


I need the second statement (
SQL
dense_rank() OVER(ORDER BY [importdata.dbo.sheet2$2011.ITEM]) AS KeyID
), to generate a new number for every item that appears in the ITEM column. There are Multiple item and some may even repeat.
Posted
Updated 4-Jun-13 19:56pm
v4

SQL
SELECT DISTINCT [ID], [Name], dense_rank() OVER( ORDER BY [Name]) AS NewKeyID
FROM a
order by [ID];

Happy Coding!
:)
 
Share this answer
 
v2
Comments
Member 9374423 4-Jun-13 6:20am    
Hi
I'm sure this code will work, but the "Name" fields actually comes from a different database all together and when i type in [DbPlayers.dbo.tbPlayers.pName], I get an invalid column name error.
Aarti Meswania 4-Jun-13 7:00am    
SELECT DISTINCT [a.ID], [P.PName], dense_rank() OVER( ORDER BY [p.PName]) AS NewKeyID
FROM YourTableName a
Left Join DbPlayers.dbo.tbPlayers P on P.ID = a.ID
order by [a.ID];

gvprabu 4-Jun-13 6:36am    
Check your column name its "Name or pName" in your Code ([DbPlayers.dbo.tbPlayers.pName]).
Member 9374423 4-Jun-13 8:39am    
it was only used as an example.
Aarti Meswania 4-Jun-13 8:57am    
yes it's example
make sure column name 'PName' is exist in 'DbPlayers.dbo.tbPlayers'
Sample Code tried in oracle

select ID, dense_rank() OVER(ORDER BY Name) KeyID, Name, No
   from (select Name, No, rownum ID
           from (select 'Ivan' Name, 21 No
                   from dual
                 union all
                 select 'Jake', 45
                   from dual
                 union all
                 select 'Jake', 45
                   from dual
                 union all
                 select 'Will', 74
                   from dual
                 union all
                 select 'Will', 65 from dual))


Output as below
        ID      KeyID   Name    No
1	1	1	Ivan	21
2	2	2	Jake	45
3	3	2	Jake	45
4	4	3	Will	74
5	5	3	Will	65


Links for more details : Analytic Functions
 
Share this answer
 
v2
Comments
Member 9374423 4-Jun-13 8:38am    
Great answer only there more than a thousand names to select individually. that would take way to long
Member 9374423 5-Jun-13 1:54am    
HI Please see the updated querie
[EDIT #1]
Please, read my comments and follow the links.

Try this:
SQL
--declare first table-variable 
DECLARE @tab1 TABLE (ID INT IDENTITY(1,1),  KeyID INT )
--insert data
INSERT INTO @tab1 (KeyId)
VALUES(1)
INSERT INTO @tab1 (KeyId)
VALUES(2)
INSERT INTO @tab1 (KeyId)
VALUES(2)
INSERT INTO @tab1 (KeyId)
VALUES(3)
INSERT INTO @tab1 (KeyId)
VALUES(3)

--declare second table-variable
--FKID -> foreign key in this table equal to ID in frist table
DECLARE @tab2 TABLE (FKID INT, [Name] VARCHAR(30), Number INT)
--insert data
INSERT INTO @tab2 (FKID, [Name], Number)
VALUES (1, 'Ivan',21)
INSERT INTO @tab2 (FKID, [Name], Number)
VALUES (2, 'Jake',45)
INSERT INTO @tab2 (FKID, [Name], Number)
VALUES (3, 'Jake',45)
INSERT INTO @tab2 (FKID, [Name], Number)
VALUES (4, 'Will',74)
INSERT INTO @tab2 (FKID, [Name], Number)
VALUES (5, 'Will',65)

--display all joined data
SELECT t1.*, t2.*
FROM @tab1 AS t1 INNER JOIN @tab2 AS t2 ON t1.ID = t2.FKID

--display data with new key id for each name
SELECT t1.ID, t2.[Name], ROW_NUMBER() OVER(PARTITION BY t2.[Name] ORDER BY t1.[ID]) AS NewKeyID
FROM @tab1 AS t1 INNER JOIN @tab2 AS t2 ON t1.ID = t2.FKID


First result:
ID    KeyID    FKID     Name    Number
1	1	1	Ivan	21
2	2	2	Jake	45
3	2	3	Jake	45
4	3	4	Will	74
5	3	5	Will	65


Second result:
ID  Name    NewKeyID
1   Ivan    1
2   Jake    1
3   Jake    2
4   Will    1
5   Will    2


Is it clear now?
[/EDIT]
 
Share this answer
 
v2
Comments
Member 9374423 4-Jun-13 6:20am    
Hi
I'm sure this code will work, but the "Name" fields actually comes from a different database all together and when i type in [DbPlayers.dbo.tbPlayers.pName], I get an invalid column name error.
Maciej Los 4-Jun-13 6:23am    
There is no problem... You need to join data from both tables:
JOIN's[^]
Visual Representation of SQL Joins[^]
Member 9374423 4-Jun-13 6:50am    
i do join the two tables.
Maciej Los 4-Jun-13 7:05am    
And...
Member 9374423 4-Jun-13 8:36am    
still the error I join like the answer above

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900