Adding values returned by GREATEST
Hello, i have a question regarding the GREATEST function of mysql. I would like to add the values returned by GREATEST function is mysql, so a query is like below: For example table t has 6 fields with values as follows: A = 1, B = 3, C=0, D = 0, E = 1 and F = 0 and I run a query: SELECT GREATEST (A, B, C) AS G1, GREATEST (D, E, F) AS G2, ( GREATEST (A, B, C) + GREATEST(D, E, F) ) AS Total FROM t The result row I expect is: 3, 1, 4 But I get 3, 1, 6 However when I run the query like below I get correct results as total being 4: SELECT ( GREATEST (1, 3, 0) + GREATEST(0,1,0) ) AS Total So what I noticed is as I add result from GREATEST function, the result is adding 1 for each GREATEST call I have in total. So, if I change my query as below: SELECT GREATEST (A, B, C) AS G1, GREATEST (D, E, F) AS G2, ( GREATEST (A, B, C) + GREATEST(D, E, F) + GREATEST(D, E, F) ) AS Total FROM t The results will be 3, 1, 8 GREATEST (A, B, C) + GREATEST(D, E, F) + GREATEST(D, E, F) is calculated as GREATEST (A, B, C) = 3 + 1 GREATEST(D, E, F) = 1 +1 GREATEST(D, E, F) = 1 +1 So the total is 8. I have tried online to search for this type of behaviour but no luck. Can anyone please explain this. Many Thanks, SK
Re: Performance boost by splitting up large table?
Hi, You could split the table into two and can avoid code changes by creating a view which matches what code is looking for. I think loading few fields vs 254 into memory will make a difference but if your select statement only have specific fields you want and not the whole row (and also given the fact that you have index on the field you are using to search record), I don't think it can make a difference. But I will looking forward for more answers to your question. Regards. On Wed, May 14, 2014 at 8:05 AM, Larry Martell larry.mart...@gmail.comwrote: We have a table with 254 columns in it. 80% of the time, a very small subset of these columns are queried. The other columns are rarely, if ever, queried. (But they could be at any time, so we do need to maintain them.). Would I expect to get a marked performance boost if I split my table up into 2 tables, one with the few frequently queried columns and another with less frequently queried ones? Doing this will require a lot of code changes, so I don't want to go down this path if it won't be beneficial. Can folks here offer their experiences and learned opinions about this? Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Decode Json in MySQL query
Many Thanks for the kind replies.
I have decoded in my code but just wondering in case I missed any solution
to decode via query.
On Thu, Mar 20, 2014 at 3:05 PM, Michael Dykman mdyk...@gmail.com wrote:
Short answer, no. There is nothing in MySQL to facilitate this. In
general, storing structured data as a blob (JSON, CSV, XML-fragment,
etc..) is an anti-pattern in a relational environment. There are
NoSQL solutions that provide the facility: Mongo comes to mind; there
are some others, I am sure.
On Thu, Mar 20, 2014 at 2:59 PM, Karr Abgarian a...@apple.com wrote:
Hi, you probably want to perform this conversion on your client. There
are JSON parser libraries available for Java, PHP and the like. Cheers,
Karr
On Mar 20, 2014, at 11:35 AM, Sukhjinder K. Narula narula...@gmail.com
wrote:
Hello,
I would like to know if there is a way to decode the json string stored
in
one of the fields as text without using triggers or stored procedures.
What I want to do is is within the query, I would like to get one row
per
element within the json string.
For example: the json string is as follow:
[
{
name : Abc,
age : 20
},
{
name : Xyz,
age : 18
}
]
and after query, I want result as:
NameAge
Abc 20
Xyz 18
Would this be possible, I greatly appreciate any help regarding this
matter.
Many Thanks,
Sukhjinder
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
--
- michael dykman
- mdyk...@gmail.com
May the Source be with you.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Decode Json in MySQL query
Hello,
I would like to know if there is a way to decode the json string stored in
one of the fields as text without using triggers or stored procedures.
What I want to do is is within the query, I would like to get one row per
element within the json string.
For example: the json string is as follow:
[
{
name : Abc,
age : 20
},
{
name : Xyz,
age : 18
}
]
and after query, I want result as:
NameAge
Abc 20
Xyz 18
Would this be possible, I greatly appreciate any help regarding this
matter.
Many Thanks,
Sukhjinder
Re: a Java-connector
Its called JDBC connector, please see the link: http://dev.mysql.com/doc/connector-j/en/connector-j-usagenotes-connect-drivermanager.html good luck. On Wed, Dec 11, 2013 at 10:15 AM, h...@tbbs.net wrote: I have MySQL 5.5.8 under Windows Vista, and I am minded to write Java programs to talk to the server. I believe that a connecter is needed for that, something with ODBC in the name--which version is best for my use? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Check for numeric values
Hi, You could use regular expression to do this, here is the example with the reference site that might help you: If your data is 'test', 'test0', 'test', '111test', '111' SELECT * FROM myTable WHERE col1 REGEXP '^[0-9]+$'; Result: '111' In regex ^ mean begin, and $ - end. SELECT * FROM myTable WHERE col1 REGEXP '^[0-9]+\.?[0-9]*$'; - for 123.12 *But,* select all records where number exists: SELECT * FROM myTable WHERE col1 REGEXP '[0-9]+'; Result: 'test0' and 'test' and '111test' and '111' http://stackoverflow.com/questions/5064977/detect-if-value-is-number-in-mysql Regards. On Tue, Oct 8, 2013 at 7:53 AM, Mike Blezien mick...@frontiernet.netwrote: Hello, I need to select some data from a table where a column is not a numerical value but can't seem to get the right syntax for this. Basically we need to do something like this: SELECT * FROM tablename WHERE column_name (IS NOT A NUMERIC VALUE) what is the correct syntax to accomplish this? MySQL version: 5.5 Thank you, Mike(mickalo)Blezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-**=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Custom Programming Web Hosting Services http://www.thunder-rain.com/ -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=**-=-=-=-=-=-=-=-=-=-=- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Problem with having
Hi, In your second query, you seem to have MIN(date_time), but you are talking about maximum. So your group by query is actually pulling the minimum date for this recipe. Regards. On Mon, Sep 23, 2013 at 3:35 PM, Larry Martell larry.mart...@gmail.comwrote: I want to find the rows from a table that have the max date_time for each recipe. I know I've done this before with group by and having, but I can't seem to get it to work now. I get the correct row id, but not the correct date_time. I'm sure I'm missing something simple. For purposes of showing an example, I'll use one recipe, 19166. For that recipe here's the row I would want: mysql select id, MAX(date_time) from data_cstmeta where recipe_id = 19166; +-+-+ | id | MAX(date_time) | +-+-+ | 1151701 | 2013-02-07 18:38:13 | +-+-+ 1 row in set (0.01 sec) I would think this query would give me that - it gives me the correct id, but not the correct date_time: mysql SELECT id, date_time as MaxDateTime FROM data_cstmeta where recipe_id = 19166 group by recipe_id HAVING MIN(date_time); +-+-+ | id | MaxDateTime | +-+-+ | 1151701 | 2010-12-13 16:16:55 | +-+-+ 1 row in set (0.01 sec) How can I fix this? Thanks! -larry
Question regarding creating a query
Hello, I have a question regarding creating a query as follows: I have several databases (all with same structure), which I to query. For instansce: db1, db2, db3 - all have table tb1 with field a, b and table tb2 with fields flag1, flag2 So I want to query and get field a from tb for all db's. One way to do is union i.e. SELECT a FROM db1.tb1 WHERE (SELECT flag1 FROM db1.tb2) = 'y' UNION SELECT a FROM db2.tb1 WHERE (SELECT flag1 FROM db2.tb2) = 'y' UNION SELECT a FROM db3.tb1 WHERE (SELECT flag1 FROM db3.tb2) = 'y' But the problem here is that if I add more db's, I have to update the query every time. In addition to above, I also have a database e.g. common, which has a table called dbnames with field name, that keeps the name of all the databases I have (db1, db2, db3). So, what I would like to do is query the common db to get the names of the db's and then run the select query on each db. So here is the pseudocode of what I want to do: for each (SELECT name AS DbName FROM common.dbnames) (SELECT a FROM DbName.tb1 WHERE (SELECT flag1 FROM DbName.tb2) = 'y') AS CONCAT(DbName, '-', a) Could you please advice if this possible and if yes, how can this be acheived. Many Thanks, SK
