I’m wondering why does the following queries give me a slightly different dataset: So I just moved BETWEEN clause to the main body, logically, it does not matter where to apply it, but the result says it matters. Any suggestions? Thanks! UPD: tried on MySQL 5.6 Answer Maybe it’s hard to answer this question without some images! but I try.
Tag: mysql-5.6
Complex mysql query counting
I have 1 table name “companies” with several datas like : Id Owner Company Job 1 John Doe Company 1 CEO 1 John Doe Company 2 CEO 1 John Doe Company 3 CEO 1 Gab Durand Company 4 CEO 1 Rob Dujn Company 5 CTO 1 Alex DoeMorse Company 6 COO What I need is to get 1 line by
How to incrementally count the number of repeated instances
I want to incrementally count the number of repeated instances in a table, so if I have a table like this: Using the column “name” as reference, the output would be The DBMS implementation is MySQL, version 5.6 Answer In MySQL 5.x, where window functions are not available, one option uses a correlated subquery: You could also do this with
How do I derive a value for a column based on another column’s value?
I have a projects table like the one below. There are two slightly different id columns idx and idy–one idx can have multiple idys (for versioning). The goal is to consolidate the usage such that for every idx, the result will have: a use_count col which is SUM(use_count) for same idx the latest usage_date the name corresponding to the latest
inner join with subquery results differs for these data sets
CREATE TABLE IF NOT EXISTS `wcd` ( `id` int(6) unsigned NOT NULL, `wid` int(11) unsigned NOT NULL, PRIMARY KEY (`id`) ) DEFAULT CHARSET=utf8; INSERT INTO `wcd` (`id`, `wid`) VALUES (‘168’, ‘…
MySQL: Counting active products that belong to active categories (and optionally to active subcategories)
i’m using MySQL 5.6. I need to get a list of categories, with a product count column. So far, a very easy and trivial task. Here’s where it gets a little complicated: A product has a category, and can also have a subcategory (optionally). Categories, subcategories and products can be active (flg_public=1) or disabled (flg_public=NULL). So, the query should return
MySQL: Is it possible to set a variable outside of SELECT with SELECT as the only permission?
WHAT I’m on MySQL 5.6 and my account only has permissions for SELECT and USAGE (as found using the SHOW GRANTS command). Presumably because of this using DECLARE before SELECT returns a generic …