title: Order a query with two keys SQL Server 2008
body: <p>I am trying to order a query by two keys. The query is built with several subqueries. The table contains, beside columns with other data, two columns, Key and Key_Father. So I need to order the results since SQL to print the results in a report. This is an example:</p>
  
  <p>Key&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Key_Father<br>
  4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;NULL<br>
  1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;4<br>
  2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;4<br>
  7&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;NULL<br>
  1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;7<br>
  2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;7  </p>
  
  <p>As you can see is a structure father-son, where a row is a father if the Key_Father is NULL and the Key column start from one for each son with a different father.
  The first subquery gives the data in order, because is stored on that order in the table, but the second subquery that uses a group by, no. So I tried adding a extra column with Row_Number on the first subquery to keep that order, but the second subquery does the same thing.</p>
  
  <p>This is the query:</p>
  
  <pre><code>SELECT Orden,INV_Key,Key_Padre,INV.INV_ID,INV.BOD_Bodega_ID,
  CASE WHEN MAX(HIS_Ventas) &gt; 0 OR max(HIS_Disponible) &gt; 0 THEN 1 ELSE 0 END AS Participacion,MAX(ISNULL(HIS_Ventas,0)) AS Ventas
  FROM(SELECT ROW_NUMBER() OVER (ORDER BY C.INV_Compra_ID) Orden,C.BOD_Bodega_ID,INV_Key,Key_Padre,CD.INV_ID
  FROM dbo.INV_COMPRAS_USADOS C
  INNER JOIN dbo.INV_COMPRAS_USADOS_DET CD ON C.INV_Compra_ID = CD.INV_Compra_ID
  WHERE C.INV_Compra_ID = @Compra_ID
  AND ((Key_Padre IS NULL AND CD.INV_Catalogo_Codigo = ISNULL(@Cod_Catalogo,CD.INV_Catalogo_Codigo)
  AND INV_Key IN (SELECT DISTINCT Key_Padre
  FROM dbo.INV_COMPRAS_USADOS_DET
  WHERE INV_Compra_ID = @Compra_ID AND Key_Padre IS NOT NULL))
  OR Key_Padre IN (SELECT DISTINCT INV_Key
  FROM dbo.INV_COMPRAS_USADOS_DET
  WHERE INV_Compra_ID = @Compra_ID AND (Key_Padre IS NULL AND CD.INV_Catalogo_Codigo = ISNULL(@Cod_Catalogo,CD.INV_Catalogo_Codigo))))) INV
  LEFT JOIN DBO.HIS_HISTORICO_DETALLE HD ON INV.INV_ID = HD.INV_ID AND HD.BOD_Bodega_ID = INV.BOD_Bodega_ID
  LEFT JOIN DBO.HIS_HISTORICO_INVENTARIO H on H.HIS_Historico_ID= HD.HIS_Historico_ID AND (CONVERT(datetime,(convert(varchar(20),HIS_Historico_Ano) + '/' + convert(varchar(20),HIS_Historico_Mes) + '/01')) BETWEEN @FechaDesde AND @FechaHasta)
  WHERE H.HIS_Historico_Mes IS NOT NULL OR INV.INV_ID IS NULL
  GROUP BY Orden,INV_Key,Key_Padre,INV.INV_ID,INV.BOD_Bodega_ID,HIS_Historico_Ano,HIS_Historico_Mes
  </code></pre>
  
  <p>Another interesting thing (well for me) is that when I change the @Variables for Constant values, the second query keeps the correct order, even when the constant values are the same that the @variables. This is just a portion of the total query, is a subquery that needs of another two selects, and I need to keep the order from those selects too.</p>
  
  <p>So I hope that someone could help me with this. Thanks! </p>