Query help

• khushbu

  SSCarpal Tunnel

  Points: 4591

  More actions

  March 25, 2010 at 4:59 am

  #146115

  Hi

  I’m failing to generate the query for below condition

  Patid disid mycol

  001 AE 1

  001 AE 1

  001 DF 2

  002 AE 1

  002 rt 2

  003 df 1

  Here patid and disid I will be getting from my_table and would require mycol to be populated with the value as stated above

  (for every uinique disid for same patid mycol should be same )

  Help please

  Thanks

  K

• Kingston Dhasian

  SSCoach

  Points: 19799

  More actions

  March 25, 2010 at 5:13 am

  #1139503

  This will do it for you..

  SELECT*, DENSE_RANK() OVER ( PARTITION BY PatID ORDER BY DisID ) myCol

  FROMmyTable

  Please make sure you post in the appropriate forums to get faster response.

  ---

  Kingston Dhasian

  How to post data/code on a forum to get the best help - Jeff Moden
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

• khushbu

  SSCarpal Tunnel

  Points: 4591

  More actions

  March 25, 2010 at 6:00 am

  #1139527

  this gives result as 1,2,3, for same patid and disid while I need 1,1,1 for same patid and disid.

  If disid changes for same patid it should increement by 1

  Patid disid mycol

  001 AE 1

  001 AE 1

  001 DF 2

  002 AE 1

  002 rt 2

  003 df 1

• Kingston Dhasian

  SSCoach

  Points: 19799

  More actions

  March 25, 2010 at 6:24 am

  #1139543

  No idea what's wrong then. It does give the desired result in my system.:unsure: Are you sure you are using DENSE_RANK and not ROW_NUMBER?

  ---

  Kingston Dhasian

  How to post data/code on a forum to get the best help - Jeff Moden
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

• khushbu

  SSCarpal Tunnel

  Points: 4591

  More actions

  April 5, 2010 at 11:48 pm

  #1146288

  requirements change ahhh!!!

  I have disid and patid and need to generate mycol. when i use dense rank function it gives incorrect value for mycol = 3 (while it shud be 1) when disid = 03 , patid = 0000643985 only on record no 17

  any help pls

  disid patid mycol

  0900006308611

  0900006308611

  0900006308611

  0900006308611

  0900006308611

  0900006308611

  0900006308611

  0900006308611

  0900006308611

  0900006308611

  0900006308611

  0300006439851

  0300006439851

  0300006439851

  0300006439851

  0300006439851

  0300006439851

  2900006439852

  2900006439852

  2900006439852

  2900006439852

  0300006485501

  0300006485501

  0300006485501

  0300006485501

  0300006485501

  0300006485501

  1500007007762

  1800007007761

  1800007007761

  1800007007761

  1800007007761

  1800007007761

• chrisfradenburg

  SSCrazy Eights

  Points: 9592

  More actions

  April 7, 2010 at 6:57 am

  #1147277

  From what I'm seeing the code that Kingston posted is working fine. The only difference I'm seeing between what his query returns and what you expect is in the last section of results. You say that patid 0000700776 and disid 15 should be 2 and 18 should be one. However, due to the fact that 15 is lower than 18 the query reverses that.

  declare @myTable table (disid char(2), patid char(10), mycol int)

  insert into @myTable values ('09','0000630861',1)

  insert into @myTable values ('09','0000630861',1)

  insert into @myTable values ('09','0000630861',1)

  insert into @myTable values ('09','0000630861',1)

  insert into @myTable values ('09','0000630861',1)

  insert into @myTable values ('09','0000630861',1)

  insert into @myTable values ('09','0000630861',1)

  insert into @myTable values ('09','0000630861',1)

  insert into @myTable values ('09','0000630861',1)

  insert into @myTable values ('09','0000630861',1)

  insert into @myTable values ('09','0000630861',1)

  insert into @myTable values ('03','0000643985',1)

  insert into @myTable values ('03','0000643985',1)

  insert into @myTable values ('03','0000643985',1)

  insert into @myTable values ('03','0000643985',1)

  insert into @myTable values ('03','0000643985',1)

  insert into @myTable values ('03','0000643985',1)

  insert into @myTable values ('29','0000643985',2)

  insert into @myTable values ('29','0000643985',2)

  insert into @myTable values ('29','0000643985',2)

  insert into @myTable values ('29','0000643985',2)

  insert into @myTable values ('03','0000648550',1)

  insert into @myTable values ('03','0000648550',1)

  insert into @myTable values ('03','0000648550',1)

  insert into @myTable values ('03','0000648550',1)

  insert into @myTable values ('03','0000648550',1)

  insert into @myTable values ('03','0000648550',1)

  insert into @myTable values ('15','0000700776',2)

  insert into @myTable values ('18','0000700776',1)

  insert into @myTable values ('18','0000700776',1)

  insert into @myTable values ('18','0000700776',1)

  insert into @myTable values ('18','0000700776',1)

  insert into @myTable values ('18','0000700776',1)

  SELECT *, DENSE_RANK() OVER ( PARTITION BY PatID ORDER BY DisID ) RankedCol

  FROM @myTable

  You can switch the order by disid to be descending but that will swap other stuff in the results too so that won't be right. Did you make a mistake in typing the result set out or does 15 need to be ranked 1 because it was put into the table first?