I am using this query to pull out article entries from my database:

$sql = "SELECT * 
        FROM articles 
        WHERE author LIKE ? AND title LIKE ? AND year LIKE ? 
        ORDER BY year DESC";

With single year specifications, the query orders the entries by year as intended:

1999
1998
1979
1969
...

Problematic is that this older database uses several publishing years in the same table cell, looking like this:

Example: 1970, 1974, 1983 or sometimes with semicolons 1970; 1974; 1983

This makes the script order the entries only using the very first year specification (1970 in the above example). Now I am looking for a way to order the entries by the most recent year specification (1983 in the above example).

So the random entries

1. 1966, 1977, 1989
2. 1925; 1956; 1977
3. 1933, 1947, 1975, 1999

should be ordered like this:

1. 1933, 1947, 1975, 1999
2. 1966, 1977, 1989
3. 1925; 1956; 1977

(only the most recent years 1999, 1989, 1977 are relevant for the order of the entries)

CodePudding user response：

Assuming the year column has at least one year, you can use

ORDER BY RIGHT(year,4) DESC