Visit Our UNIX and Linux User Community


mysql query in shellscript


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting mysql query in shellscript
# 1  
Old 12-20-2011
mysql query in shellscript

Hi,

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

Code:
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

Code:
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

Code:
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

Last edited by aish11; 12-20-2011 at 10:16 AM..
# 2  
Old 12-20-2011
Tune your sql statement to get the max date
# 3  
Old 12-20-2011
Code:
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.
# 4  
Old 12-20-2011
Tried both the code..

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
# 5  
Old 12-20-2011
You could use either:

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

Or:

Code:
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).
# 6  
Old 12-20-2011
issue the following command in MySQL prompt
and post here again
Code:
mysql> use your_databse_name;
mysql>desc  indata ;

# 7  
Old 12-20-2011
Code:
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 ----------

Quote:
Originally Posted by radoulov
You could use either:

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

Or:

Code:
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).

thanks radoulov but can i get any shorcut code??

Previous Thread | Next Thread

8 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Need wrapper around mysql query

Hello Friends, I am using check_mysql_query plugin in nagios to query database and get output. query is working fine but output which i am getting contain query. I want to remove query from output and give custom message which will be simple and understandable. Can you help me here with any... (5 Replies)
Discussion started by: ghpradeep
5 Replies

2. Programming

Need help in mysql query

Hi All, i have a table in mysql with the following data Table name Test Assettype Serial_No Status location Mouse 123456 In Stock chennai Mouse 98765 Allocated chennai Keyboard ... (2 Replies)
Discussion started by: venkitesh
2 Replies

3. Programming

mysql query help

Hello i have created mysql query to compare to values and get difference in percentage as following: SELECT file_name, 100 - ((100 * (SELECT file_count FROM xipi_files z WHERE x.file_group = z.file_group AND x.file_name = z.file_name AND z.insert_date = CURDATE( ) - INTERVAL 1 DAY)) /... (1 Reply)
Discussion started by: mogabr
1 Replies

4. Shell Programming and Scripting

mysql query in shellscript

Hi, I want to access mysql query from database , for that i have tried the below code #! /bin/bash TABLE_NAME=database1 USER_NAME=root IP_ADDR=111.20.9.256 somevar=`echo "select altid from alert where altid='2724'"| mysql -h $IP_ADDR -u $USER_NAME $TABLE_NAME ` echo $somevar ... (1 Reply)
Discussion started by: aish11
1 Replies

5. Web Development

mysql query help

hello all i have 2 columns every column in the following format column1 2011-04-01 11:39:54 column2 2019-02-03 00:00:00 i want get difference between above data as following 2 days 11:39 how to do so ? i tried many functions but nothing works please advice what is the query... (6 Replies)
Discussion started by: mogabr
6 Replies

6. Shell Programming and Scripting

? parameter in mysql query

I am debugging some code and came across ? in the WHERE cause in a mysql query. Is this possible and what situations would this be used? SELECT ip, count FROM table WHERE domain=? thanks & regards (2 Replies)
Discussion started by: hazno
2 Replies

7. Shell Programming and Scripting

shellscript.query Oracle table..populate in a text file

Hi Guys, I'm new to this forum as well as to UNIX shell scripting. I'm looking for a shellscript to query an Oracle database table and populate the result set of the query in a text file. Could you someone help me out with a sample code? Thanks, Bhagat (7 Replies)
Discussion started by: bhagat.singh-j
7 Replies

8. Programming

How to query one to many mysql

Hi there, I have a hierarchical database that include 4 tables. Table A is the parent of B, B is Parent of C, C is parent of D. If I want to query everything in D that is associated with A.name, how do I do that? Thanks! YanYan (0 Replies)
Discussion started by: pinkgladiator
0 Replies

Featured Tech Videos