problem with patindex()

[deolalkar_pooja]

Hi to all,
I created following table
---------------------------------------
crate table publisher(name varchar(20), city varchar(20))
---------------------------------------

I want to run following query.
'list all the positions of charcter 'a' from name.'

for this, i ran following query,

----------------------------------------

select patindex('%a%', name)
from publisher;

---------------------------------------

But, it doesn't show proper output. When i put 'city' column instead of 'name' column, it shows right output. What is the problem?

=======================================

Also, I want to dispaly the name of publishers whose getting minimum and maximum profit. for this, i ran following query,

--------------------------------------

select name, max(profit), min(profit) from publisher;

-------------------------------------

It shows the error like that "'name' is not part of aggregate function". what should i do for retrieving name of publishers who getting minimum and maximum profit.

Thanks & regards,
Pooja.

[smantscheff]

patindex() is not a mysql function. You cannot use it in a select clause unless you define the function first.
To select all names like "%a%", use the like operator:

SELECT * FROM publisher where name like "%a%";

To use aggregate functions like sum() or avg() you need an GROUP BY aggregate clause:

select name, max(profit), min(profit) from publisher GROUP BY name;

To find which publisher has the maximum profit, use

SELECT name from publisher order by profit desc limit 1;

[deolalkar_pooja]

hi smantscheff, i will work on ur suggestion abt 1st query.

I tried the same also abt 2nd query, but it shows same error. can u give different solution?

Thanks & regards.
Pooja.