Get maximum number with combination of columns 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.
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