How to sort columns.

• Gators_79

  SSC Veteran

  Points: 251

  More actions

  April 4, 2016 at 8:58 am

  #99024

  I have a table with 2 columns

  Tags Value

  The first 14 tags have to be in the order I give . However after that I want all tags with the numeric value at the end sorted

  SELECT LTRIM(RTRIM(TagName))

  , LTRIM(RTRIM(PLCPointVal))

  FROM Table

  WHERE

  TagName LIKE ' %DataDownloadBit%'

  OR TagName LIKE '%NextAddDeletePO%'

  OR TagName LIKE '%NextPONonConfFlg%'

  OR TagName LIKE '%NextPOTargtQuant%'

  OR TagName LIKE '%NextPOSapCode%'

  OR TagName LIKE '%NextPOSeq%'

  OR TagName LIKE '%NextCasingCode%'

  OR TagName LIKE '%NextPOTank%'

  OR TagName LIKE '%NextNumPart1%'

  OR TagName LIKE '%NextNumPart2%'

  OR TagName LIKE '%NextNumPart3%'

  OR TagName LIKE '%NextOrgNumPart1%'

  OR TagName LIKE '%NextOrgmPart2%'

  OR TagName LIKE '%NextOrgNumPart3%'

  Then the rest of the TagName are like

  tagor1

  tagor2

  tagor3

  tagor4

  tagor1

  tagop2

  tagop3

  I want the first 14 then the rest in order like this

  tagor1

  tagop1

  tagog1

  tagor2

  tagop2

  tagog2

  I tried with a union

  and the order by

  ISNULL(STUFF( TagName, 1, PATINDEX('%[0-9]%', TagName)-1, ''),0)

  Im on SQL 2000

• Lowell

  SSC Guru

  Points: 323518

  More actions

  April 4, 2016 at 1:26 pm

  #1869614

  i would think youll start with a case expression, and then order by the tagname anyway as the second condition;

  how do you get the value "tagor1", for example? uis that the full tagname, or a substring?

  ORDER BY

  CASE

  WHEN TagName LIKE ' %DataDownloadBit%' THEN 1

  WHEN TagName LIKE '%NextAddDeletePO%' THEN 2

  WHEN TagName LIKE '%NextPONonConfFlg%' THEN 3

  WHEN TagName LIKE '%NextPOTargtQuant%' THEN 4

  WHEN TagName LIKE '%NextPOSapCode%' THEN 5

  WHEN TagName LIKE '%NextPOSeq%' THEN 6

  WHEN TagName LIKE '%NextCasingCode%' THEN 7

  WHEN TagName LIKE '%NextPOTank%' THEN 8

  WHEN TagName LIKE '%NextNumPart1%' THEN 9

  WHEN TagName LIKE '%NextNumPart2%' THEN 10

  WHEN TagName LIKE '%NextNumPart3%' THEN 1`

  WHEN TagName LIKE '%NextWHENgNumPart1%' THEN 12

  WHEN TagName LIKE '%NextWHENgmPart2%' THEN 13

  WHEN TagName LIKE '%NextWHENgNumPart3%'THEN 14

  ELSE 99 END,TagName --tagor1 substring isntead?

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!