How can we sort these records this way ? - SQL HELP

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  February 24, 2017 at 8:31 am

  #319013

  Take a look at the output attached.
  What I need is when there is a value in the histclaim column, 
  then the records that has that  value in t he CLAIMNUM column should be 
  listed immediately below the record that had the HISTCLAIM.

  Ex: #6 should be immediately under #2

  

  
IF object_id('tempdb..#a') IS NOT NULL DROP TABLE #a;

  create table #a
(
memberid varchar(40),
claimnum varchar(10),
claimB varchar(10),
histclaim varchar(10)
)

  INSERT INTO #a( memberid, claimnum, claimB, histclaim )
VALUES
( 'a100', 'CLM050','CLAIMB', NULL ) ;

  INSERT INTO #a( memberid, claimnum, claimB, histclaim )
VALUES
( 'a100', 'CLM200','CLAIMB200', 'CLM100' ) ;

  INSERT INTO #a( memberid, claimnum, claimB, histclaim )
VALUES
( 'aCVB', 'CLM300FF',NULL, NULL ) ;

  INSERT INTO #a( memberid, claimnum, claimB, histclaim )
VALUES
( 'aCVBX', 'CLM350FF',NULL, NULL ) ;

  INSERT INTO #a( memberid, claimnum, claimB, histclaim )
VALUES
( 'a105', 'CLM700',NULL, NULL ) ;

  INSERT INTO #a( memberid, claimnum, claimB, histclaim )
VALUES
( 'a100', 'CLM100','CLAIMB', NULL ) ;

  INSERT INTO #a( memberid, claimnum, claimB, histclaim )
VALUES
( 'a400', 'CLM600','CLAIMB', NULL ) ;

  Select * FROM #a;

• Henrico Bekker

  One Orange Chip

  Points: 27652

  More actions

  February 24, 2017 at 8:43 am

  #1930182

  Select * FROM #a order by memberid, histclaim DESC

  is that the order you require?

  -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

• Thom A

  SSC Guru

  Points: 99126

  More actions

  February 24, 2017 at 8:45 am

  #1930185

  What defines the rest of your ordering? Do you need to retain the ordering in memberid of a100, then aCVB, then aCvbx, then a105, etc? Does the rest of the ordering actually matter, or do you only care about having a claim being the line after it's a line with it's histclaim?

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  February 24, 2017 at 9:13 am

  #1930194

  Bekker: Thanks That works!

  
IF object_id('tempdb..#a') IS NOT NULL DROP TABLE #a;

  create table #a
(
memberid varchar(40),
claimnum varchar(10),
claimB varchar(10),
histclaim varchar(10)
)

  INSERT INTO #a( memberid, claimnum, claimB, histclaim )
VALUES
( 'a100', 'CLM050','CLAIMB', NULL ) ;

  INSERT INTO #a( memberid, claimnum, claimB, histclaim )
VALUES
( 'a100', 'CLM200','CLAIMB200', 'CLM100' ) ;

  INSERT INTO #a( memberid, claimnum, claimB, histclaim )
VALUES
( 'a200', 'CLM200','CLAIMB200', 'CLX200' ) ;

  INSERT INTO #a( memberid, claimnum, claimB, histclaim )
VALUES
( 'a200', 'CLX200','CLAIMB200', '' ) ;

  INSERT INTO #a( memberid, claimnum, claimB, histclaim )
VALUES
( 'aCVB', 'CLM300FF',NULL, NULL ) ;

  INSERT INTO #a( memberid, claimnum, claimB, histclaim )
VALUES
( 'aCVBX', 'CLM350FF',NULL, NULL ) ;

  INSERT INTO #a( memberid, claimnum, claimB, histclaim )
VALUES
( 'a105', 'CLM700',NULL, NULL ) ;

  INSERT INTO #a( memberid, claimnum, claimB, histclaim )
VALUES
( 'a100', 'CLM100','CLAIMB', NULL ) ;

  INSERT INTO #a( memberid, claimnum, claimB, histclaim )
VALUES
( 'a400', 'CLM600','CLAIMB', NULL ) ;

  Select * FROM #a order by memberid, histclaim DESC