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.

I have table with columns like AutoID, Number, Name, City, State, Country.

What I wanted is the maximum number entered in the “Number” column with the combination of Name, City, State and Country.

Example:

Name  City State Country
----- ---- ----- -------
Smith NY   NY    USA
John  NY   NY    USA
John  NJ   NY    USA

Now smith should get “Number” 1, John 2, and again John(in NJ) 1 as he is the first from NJ.

I can simply put a where clause in query and get the max number + 1. But the problem is that when I have huge amount of data and the number of users increases, my query will be really slow. I am also inserting data in the same table so it will keep on piling.

I hope I have made my self clear.

share|improve this question

closed as unclear what you're asking by Mark Storey-Smith, Marian, Max Vernon, RolandoMySQLDBA, Kin Feb 6 at 20:17

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question.If this question can be reworded to fit the rules in the help center, please edit the question.

1 Answer 1

If I'm understanding you correctly then the following should give you what you want.

  create table xt (name NVARCHAR(50) NULL, 
  city nvarchar(2) NULL,
  state nvarchar(2) NULL,
  country nvarchar(3) NULL)

  INSERT into xt (name, city, state, country)
  values(N'smith', N'ny', N'ny', N'usa'),
  (N'john', N'ny', N'ny', N'usa'),
  (N'john', N'nj', N'ny', N'usa')

SELECT name,
       city,
       state,
       ROW_NUMBER() OVER (PARTITION BY name ORDER BY (SELECT 0)) AS RN
  FROM xt
   GROUP BY name, city, state
   order by rn

drop table xt

This should provide the following output

name    city    state   RN
john    nj  ny  1
smith   ny  ny  1
john    ny  ny  2
share|improve this answer

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