title: Sort by name but also by key
body: <p>I have a table with departments key (<code>DEPA_KEY</code>) and rooms key (<code>OPRO_KEY</code>). Each room is inside department, so each <code>OPRO_KEY</code> belongs to <code>DEPA_KEY</code>. </p>
  
  <p>My question is how to sort departments by name - <code>OPRO_NME</code>. If I do simply:</p>
  
  <pre><code>SELECT * FROM @OPRO_ROOM
  ORDER BY OPRO_NME
  </code></pre>
  
  <p>than rooms will be mixed between departments which I don't want. Rooms and departments must stay together, so everyone can easily see which rooms are inside which department.</p>
  
  <p>Here is table definition and insert:</p>
  
  <pre><code>DECLARE @OPRO_ROOM TABLE
  (
  DEPA_KEY INT,
  OPRO_KEY INT,
  OPRO_NME VARCHAR(255)
  )
  
  INSERT INTO @OPRO_ROOM(DEPA_KEY, OPRO_KEY, OPRO_NME)
  VALUES(120000338,NULL,'B oddelek'),
  (120000338,160000014,'Soba 1'),
  (120000338,160000015,'Soba 2'),
  (120000338,160000016,'Soba 3'),
  (120000306,NULL,'C oddelek'),
  (120000306,160000030,'Soba 1'),
  (120000306,160000031,'Soba 3'),
  (120000306,160000032,'Soba 7'),
  (120000306,160000032,'Soba 7'),
  (120000305,NULL,'A oddelek'),
  (120000305,160000001,'Soba 1'),
  (120000305,160000002,'Soba 3'),
  (120000305,160000003,'Soba 4')
  </code></pre>