How to match maximum character

karthik_192me · #1 (**permalink**) 05-17-11, 12:35

hi
I m new to this group,new bee to sql,i need help.my DB is MDL like

digit,name
937,RAM
9379,GURU
93,HARI

sql query i tried is
SELECT name,digit from MDL where LEFT('93799321205',length(digit))=digit;
i need to match maximum character, i shd get output as "9379,GURU"..pl help

r937 · #2 (**permalink**) 05-17-11, 14:18

SELECT name,digit from MDL where LEFT('93799321205',length(digit))=digit
ORDER BY length(digit) DESC LIMIT 1

note: does not include ties

karthik_192me · #3 (**permalink**) 05-19-11, 11:03

Thank u very much..it working perfectly...

one more issue....my DB is

country| id |Plan
DE | XX1 | P
US | XX2 | W
US | XX3 | P
DE | XX4 | P

my output shd like

Plan_|_DE_|_US
W | 0 | 2
P | 2 | 1

i m using DECODE option, not getting exact output...Pl help me

SELECT plan,count(DECODE(country,'DE',id)),count(DECODE(c ountry,'US',id))
ORDER BY Plan;