create table Building (
id integer primary key,
name varchar(10) not null,
longname varchar(40) not null,
gridref char(3) -- Grid ref is one letter, followed by up to 2 digits, for example (A1, B11, C20, D9) etc
);
Need to write a query that returns the longname of the building with the highest numeric part of the grid ref. I've written it to use a subquery, but was wondering if there's any way to do it in a single query.
My current query is:
select building.longname FROM building WHERE CAST(substr(building.gridref, 2, 2) as integer) = (SELECT MAX(CAST(substr(building.gridref, 2, 2) as integer)) FROM building)