aish11
December 20, 2011, 5:36am
1
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
Klashxx
December 20, 2011, 5:46am
2
Tune your sql statement to get the max date
h112
December 20, 2011, 5:52am
3
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.
aish11
December 20, 2011, 6:01am
4
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).
h112
December 20, 2011, 6:11am
6
issue the following command in MySQL prompt
and post here again
mysql> use your_databse_name;
mysql>desc indata ;
aish11
December 20, 2011, 6:25am
7
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 ----------
radoulov:
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).
thanks radoulov but can i get any shorcut code??
Unfortunately no
But ask yourself - do you really need shorter code?
I believe that what you need is sufficiently efficient code.
aish11
December 20, 2011, 7:20am
9
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.
aish11
December 20, 2011, 7:37am
11
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.