mysql query in shellscript

Hi,

I want to run below query on shellscript but having one problm.

ADV=$( mysql -h "$IP_ADDR" -u "$USER_NAME"  "$TABLE_NAME" -BNe" SELECT ADV FROM indata where inid='$INSTRUID' and  Date='$latest Date';" )

here Date column contans different below dates

2011-12-01
2011-12-05
2011-12-07
2011-12-08
.
.
2011-12-13

and i want to catch date as latest date i.e 2011-12-13

finally the query looks like

ADV=$( mysql -h "$IP_ADDR" -u "$USER_NAME"  "$TABLE_NAME" -BNe" SELECT ADV FROM indata where inid='$INSTRUID' and  Date='2011-12-13';" )

Also i want to make this script in minimum size code.

please help me to catch latest date.

Thanks

Tune your sql statement to get the max date

ADV=$( mysql -h "$IP_ADDR" -u "$USER_NAME"  "$TABLE_NAME" -BNe" SELECT ADV FROM indata where inid='$INSTRUID' and  max(Date);" )

I think this is the minimalist fragment of code you can write to get what you want.

Tried both the code..

ADV=$( mysql -h "$IP_ADDR" -u "$USER_NAME"  "$TABLE_NAME" -BNe" SELECT ADV FROM indata where inid='$INSTRUID' and  max(Date);" 
 
and
 
 
ADV=$( mysql -h "$IP_ADDR" -u "$USER_NAME"  "$TABLE_NAME" -BNe" SELECT ADV FROM indata where inid='$INSTRUID' and  Date=max(Date);" 
 

getting error -invalid use of group function

can u plz tell me the right command , i ma fresher new to mysql command also

You could use either:

select adv 
from indata
where inid = '$INSTRUID'
and Date = ( 
    select max(Date) 
    from indata
    where inidi = '$INSTRUID'
    limit 1
    )
limit 1;

Or:

select a.adv
from indata a
join (
  select inid, max(Date) Date    
  from indata 
  where inid = '$INSTRUID' 
  group by inid
   ) b
  on a.inid =  b.inid
  and a.Date = b.Date
  where a.inid = '$INSTRUID'
  limit 1
  ;    

I've added the limit clause for safety (not knowing your table structure and data, it's impossible to know if duplicates are possible).

issue the following command in MySQL prompt
and post here again

mysql> use your_databse_name;
mysql>desc  indata ;
mysql> desc indata;
+--------------+---------------------+------+-----+---------+-------+
| Field        | Type                | Null | Key | Default | Extra |
+--------------+---------------------+------+-----+---------+-------+
| MDate   | date                | NO   | PRI | NULL    |       |
| OPrice | double              | NO   |     | 0       |       |
| CPrice | double              | NO   |     | 0       |       |
| Volume | double              | NO   |     | 0       |       |
| ADV          | bigint(20) unsigned | NO   |     | 0       |       |
| InID | int(20) unsigned    | NO   | PRI | NULL    |       |
+--------------+---------------------+------+-----+---------+-------+
 
 

sorry its MDate

---------- Post updated at 06:25 AM ---------- Previous update was at 06:23 AM ----------

thanks radoulov but can i get any shorcut code??

Unfortunately no :slight_smile:

But ask yourself - do you really need shorter code?
I believe that what you need is sufficiently efficient code.

hi radoulov,

i am confused in between the column name Date and might be Date function

can u plz tell me using MDate as a column name.

Tried ur code but getting error

Just substitute all occurrences of Date with MDate.

hi,

tried the first code which u have posted here.

running sccesfully but not showing any output . but according the logic output should display the zero value

and for second code its still giving an error

Unknown column 'b.MDate' in 'on clause'

Please post more data. Post part of the records:

mysql> select * from indata limit 15;

And post the exact command and error message that you're getting.