LibreOffice Calc spread sheet: Find highest house number, for each street and show those rows only

I use Lubuntu Linux and LibreOffice Calc spread sheet software.

I have a large list of addresses, for a whole town, as a LibreOffice Calc Spread sheet. The rows typically look like this

..
15 Mike Street
17 Mike Street
5 Busy Street Drive
13 Busy Street Drive
2 Busy Street Drive
4 Busy Street Drive
6 Busy Street Drive
25 Morray Street
27A Morray Street
27B Morray Street
29 Morray Street
31 Morray Street
33 Morray Street
60 Morray Street
62 Morray Street
64 Morray Street
110 Green Road
112 Green Road
116 Green Road
118 Green Road
120 Green Road
122 Green Road
..

Each row above occupies a single cell (ie the house/ apartment number are not split into different cells).

For a demographic survey I need to find out how many houses / apartments there are in each street. My approximate method will be to assess numbers in each street by identifying the highest house number in each street. So I want a script, or macro, that will sort the above list to look like this

..
17 Mike Street
13 Busy Street Drive
64 Morray Street
122 Green Road
..

I have 8,000 or so rows in the spreadsheet so I have to automate this.

I use LibreOffice Calc and do not have access to excel. (If need be I could set-up/ create a mySQL database for an appropriate SQL query, though it is a long time since I have done such a thing and I am not sure I have time available to re-learn that set-up process).

Does someone know how I could automate this in LibreOffice Calc.

Bash scripting csv files, might work, though I have no idea how.

Thank you.

Solution

I can't tell you how to automate this in LOC, but if you were to do this in MySQL, then something like the following could work...

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(address VARCHAR(30) NOT NULL PRIMARY KEY
);

INSERT INTO my_table VALUES
('15 Mike Street'),
('17 Mike Street'),
('5 Busy Street Drive'),
('13 Busy Street Drive'),
('2 Busy Street Drive'),
('4 Busy Street Drive'),
('6 Busy Street Drive'),
('25 Morray Street'),
('27 Morray Street'),
('29 Morray Street'),
('31 Morray Street'),
('33 Morray Street'),
('60 Morray Street'),
('62 Morray Street'),
('64 Morray Street'),
('110 Green Road'),
('112 Green Road'),
('116 Green Road'),
('118 Green Road'),
('120 Green Road'),
('122 Green Road');

SELECT MAX(SUBSTRING_INDEX(address,' ',1)+0) max_no 
     , SUBSTRING(address,LOCATE(' ',address)) street
  FROM my_table
 GROUP 
    BY street;

+--------+--------------------+
| max_no | street             |
+--------+--------------------+
|     13 |  Busy Street Drive |
|    122 |  Green Road        |
|     17 |  Mike Street       |
|     64 |  Morray Street     |
+--------+--------------------+