select string column with max id

[sukahati]

Hi,

I need help on a select query

Example, i got a column id in table store, and the data is like below

id
--

ABC00123P01
ABC00123P02
ABC00123P03
ABC00123P04
ABC00123P05
ABC00123P06
DEF00456P01
DEF00456P02

I just want to select id which have the max value number after the P. So for the example above..the resulkt i want is

ABC00123P06
DEF00456P02

Anyone can help me on this??
Thanks in advance

[alt]

This would be good information to know, if its possible Within a query. I cannot figure this out either. The MAX technique would only go by the first characters in the field. Only thing I can think of is to have another column for just the last two numbers.

[BrainDonor]

try something like this?

select substring(id,1,9), max(substring(id,10,11))
from store
group by substring(id,1,9)

[sukahati]

thanks braindonor....ur query not working...but u gave me an idea...thank u.

it works when i do like this

SELECT MAX(SUBSTR(id,1,11))
FROM
(SELECT SUBSTR(id,1,9) FROM store
WHERE TO_CHAR(create_date,'dd/mm/yyyy') = '11/10/2007')
GROUP BY SUBSTR(id,1,9)

but then i encounter another problem

if the store table have another column....say description column and i want also to select this column....it doesn't work...it will display all the value....i guess it because of the group by.....can u help me on this??

example

id description
-- -----------
ABC00123P01 postback1
ABC00123P02 postback2
ABC00123P03 postback3
ABC00123P04 postback4
ABC00123P05 postback5
ABC00123P06 postback6
DEF00456P01 sent1
DEF00456P02 sent2

[BrainDonor]

you'll need to group by the description column as well and have the SUBSTR(id,1,9) in your select, even if you don't plan on using it.

[sukahati]

i already group by the description column like below

SELECT MAX(SUBSTR(id,1,11)),description
FROM
(SELECT SUBSTR(id,1,9),description FROM store
WHERE TO_CHAR(create_date,'dd/mm/yyyy') = '11/10/2007')
GROUP BY SUBSTR(id,1,9),description

but the answer is not like what i want...i gave the result like below....

ABC00123P01 postback1
ABC00123P02 postback2
ABC00123P03 postback3
ABC00123P04 postback4
ABC00123P05 postback5
ABC00123P06 postback6
DEF00456P01 sent1
DEF00456P02 sent2

its doesnt select the max id column...the result i want is....

ABC00123P06 postback6
DEF00456P02 sent2

[mdjo]

Including description in the group-by means it will find the max id for any given description, which is probably not what you want.

Side note: substr(id,1,11) looks to me like it would be no different from simply "id" as all your id's look like they're 11 positions.

So I think the solution would be something like:

select id,description
from store
where id in
(select max(id)
from store
group by substr(id,1,9)
)

That is, the inner query finds the highest id within each prefix, then the outer query finds the matching description.