Sorting in access database with multiple columns

Question

Im trying to sort a database in access and I can't get it done. First the city with the lowest price and all the prices of that city, then the city with the second lowest price, etc.

This is the example database:

Country    City     Price    Departure_date     Return_date
Peru       Lima     360$     xxxx               xxxxx
Peru       Lima     420$     xxxx               xxxxx
Mexico     CMX      300$     xxxx               xxxxx
Mexico     CMX      400$     xxxx               xxxxx
Mexico     Cancun   350$     xxxx               xxxxx
Mexico     Cancun   500$     xxxx               xxxxx
Peru       Cusco    50$      xxxx               xxxxx
Peru       Cusco    60$      xxxx               xxxxx

It has to be sorted this way:

Country    City     Price    Departure_date     Return_date
Peru       Cusco    50$      xxxx               xxxxx
Peru       Cusco    60$      xxxx               xxxxx
Mexico     CMX      300$     xxxx               xxxxx
Mexico     CMX      400$     xxxx               xxxxx
Mexico     Cancun   350$     xxxx               xxxxx
Mexico     Cancun   500$     xxxx               xxxxx
Peru       Lima     360$     xxxx               xxxxx
Peru       Lima     420$     xxxx               xxxxx

Second Part:

I need it to only use the rows with latest date.

This is the example database:

Date           Country    City     Price    Departure_date     Return_date
05-06-2019     Peru       Lima     360$     xxxx               xxxxx
05-06-2019     Peru       Lima     420$     xxxx               xxxxx
05-06-2019     Mexico     CMX      300$     xxxx               xxxxx
05-06-2019     Mexico     CMX      400$     xxxx               xxxxx
05-06-2019     Mexico     Cancun   350$     xxxx               xxxxx
05-06-2019     Mexico     Cancun   500$     xxxx               xxxxx
05-06-2019     Peru       Cusco    50$      xxxx               xxxxx
05-06-2019     Peru       Cusco    60$      xxxx               xxxxx
04-06-2017     Mexico     Cancun   300$     xxxx               xxxxx
04-06-2017     Peru       Cusco    70$      xxxx               xxxxx
04-06-2017     Peru       Cusco    30$      xxxx               xxxxx

It has to be sorted this way:

Date         Country    City     Price    Departure_date     Return_date
05-06-2019   Peru       Cusco    50$      xxxx               xxxxx
05-06-2019   Peru       Cusco    60$      xxxx               xxxxx
05-06-2019   Mexico     CMX      300$     xxxx               xxxxx
05-06-2019   Mexico     CMX      400$     xxxx               xxxxx
05-06-2019   Mexico     Cancun   350$     xxxx               xxxxx
05-06-2019   Mexico     Cancun   500$     xxxx               xxxxx
05-06-2019   Peru       Lima     360$     xxxx               xxxxx
05-06-2019   Peru       Lima     420$     xxxx               xxxxx

Answer 1

Try this:

SELECT t1.*
FROM Table1 AS t1 INNER JOIN
     (SELECT Table1.City, Min(Table1.Price) AS min_price
        FROM Table1
        GROUP BY Table1.City) AS t2 
     ON t1.City = t2.City
ORDER BY t2.min_price, t1.City, t1.Price

To accommodate second part, include WHERE clause:

SELECT t1.*
FROM Table1 AS t1 INNER JOIN
     (SELECT Table1.City, Min(Table1.Price) AS min_price
        FROM Table1
        WHERE DateEnter=(SELECT Max(DateEnter) AS MaxDate FROM Table1)
        GROUP BY Table1.City) AS t2 
     ON t1.City = t2.City
WHERE DateEnter=(SELECT Max(DateEnter) AS MaxDate FROM Table1)
ORDER BY t2.min_price, t1.City, t1.Price;