SQL Query Help

• RamG

  Right there with Babe

  Points: 767

  More actions

  July 6, 2010 at 4:18 am

  #234873

  Hi,

  I have two tables like application and user application. Now i want to take the application id using by majority of users

  Application

  APPIDAPPNAMEVERSION

  1Access2000

  2Access2001

  3Access2002

  4rar7

  5xp1.1

  6xp1.3

  UserApplication

  APPIDUSERNAME

  1aaa

  1bbb

  1ccc

  1ddd

  1eee

  2fff

  2ggg

  2hhh

  3iii

  6jjj

  6kkk

  4lll

  4mmm

  Expected Result

  aPPIDMAJORAppID

  11

  21

  31

  44

  56

  66

  Here

  Access 2000 is used by 6 users so the majorappid is 1 for all access applications.

  Rar doesn’t have any diff version so the appid 4 will act as majorid

  for XP veriosn 1.3 is used by 2 users so 6 is major for all xp applications.

  Test Query:

  create table #Applications

  (appid int identity,

  appname varchar(100),

  version varchar(100))

  create table #user

  (appid int ,

  username varchar(100))

  insert into #Applications

  select 'Access', '2000' union

  select 'Access', '2001' union

  select 'Access', '2002' union

  select 'xp', '1.1' union

  select 'xp', '1.3' union

  select 'rar', '7'

  insert into #user

  select '1', 'aaa' union

  select '1', 'bbb' union

  select '1', 'ccc' union

  select '1', 'ddd' union

  select '1', 'eee' union

  select '2', 'fff' union

  select '2', 'ggg' union

  select '2', 'hhh' union

  select '3', 'iii' union

  select '6', 'jjj' union

  select '6', 'kkk' union

  select '4', 'lll' union

  select '4', 'mmm'

  can any one help me please?

• Dave Morrison

  SSCrazy

  Points: 2077

  More actions

  July 6, 2010 at 5:56 am

  #1189036

  Hi Ram,

  If I have understood you correctly you want to look at a windowing function called RANK()

  Look it up in BOL or google it, I think this should give you what you need.

  Please come back to me if this looks about right and I'll try and help