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