1
id     salary       address

101     $400         NY
101     $200         NY
102     $102         TX
102     $127         TX
102     $391         TX

Now how to assign key for every repeating row based on id column

id       salary        address      key

101       $400          NY           1
101       $200          NY           2
102       $102          TX           1
102       $127          TX           2
102       $391          TX           3
Jens
  • 67,715
  • 15
  • 98
  • 113
suri5913
  • 59
  • 6
  • How do you know which salary is the correct one? – Ann Addicks Oct 15 '15 at 11:46
  • Hi Ann , that i can sort later in my real Table – suri5913 Oct 15 '15 at 11:54
  • Please share the code/query/whatever what you tried to achieve your goal. Please read [ask] in [help] to get help how to improve your question. Requesting code without showing any effort to solve your problem is for a freelancer site, not for a Q&A site. – Pred Oct 15 '15 at 12:24
  • Hi pred , I am very new to SQL, So unable to write these type of complex queries.. – suri5913 Oct 16 '15 at 09:08

1 Answers1

0

If I understood correctly, you want to get an ID based on the id occurrences, so you can define both as a unique key, right?

First, you can use SQL COUNT to find the number of times that an id appears on the table, then increment the result to get your id. Example in pseudocode:

sqlresult = sqlquery( COUNT(*) FROM `your_table` WHERE id = "wanted_id";);
key = sqlresult++;

Then you can define a composite key by following this post: How can I define a composite primary key in SQL?

Sorry for not being more detailed, but right now I don't have much time, if you need I can help you better later on today.

Hope I could help.

Community
  • 1
  • 1
José Gonçalves
  • 79
  • 1
  • 10