title: Order by Lastname and ID
body: <p>I have two DB tables</p>
  
  <pre><code> FAMILY
   ------
   family_id (PK)
   address
   ....
  
   PERSON
   -----
   person_id (PK)
   lastname
   birthday
   family_id (FK)
   family_role (numeric &gt;1 male, 2 female, 3 male(child), 4 female(child)
   ....
  </code></pre>
  
  <p>I want to create an alphabetic family list, in which the lastname of the oldest person must be leading and ordered by the FAMILY_ROLE. I was trying something like:</p>
  
  <pre><code> select *
   from person, family
   where person.family_id = family.family_id
   order by lastname, family_role
  </code></pre>
  
  <p>Now this is not working, because last names in the same family doesn't have to be the same. So this is working </p>
  
  <pre><code> select *
   from person, family
   where person.family_id = family.family_id
   order by family_id, family_role
  </code></pre>
  
  <p>But that doesn't give me an alphabetic list.
  The output must be something like:</p>
  
  <pre><code> Test street 1 - New York (family_id = 100)
   John Bla (family_id = 100 | person_id =10 | family_role=1) 
   Sara Bla (family_id = 100 | person_id =13 | family_role=2)
   ----
   Apple street 1 - New York (family_id = 45)
   Rick Cha (family_id = 45 | person_id =1 | family_role=1)
   Lin Cha (family_id = 45 | person_id =5 | family_role=2)
   Jean App (family_id = 45 | person_id =3 | family_role=4)
   ----
   Cherry street 114 - New York (family_id = 23)
   Becky Cha (family_id = 23 | person_id =122 | family_role=2)
   ----
  </code></pre>
  
  <p>Can someone help me? Database version MSSQL 2008.</p>
  
  <p>Also please keep in mind that people can have the same last name, but belong in a different family (see the Cha families in the output example). </p>
  
  <p>Thanks!</p>