Select MAX prob

[Meltdown]

Hi everyone,

I have a column called PropertyID' and it stores 4 different types pf ID's, related to the type of property

All Apartments start with APT..then a number...200(incremented)...making a property id of APT200. APT201 and so on

All Houses start with HSE...then a number...200, (incremented)...making a property id of HSE200. HSE201 and so on.

What I want to get is the MAX ID for each of the different property types within the propertyid column.

So the query would return
House MAX ID = HSE334
Apartment MAX ID = APT267....and so on for other property types.

Any help appreciated

[nemonoman]

Here's a tidbit from data geeks:

NEVER BUILD INTELLIGENCE INTO YOUR DATA!!

Cute ID schemes like APT201 or HSE234 are what is being described. You've loaded intelligence into the data instead of capturing different data elements in a database.

Rather than

Building

ID: APT123

USE

BUILDING

Type: APT
Number: 123

You can concat these fields easily for printing and display purposes

So instead of a nice simple query like

SELECT Type, MAX(number) FROM building GROUP BY type

You're stuck doing:

SELECT concat(substring(id,0,3), max(substring(id, 4))))
FROM building
GROUP BY substring(id,0,3)

[Meltdown]

Thanks for the reply nemonoman, and the tips, I'll implement that immediately.

Meltdown