How to order the result based on the column values in sql server

Question

I have a table with the following type:

Id  Parent_id  Code   Name       market
1    NULL      1ex    name 1       3
2    1         2ex    name 2       3
3    1         3ex    name 3       3
4    Null      4ex    name 4       1
5    null      5ex    name 5       3
6    4         6ex    name 6       3

I wanted to select code and name from the above table such that it is ordered in the following way:

1. based on the market where market id=3
2. Parent id
3. related child
4. others

ie. id 1 (Parent_id) should be displayed first followed by id 2 and 3 (Child id). The values in 'parent_id' are from the column 'id'.

I have built the following query so far and i am feeling little difficult to order the parent code and the related child codes.

select code,name from tbl_codes A
order by  CASE WHEN(A.[Market] = 3) THEN 0 ELSE 1 END

Can someone please help me out.

Answer 1

A recursive CTE is the best way to construct a parent/child heirarchy as follows:

-- Set up test data
CREATE TABLE tbl_codes (id INT , Parent_id INT, Code VARCHAR(3), NAME VARCHAR(12), Market INT)
INSERT tbl_codes 
SELECT 1, NULL, '1ex', 'name 1', 3 UNION ALL
SELECT 2, 1    , '2ex', 'name 2', 3 UNION ALL
SELECT 3, 1    , '3ex', 'name 3', 3 UNION ALL
SELECT 4, NULL , '4ex', 'name 4', 1 UNION ALL
SELECT 5, NULL , '5ex', 'name 5', 3 UNION ALL
SELECT 6, 4    , '6ex', 'name 6', 3

CREATE VIEW [dbo].[View_ParentChild]
AS
-- Use a recursive CTE to build a parent/child heirarchy
WITH  
  RecursiveCTE AS 
  (
     SELECT
       id,
       name,
       parent_id,
       Code,
       market,
       sort = id
     FROM
      tbl_codes
     WHERE
      parent_id IS NULL
     UNION ALL
     SELECT
       tbl_codes.id,
       tbl_codes.name,
       tbl_codes.parent_id,
       tbl_codes.Code,
       tbl_codes.market,
       sort = tbl_codes.parent_id
     FROM
      tbl_codes
      INNER JOIN RecursiveCTE
        ON tbl_codes.parent_id = RecursiveCTE.id
     WHERE
      tbl_codes.parent_id IS NOT NULL
  )
  SELECT
    Code,
    NAME,
    Market,
    Sort
  FROM
    RecursiveCTE

GO

As per your request I have refactored the query as a VIEW.

To use the view:

SELECT
  *
FROM
  dbo.View_ParentChild AS vpc
ORDER BY
  CASE WHEN ( Market = 3 ) THEN 0
       ELSE 1
  END,
  sort 

It gives the following result:

    Code    NAME    Market  Sort
    ----    ------  ------  ----
    1ex     name 1      3     1
    2ex     name 2      3     1
    3ex     name 3      3     1
    6ex     name 6      3     4
    5ex     name 5      3     5
    4ex     name 4      1     4

To learn more about recursive CTEs click here

And, as requested, is a new version of the view that does not use a recursive CTE

CREATE VIEW [dbo].[View_ParentChild_v2]
AS
SELECT
  id,
  Code,
  market,
  sort
FROM
(
  SELECT
    id,
    name,
    parent_id,
    Code,
    market,
    sort = id
  FROM
    tbl_codes
  WHERE
    parent_id IS NULL

  UNION ALL

  SELECT
    tbl_codes.id,
    tbl_codes.name,
    tbl_codes.parent_id,
    tbl_codes.Code,
    tbl_codes.market,
    sort = tbl_codes.parent_id
  FROM
    tbl_codes
  WHERE
    tbl_codes.parent_id IS NOT NULL
) AS T

GO

Used as follows:

SELECT
  *
FROM
  View_ParentChild_v2
ORDER BY
  CASE WHEN ( Market = 3 ) THEN 0
       ELSE 1
  END,
  sort 

nb: The first version, using a recursive CTE, could handle virtually unlimited levels of Parent/Child while version 2 only handles one level.

Answer 2

Try this

SELECT  code ,
        name 
FROM    tbl_codes A
ORDER BY CASE WHEN ( A.[Market] = 3 ) THEN 0
             ELSE 1
        END  ,
        CASE WHEN ( ISNULL(parent_id,0) = 1 ) THEN 0
             ELSE 1
        END 

Answer 3

You can put condition in your columns. Try:

SELECT  code ,
        name ,
        CASE WHEN ( A.[Market] = 3 ) THEN 0
             ELSE 1
        END AS marketOrder ,
        CASE WHEN ( parent_id = 1 ) THEN 0
             ELSE 1
        END AS parentOrder
FROM    tbl_codes A
ORDER BY parentOrder ,
        marketOrder