All Questions
30 questions
0
votes
1
answer
103
views
The most recent rows in for each type/agent
I have a table (still on MySQL 5.7) with lots of data. This table is written by some scripts. It works like a "checkin", where each agent is checking in in some period of time. It looks like ...
- 13
0
votes
1
answer
45
views
Select distinct
I had some confusion regarding distinct keyword...
For example Table test has 5 columns a b c d e, and column a has repeated value, and I want to use distinct keyword to get distinct values while also ...
0
votes
1
answer
111
views
Fetching 88 records via DISTINCT from a table of 1.5 million rows is taking 3 seconds
There is a table which are populated from an online service and updated every week.
This goes on a separate (second) database.
The table is like 300MB in size (1.5 million rows) processed by a cron ...
- 279
0
votes
1
answer
31
views
Fetch distinct / different from previos row Data from MySQL Table
I have a simple Table. It contains an ID, a Temperature and a Date. Every 10 Seconds a Sensor writes a Row with the measured Temperature-Value.
What I need is the Data, where the temperature changed. ...
- 101
0
votes
1
answer
282
views
How to structure an index to support counting distinct values of one column and grouping by several others?
I have a single reporting table of sales data with about 4 million rows of data:
CREATE TABLE reporting_sales (
customer_id bigint(20) DEFAULT NULL,
effective_date date DEFAULT NULL,
...
- 103
0
votes
1
answer
35
views
Can't get this GROUP BY or DISTINCT right
On MySQL 5.7.34 I have this simple db structure. Important columns are domain name, domain total links in, but entries are duplicate (once for each found link from various sources), I need to select ...
- 204
0
votes
1
answer
175
views
List out distinct attributes in one column for distinct IDs [duplicate]
right now I am getting duplicates on an ID because it has unique attributes. Example and query below
Current:
Select distinct ID, Company
from Order
ID
Company
1100
Fisher
1100
Nesses
1101
Denny's
...
- 1
1
vote
4
answers
5k
views
Select the rows only when all sub columns contain the same value [duplicate]
Suppose I have 3 columns, Item, SubItem, Number.
I want to select the rows only when all the subitems have number as 0.
For example in the below table, only Phone and Pant should be selected since all ...
- 11
1
vote
1
answer
737
views
Optimizing a distinct on a parent table in a multi-level join in MySQL 5
I have the following tables: in a MySQL 5 database:
projects - 1,000 rows
tasks (FKed to projects) - 10,000 rows
task_tags (FKed to tasks) - 350,000 rows
task_comments (FKed to tasks) - 750,000 rows
...
- 113
1
vote
2
answers
268
views
select rows on distinct column value without vendor-specific sql syntax
I'm trying to select distinct rows without any vendor specific syntax. My table layout is as follows:
+--------------------------------------+---------+--------------------------+----------------------...
- 113
7
votes
2
answers
21k
views
MySQL: Using DISTINCT and GROUP BY together?
I saw the following MySQL query that that uses both DISTINCT and GROUP BY together:
SELECT DISTINCT user_id, post_id, post_content
FROM some_table
GROUP BY post_id, user_id
HAVING ...
0
votes
1
answer
43
views
Filter the query result
I have one query by which I am getting duplicate ComapnyID and I want to filter that and remove duplicate ComapnyID but don't want change the order by report.
table 1 reports
Table 2 Company
Here ...
- 3
0
votes
1
answer
30
views
distinct description
I have the following database:
id | remote_id | title | description
-------------------------------------
1 | 12341234 | test | test-description
2 | 4535234 | blah | testestetetst
3 | ...
0
votes
1
answer
43
views
Why this join provide multiple value?
This is my Query:
SELECT GROUP_CONCAT(c.categoryName) AS categories
FROM post p
INNER JOIN join_post_cat jpc USING (postId)
INNER JOIN categories c USING (categoryId)
WHERE p.postId ...
- 101
1
vote
2
answers
2k
views
merge 3 mysql tables into one with no duplicates
I would like to merge 3 tables data, each have a number column in common,
but all the other data differs.
What I would like to achieve is selecting all of the data from the common column
and merging ...
- 11
2
votes
1
answer
15k
views
Select unique value pairs that occur (count) more than once inside a table
How can I retrieve an row with unique value pairs that occur more than once...
Heres an example of what I'm what I am trying to..
SELECT
column_fk_id1, column_fk_id2,
COUNT(distinct column_fk_id1, ...
- 23
3
votes
1
answer
1k
views
inconsistent distinct + order by behavior between mysql 5.6 and 5.7
I use MySQL 5.6 and 5.7 on Ubuntu 16.04.3 LTS and I have a problem with the following query (which is derived from drupal 7 but have simplified it in order to pinpoint the issue):
select distinct ...
- 171
1
vote
1
answer
54
views
Implementing a query, two types of strategies
Tables have not been created yet, but to simplify there is a groups and an items tables. A group contains an item id (and a group id). And several groups may contain the same item id.
For instance
...
- 513
0
votes
1
answer
58
views
MySQL Select all clients that searched for two different items or more
I have a table like this :
serial,ip,term
1,1.2.3.4,bag
2,2.3.4.5,shampoo
3,1.2.3.4,handbag
4,3.4.5.6,clothes
5,2.3.4.5,battery
6,4.5.6.7,lamp
I want to select all IPs that did search for multiple ...
0
votes
2
answers
5k
views
Why is a 'Select distinct limit 100' using tmp tables? [closed]
I hope someone can teach me why MySQL is using temporary tables on a super simple select with a limit of 100 and how to get it do query more efficiently. I'm running MySQL 5.1.53-community.
The ...
- 133
5
votes
1
answer
2k
views
Tolerating SQL errors (orderby not in distinct, select not in group by) in MySQL 5.7.13 vs 5.7.11
This question is related to the following 2 SQL errors (line breaks added by me)
MySql.Data.MySqlClient.MySqlException (0x80004005):
Expression #2 of ORDER BY clause is not in SELECT list, references ...
0
votes
1
answer
227
views
mysql selecting a distinct summed column while having
I have a one-to-many relationship between two tables, where I need to get distinct values out of a summed column b while limited by another summed field a
SELECT
t1.a - sum(t2.a) as a,
t1.b - ...
1
vote
2
answers
3k
views
Which row is selected by DISTINCT?
I have a table with products with names in different languages:
product-id | lang-no | name
Now I want to generate product lists with names in each language. Unfortunately I don't have all names in ...
- 172
1
vote
1
answer
24k
views
2 GROUP BY WITH DISTINCT , SUM , COUNT : PHP MYQL
Ref : https://stackoverflow.com/questions/32066097/group-by-with-having-distinct-php-mysql
I got answer (thanks coytech ) but i need one more column in its :
as per :
id | mid | pid | owgh | nwgh |
...
- 123
7
votes
2
answers
42k
views
Distinct Combination of Two Columns
I am currently trying to find distinct combinations within a table as two of the columns have a many to many relationship with each other.
The data is all around backup policies being run on against ...
- 73
1
vote
2
answers
1k
views
Get the nth distinct column value ordered by another column
I want to get the row with the nth distinct value in a column, as ordered by another column.
In more real terms, I want the 1000th distinct email_address as ordered by created_at, and the ...
- 125
1
vote
4
answers
2k
views
Grouping Data Using Joined Tables with Certain Distinct Values
I am struggling to get my head around how to do this so please bare with me while I explain.
We have an order system which records orders, order lines, items, users etc.
What I am interested in in ...
- 11
0
votes
2
answers
733
views
MYSQL Select Distinct with SUM
I am developing a Stocks management application, the Portfolio register table has 4 columns, other than date and registry_key, and it looks like this:
----------------------------------------
| ...
- 111
1
vote
4
answers
4k
views
SQL retrieve last elements of table using group by like behaviour
I am trying without success to retrieve the last version of all articles in a table.
The table structure:
CREATE TABLE `journalarticle` (
`uuid_` varchar(75) DEFAULT NULL,
`articleId` varchar(75)...
- 107
2
votes
1
answer
7k
views
Grouping based on the status, then count its product and the total based on its category ID
I have a table with product ID, category ID and status of the product.
+---------+-------------+-------------+
| id | cat_id | status |
+---------+-------------+-------------+
| 1 ...
- 159