Puzzle

• Anju Renjith

  Ten Centuries

  Points: 1135

  More actions

  September 12, 2012 at 5:12 am

  #393160

  Step:1

  CREATE TABLE tbl_Group_Test

  (

  _ID INT IDENTITY,

  _Place VARCHAR(50),

  _Name VARCHAR(100)

  )

  Step:2

  INSERT INTO tbl_Group_Test VALUES('Tamil Nadu','Abdul Kalam')

  INSERT INTO tbl_Group_Test VALUES('Tamil Nadu','Karunanidhi')

  INSERT INTO tbl_Group_Test VALUES('Tamil Nadu','Jayalalitha')

  INSERT INTO tbl_Group_Test VALUES('Tamil Nadu','Robin Singh')

  INSERT INTO tbl_Group_Test VALUES('Maharashtra','Sachin Tendulkar')

  INSERT INTO tbl_Group_Test VALUES('Orissa','Sourav Ganguly')

  Output must be

  _ID _Place _Name

  -------- --------------------------------------------

  1 Tamil Nadu Abdul Kalam

  2 " Karunanidhi

  3 " Jayalalitha

  4 " Robin Singh

  5 Maharashtra Sachin Tendulkar

  6 Orissa Sourav Ganguly

  ---------------------------------------------------------

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  September 12, 2012 at 5:26 am

  #1536652

  Anju Renjith (9/12/2012)

  ---

  Step:1

  CREATE TABLE tbl_Group_Test

  (

  _ID INT IDENTITY,

  _Place VARCHAR(50),

  _Name VARCHAR(100)

  )

  Step:2

  INSERT INTO tbl_Group_Test VALUES('Tamil Nadu','Abdul Kalam')

  INSERT INTO tbl_Group_Test VALUES('Tamil Nadu','Karunanidhi')

  INSERT INTO tbl_Group_Test VALUES('Tamil Nadu','Jayalalitha')

  INSERT INTO tbl_Group_Test VALUES('Tamil Nadu','Robin Singh')

  INSERT INTO tbl_Group_Test VALUES('Maharashtra','Sachin Tendulkar')

  INSERT INTO tbl_Group_Test VALUES('Orissa','Sourav Ganguly')

  Output must be

  _ID _Place _Name

  -------- --------------------------------------------

  1 Tamil Nadu Abdul Kalam

  2 " Karunanidhi

  3 " Jayalalitha

  4 " Robin Singh

  5 Maharashtra Sachin Tendulkar

  6 Orissa Sourav Ganguly

  ---------------------------------------------------------

  Looks like homework to me. Rather than provide a full solution - which would defeat the point of setting homework, see what you can do with this:

  SELECT

  _ID,

  _Place,

  _Name,

  rn = ROW_NUMBER() OVER(PARTITION BY _Place ORDER BY _ID)

  FROM tbl_Group_Test

  ORDER BY _ID

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• rhythmk

  SSCertifiable

  Points: 7271

  More actions

  September 12, 2012 at 5:32 am

  #1536657

  Anju Renjith (9/12/2012)

  ---

  Step:1

  CREATE TABLE tbl_Group_Test

  (

  _ID INT IDENTITY,

  _Place VARCHAR(50),

  _Name VARCHAR(100)

  )

  Step:2

  INSERT INTO tbl_Group_Test VALUES('Tamil Nadu','Abdul Kalam')

  INSERT INTO tbl_Group_Test VALUES('Tamil Nadu','Karunanidhi')

  INSERT INTO tbl_Group_Test VALUES('Tamil Nadu','Jayalalitha')

  INSERT INTO tbl_Group_Test VALUES('Tamil Nadu','Robin Singh')

  INSERT INTO tbl_Group_Test VALUES('Maharashtra','Sachin Tendulkar')

  INSERT INTO tbl_Group_Test VALUES('Orissa','Sourav Ganguly')

  Output must be

  _ID _Place _Name

  -------- --------------------------------------------

  1 Tamil Nadu Abdul Kalam

  2 " Karunanidhi

  3 " Jayalalitha

  4 " Robin Singh

  5 Maharashtra Sachin Tendulkar

  6 Orissa Sourav Ganguly

  ---------------------------------------------------------

  Sorry but I don't understand the exact requirement because if you want to get this output only then what is point of saying puzzle 😉

  --rhythmk
  ------------------------------------------------------------------
  To post your question use below link
  
  https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
  🙂