title: SQL Order the rows
body: <p>This is the following db structure i have, here the issue we are facing to order the rows based on the filed wise.</p>
  
  <p><strong>Field</strong> - table hold the field information ed, 2002,2015, Region 1 etc</p>
  
  <p><strong>IndicatorData</strong>- hold the row data </p>
  
  <p><strong>datafield</strong> - relationship with IndicatorData table... row can have multiple fields</p>
  
  <pre><code>/****** object:  table [dbo].[indicatordata] ******/ 
  create table [dbo].[indicatordata](
      [id] [bigint] null,
      [value] [decimal](18, 2) null,
      [hopevalue] [decimal](18, 2) null,
      [indicatorid] [int] null,
      [datakind] [int] null
  ) on [primary]
  go
  insert [dbo].[indicatordata] ([id], [value], [hopevalue], [indicatorid], [datakind]) values (195045, cast(70.00 as decimal(18, 2)), cast(0.00 as decimal(18, 2)), 2032, 0)
  insert [dbo].[indicatordata] ([id], [value], [hopevalue], [indicatorid], [datakind]) values (195046, cast(40.00 as decimal(18, 2)), cast(0.00 as decimal(18, 2)), 2032, 0)
  insert [dbo].[indicatordata] ([id], [value], [hopevalue], [indicatorid], [datakind]) values (195047, cast(5.00 as decimal(18, 2)), cast(0.00 as decimal(18, 2)), 2032, 0)
  insert [dbo].[indicatordata] ([id], [value], [hopevalue], [indicatorid], [datakind]) values (195048, cast(100.00 as decimal(18, 2)), cast(0.00 as decimal(18, 2)), 2032, 0)
  insert [dbo].[indicatordata] ([id], [value], [hopevalue], [indicatorid], [datakind]) values (195049, cast(87.00 as decimal(18, 2)), cast(0.00 as decimal(18, 2)), 2032, 0)
  insert [dbo].[indicatordata] ([id], [value], [hopevalue], [indicatorid], [datakind]) values (195050, cast(9.00 as decimal(18, 2)), cast(0.00 as decimal(18, 2)), 2032, 0)
  /****** object:  table [dbo].[indicator]  ******/
  go
  create table [dbo].[indicator](
      [id] [int] null,
      [name] [varchar](50) null
  ) on [primary]
  go
  insert [dbo].[indicator] ([id], [name]) values (2032, n'test tile')
  /****** object:  table [dbo].[field]    ******/
   go
  create table [dbo].[field](
      [id] [int] null,
      [name] [varchar](255) null,
      [rank] [int] null,
      [parentid] [int] null
  ) on [primary]
  go
  insert [dbo].[field] ([id], [name], [rank], [parentid]) values (120, n'2006', 18, 57)
  insert [dbo].[field] ([id], [name], [rank], [parentid]) values (63, n'2015', 17, 57)
  insert [dbo].[field] ([id], [name], [rank], [parentid]) values (303, n'2007', 9, 57)
  insert [dbo].[field] ([id], [name], [rank], [parentid]) values (168, n'2018', 20, 57)
  insert [dbo].[field] ([id], [name], [rank], [parentid]) values (1463, n'region 1', 1, 1459)
  insert [dbo].[field] ([id], [name], [rank], [parentid]) values (1461, n'region 2', 3, 1459)
  insert [dbo].[field] ([id], [name], [rank], [parentid]) values (57, n'year', 0, 0)
  insert [dbo].[field] ([id], [name], [rank], [parentid]) values (1459, n'region', 0, 0)
  insert [dbo].[field] ([id], [name], [rank], [parentid]) values (315, n'2002', 1, 57)
  insert [dbo].[field] ([id], [name], [rank], [parentid]) values (123, n'2017', 19, 57)
  /****** object:  table [dbo].[datafields]     ******/
  set ansi_nulls on
  go
  create table [dbo].[datafields](
      [dataid] [int] null,
      [fieldid] [int] null
  ) on [primary]
  go
  insert [dbo].[datafields] ([dataid], [fieldid]) values (195045, 120)
  insert [dbo].[datafields] ([dataid], [fieldid]) values (195045, 1463)
  insert [dbo].[datafields] ([dataid], [fieldid]) values (195046, 63)
  insert [dbo].[datafields] ([dataid], [fieldid]) values (195046, 1461)
  insert [dbo].[datafields] ([dataid], [fieldid]) values (195047, 303)
  insert [dbo].[datafields] ([dataid], [fieldid]) values (195047, 1463)
  insert [dbo].[datafields] ([dataid], [fieldid]) values (195048, 168)
  insert [dbo].[datafields] ([dataid], [fieldid]) values (195048, 1463)
  insert [dbo].[datafields] ([dataid], [fieldid]) values (195049, 315)
  insert [dbo].[datafields] ([dataid], [fieldid]) values (195049, 1463)
  insert [dbo].[datafields] ([dataid], [fieldid]) values (195050, 123)
  insert [dbo].[datafields] ([dataid], [fieldid]) values (195050, 1463)
  go
  </code></pre>
  
  <p>Here is the query that i have tried to archive it. but its fail </p>
  
  <pre><code>    select fieldid, groupedData.value as value, groupedData.hopeValue as hopeValue, 
  groupedData.datakind, groupedData.id as id, FieldSelector.name,
   groupedData.rank
  from DataFields FieldsToInsert
  join (
  select d.id id,min(d.datakind) as datakind, sum(rank) rank, min(value) value, min(hopeValue) hopeValue 
  from indicatorData d join datafields df on d.id = df.dataid
    join field f  on df.fieldId=f.id  where indicatorId=2032 
  group by d.id) groupedData on FieldsToInsert.dataid = groupedData.id 
  join Field FieldSelector  on FieldSelector.id=FieldsToInsert.fieldId
  order by  groupedData.rank asc, groupedData.id
  </code></pre>
  
  <p>Output we got it is 
  <a href="https://i.stack.imgur.com/Agklt.jpg" rel="nofollow noreferrer">View Image</a></p>
  
  <p>Expecting the output is
  <a href="https://i.stack.imgur.com/6tIuc.jpg" rel="nofollow noreferrer">View image</a></p>