Looking for an SQL query that does an ORDER BY first and then a GROUP BY

I have a single table (called people) that includes among others: ID, code, FullName, ADDRESS1, ADDRESS2, ADDRESS3, COUNTRY and IsMember. The ID and IsMember columns are smallints and the rest are varchars. There are more columns, but they are irrelevant to this question.

My goal is to produce address labels that we can stick onto envelopes and send a newsletter to family members. At an address can have a single family member, or a whole family: father + mother + kid + kid for example. Not every person is a member (for one, you need to be older than 18). Members have an IsMember of 1, otherwise IsMember is 0. The code is a representation of the lineage of the person: children have longer codes than their parents; in-law spouses (who married into the family) have longer codes than the person who was in the family from birth.

The problem is getting the correct SQL Select query that produces the 7 columns (I don't need IsMember), but only produces 1 line (= 1 label) per ADDRESS1. And the code and FullName need to belong to the oldest person that was born into the family, not married into the family. In other words, I want the person on an address that has the shortest code.

My first idea was to write a simple query that would select all the columns. Then Order By the result based on the length() of code. And then finally run that through a Group By of the ADDRESS1 column.

But you aren't allowed to use the Group By after the Order By. And the other way around doesn't produce the correct result: instead of returning the oldest original family member it returns a random member of that family.

The data could be something like:

+----+------+----------+----------+----------+----------+----------+----------+
| ID | code | FullName | ADDRESS1 | ADDRESS2 | ADDRESS3 | COUNTRY  | IsMember |
+----+------+----------+----------+----------+----------+----------+----------+
| 1  | A1   | Alex     | Main 10  | 1234 AB  | New York | USA      | 1        |
| 2  | A1.2 | Fred     | Longstr 1| 5757 GE  | London   | UK       | 1        |
| 3  | A1a  | Alexa    | Main 10  | 1234 AB  | New York | USA      | 1        |
| 4  | J2   | John     | 2nd Str 7| 1970 BV  | The Hague| NL       | 1        |
| 5  | J2a  | Janet    | 2nd Str 7| 1970 BV  | The Hague| NL       | 1        |
| 6  | J2.1 | Alfred   | 2nd Str 7| 1970 BV  | The Hague| NL       | 1        |
| 7  | J2.2 | Eric     | 2nd Str 7| 1970 BV  | The Hague| NL       | 0        |
| 8  | J3   | Donald   | ObraStr 8| 8582 UT  | Toronto  | CA       | 0        |
+----+------+----------+----------+----------+----------+----------+----------+

And the expected result would be:

+----+------+----------+----------+----------+----------+----------+
| ID | code | FullName | ADDRESS1 | ADDRESS2 | ADDRESS3 | COUNTRY  |
+----+------+----------+----------+----------+----------+----------+
| 1  | A1   | Alex     | Main 10  | 1234 AB  | New York | USA      |
| 2  | A1.2 | Fred     | Longstr 1| 5757 GE  | London   | UK       |
| 4  | J2   | John     | 2nd Str 7| 1970 BV  | The Hague| NL       |
+----+------+----------+----------+----------+----------+----------+

In other words: we get only one result per ADDRESS1 and the corresponding data on that line belongs to the person with the shortest code. So Alex instead of Alexa on Main 1, and John instead of Janet on 2nd Str 7.
Only lines with IsMember = 1 are taken into account.
The order of the lines in the end result is irrelevant to me.

So I first tried:

Select ID
, code
, FullName
, ADDRESS1
, ADDRESS2
, ADDRESS3
, COUNTRY
from `people`
Where IsMember = 1
Order by Length(code)
Group By ADDRESS1
;

But that gives an error.

So I switched to try to nest SQL code, like

SELECT id
, code
, FullName
, ADDRESS1
, ADDRESS2
, ADDRESS3
, COUNTRY
from (
  SELECT id
  , code
  , FullName
  , ADDRESS1
  , ADDRESS2
  , ADDRESS3
  , COUNTRY
  from `people`
  where IsMember = 1
) as tempvar
order by length(code)
; 

but that also fails when I try to add in the Group By somewhere.

When I try

SELECT id
, code
, FullName
, ADDRESS1
, ADDRESS2
, ADDRESS3
, COUNTRY
from (
  SELECT id
  , code
  , FullName
  , ADDRESS1
  , ADDRESS2
  , ADDRESS3
  , COUNTRY
  from `people`
  where IsMember = 1
  order by length(code)
) as tempvar
group by ADDRESS1
; 

then the ordering is lost, and the group comes up with a random member in the family again.

Can someone help me out here?

-Edited to add examples-

You can try this code and here I have used "order by" clause

SELECT ID, code, FullName, ADDRESS1, ADDRESS2, ADDRESS3, COUNTRY
FROM (
  SELECT ID, code, FullName, ADDRESS1, ADDRESS2, ADDRESS3, COUNTRY,
         ROW_NUMBER() OVER (PARTITION BY ADDRESS1 ORDER BY LENGTH(code)) AS rn
  FROM people
  WHERE IsMember = 1
) AS tempvar
WHERE rn = 1;

I hope this could help you!!

The code below will present the code of minimum length for the address. I use the full address instead of ADDRESS1 because if you have people at these two addresses...

Main 10
1234 AB
New York
USA

Main 10
5678 CD
Chicago
USA

...you probably don't want all of the mail going to the first address.

This solution is written for SQL Server. Your mileage may vary.

with people (
  ID
  , code
  , FullName
  , ADDRESS1
  , ADDRESS2
  , ADDRESS3
  , COUNTRY 
  , IsMember
) as (
  select *
  from (
    values 
      (1 , 'A1'  , 'Alex'  , 'Main 10'  , '1234 AB', 'New York' , 'USA', 1)
    , (2 , 'A1.2', 'Fred'  , 'Longstr 1', '5757 GE', 'London'   , 'UK' , 1)
    , (3 , 'A1a' , 'Alexa' , 'Main 10'  , '1234 AB', 'New York' , 'USA', 1)
    , (4 , 'J2'  , 'John'  , '2nd Str 7', '1970 BV', 'The Hague', 'NL' , 1)
    , (5 , 'J2a' , 'Janet' , '2nd Str 7', '1970 BV', 'The Hague', 'NL' , 1)
    , (6 , 'J2.1', 'Alfred', '2nd Str 7', '1970 BV', 'The Hague', 'NL' , 1)
    , (7 , 'J2.2', 'Eric'  , '2nd Str 7', '1970 BV', 'The Hague', 'NL' , 0)
    , (8 , 'J3'  , 'Donald', 'ObraStr 8', '8582 UT', 'Toronto'  , 'CA' , 0)
  ) q (
      ID
    , code
    , FullName
    , ADDRESS1
    , ADDRESS2
    , ADDRESS3
    , COUNTRY 
    , IsMember
  )
),
a as (
  Select ID
  , code
  , FullName
  , ADDRESS1
  , ADDRESS2
  , ADDRESS3
  , COUNTRY
  , len(code) as codelen
  , min(len(code)) over (partition by ADDRESS1, ADDRESS2, ADDRESS3, COUNTRY) as mincodelen
  from people
  Where IsMember = 1
)

select *
from a
where codelen = mincodelen
order by ID
;