2
votes
1answer
51 views

Find the second highest value from a subquery or derived table

I have a subquery which returns the following results. 'c003', '120000.00' 'c002', '90000.00' 'c001', '20000.00' 'c005', '8000.00' 'c004', '5000.00' I want to find the second highest value from ...
0
votes
1answer
70 views

How to find the same salaries and department names?

Write a query to display last name ,department number,and salary of any employee whose department number and salary both match the department number and salary of any employee using the following ...
0
votes
1answer
39 views

How do I solve this problem of a correlated subquery

We have an employee table with the following attributes : employee_id first_name last_name email phone_number hire_date job_id salary commission_pct manager_id department_id Display the manager ...
0
votes
1answer
50 views

Can this where in subquery be optimized?

Currently the query takes ~ 0.2 seconds. Is this the best it gets? Because it feels rather slow for the amount of data I have in my database. I've tried creating an inner join out of it but I can't ...
0
votes
2answers
61 views

How do I add an alias to the table

SELECT MAX(COUNT), A.id FROM ( SELECT supplier_id as id, COUNT(*) AS count FROM `library`.`lms_book_details` GROUP BY supplier_id ) GROUP BY A.id; While executing the query, MySQL gives ...
0
votes
2answers
87 views

MAX from Subquery [closed]

How do I find the maximum value of a particular column from the result of a SubQuery? For example, if the result of the subquery is: 10 20 30 How do I get maximum from this, in this case 30?
0
votes
0answers
44 views

Alternatives for subquery

There are three tables: product_hits, products, and product_subcategory_map. I want to count the number of product_hits and for each product. There could be more than one subcategory in the ...
0
votes
0answers
101 views

MySQL Pre-Filter Subquery

Good Evening All, I'm trying to pre-filter a table such that the more complex MATCH ... AGAINST query doesn't need to process every row in the DB. I tried obtaining a list of identifiers (query 3) ...
1
vote
1answer
60 views

Counting number of visitors who did NOT log in

I'm trying to calculate the number of visitors to the homepage of my site who did NOT then log in to the member's section of the site, while also filtering bots and admin users. Bots and admins are ...
1
vote
1answer
139 views

MySQL Subquery returns more than one row

I have a table namely tbl_plays. It has the following columns and data id(PK)| gameid | drawid | etc ---------------------------------------------- 1 | 1509 | 73 | ...
0
votes
0answers
35 views

Alternative to TEMPORARY tables capable of re-opening in a query?

I use TEMPORARY table for calculation of temporary data, but TEMPORARY tables in mysql has a big limitation, which made it useless for me. A table cannot be re-opened in a query. This mean I cannot ...
1
vote
1answer
116 views

UPDATE a column by COUNT of another table

It might be a naive question, but what is the difference of these two queries and which is preferred? UPDATE table1, (SELECT id,COUNT(*) idcount FROM table2 GROUP BY id) AS B SET table1.Freq = ...
1
vote
1answer
109 views

How to get MySQL to iterate a subquery in an aggregate select?

Because the vocabulary describing what I'd like to do can be so disparate, I haven't had any luck figuring out why MySQL 5.6 is doing what it does. Consider the following two simple queries: #1 ...
1
vote
1answer
106 views

Using OFFSET in a large table by a simple sub-query

The slow performance for using OFFSET in large table has been widely discussed in various blogs, and the most efficient way is to use an INNER JOIN as SELECT * FROM table INNER JOIN ( SELECT id FROM ...
1
vote
1answer
49 views

How to UPDATE a column by COUNT of FK from another table?

How to extend this query for the entire table, to update all ids? UPDATE table1 SET number=( SELECT COUNT(*) FROM table2 where id=1 ) WHERE id=1 id is the PRIMARY KEY in table1 and FK in table2. ...
0
votes
1answer
68 views

Combine results from two queries?

I need to combine the results from two queries into one result set and order them altogether. This is the working, raw SQL code I have: SELECT * FROM ( SELECT t1.id AS entity_id, ...
0
votes
1answer
119 views

Optimizing multiple MySQL sub-queries and joins for multiple runs

The query (below) that's running is taking ~0.6 sec which normally would be fine but it gets run almost 1300 times for different custids. How can I make it run faster? Setup: Mac OS X 10.7.5 MySQL ...
0
votes
1answer
148 views

Find nearest values from MySQL Table

I want to fetch nearest rates for a given value from below table. mysql> select * from rates; +------+----------+----------+ | ID | Type | Rate | +------+---------------------+ | 1 | ...
1
vote
1answer
793 views

Mysql: update query with subquery

Hello i need to perform the following query Query update Taxonomy set sourceId = ( select id from TaxonomyMapping a where a.oldId = ( select cm.en_ID from TaxonomyMapping ta ...
0
votes
1answer
72 views

Need some advice creating this complex view

I need to create a view or query in MySQL to obtain if product has one or more product_detail. This are the create sentences for tables: CREATE TABLE IF NOT EXISTS `stock` ( `product` INT NOT NULL, ...
0
votes
1answer
195 views

How to update a table by count from another table?

I have two tables as CREATE TABLE country_statistics ( id int(11) NOT NULL AUTO_INCREMENT us int(11), uk int(11), ca int(11), PRIMARY KEY(id) ) ENGINE=InnoDB DEFAULT CREATE TABLE entries ( id ...
1
vote
1answer
189 views

Subselect dependent on first select value

I wish to do a select of all data matching a particular id. I would also like to return a selection of all data matching the alt_id field in the first select. This example works fine for the main ...
0
votes
2answers
71 views

Query keeps on executing

I'm executing below query, but it keeps on executing. In place of some time I have a time value. select distinct(col1) from table1 where col2 > 'some time' and col1 not in (select ...
-1
votes
1answer
1k views

Delete from table where multiple fields match select subquery from other table

I want to delete an entry in a table where multiple fields match the results of another select subquery which takes data from another table. This is what I have so far, though it doesn't work: ...
1
vote
3answers
1k views

How do I use subquery on the same table in MySQL?

I have a query like this which takes a really long time to run. The table is around 4 million rows. DELETE FROM TABLE WHERE value_was IS NULL OR value_was <= value_now; I'm hoping I could ...
0
votes
2answers
557 views

How to use and optimize subquery on 100 million rows

What I'm trying to do is running a job on more than 100 million domains which haven't processed before. I have two tables, "domain" and "domain_setting", on every batch (10.000 domains per batch) I'm ...
2
votes
1answer
472 views

Best practice: Unions or a derived table?

I've inherited a medium-sized database with a terrible schema. The sanitized portion in question is like so: CREATE TABLE `pending` ( ... `invoice` int(11) DEFAULT NULL, `lid` int(11) DEFAULT ...
0
votes
3answers
140 views

Not getting the expected result with 'IN' and subquery

I am trying to access the ring_to_number from tbl_destinations that holds all the numbers. And field destinations has the comma separated ID like 1,3. but according to this query i just get the the ...
1
vote
1answer
45 views

Identical subquery optimisation in an update

update activitybooking set `submitted`='1' where id='958' and (select SUM(pool1_count) from (select pool1_count from `activitybooking` where `abt`='12' and (id='958' or ...
1
vote
1answer
431 views

MySQL subqueries that use range based on values of main queries don't use indices properly

I think I've isolated a problem that has been affecting many of my queries lately. And would like some help to figure out a solution for this. Ok so my findings are that a normal query that runs very ...
2
votes
1answer
491 views

Selecting minimum value using a subquery

I need to write a query to grab the lowest price from each merchant and output the price link (p_link) and some other information such as merchant name + rating. We have a table for prices ...
1
vote
1answer
73 views

Why is this query so slow?

I have a quiz site with approx 2000 questions. The function in question worked great when I wasn't tracking that many users, but now there are 300,000 question that users have seen. The function only ...
7
votes
2answers
3k views

SELECTing multiple columns through a subquery

I am trying to SELECT 2 columns from the subquery in the following query, but unable to do so. Tried creating alias table, but still couldn't get them. SELECT DISTINCT petid, userid, (SELECT ...
2
votes
1answer
907 views

Select multiple columns in subquery

I am having trouble selecting all cloumns in a sub query and can't seem to get no where with joining for replacement. http://sqlfiddle.com/#!2/2481e/1 The link is to the schema with my current ...
2
votes
1answer
230 views

Does MySQL have problems with nested insert like with subqueries in where?

Query 1: INSERT `personal`.`locations` SELECT DISTINCT `s`.* FROM `references` `t` JOIN `locations` `s` ON `first_id` = `s`.`id` WHERE `lat` >= 37.3 AND `lat` <= ...
3
votes
1answer
2k views

Subqueries run very fast individually, but when joined are very slow

ypercube solved the problem. Subqueries were totally unnecessary, and the whole thing works with plain joins. It is still strange that MySQL's optimizer could not make use of my original query, ...
2
votes
2answers
141 views

Query taking too much time

My query is taking too much time to execute. Please help me here. mysql> explain select pcm.catalog_id from cat_produ_catal_map_defer pcm, cat_catal_catal_map_defer ccm, cat_catal_defer c ...
1
vote
1answer
249 views

How to re-arrange rows to best fit within groups?

I categorized a table with the method introduced here to categorize my items to folders with a limit size of 100. mixing_order id num_items cat order_in_cat cumulative_sum folder 1 ...
1
vote
1answer
321 views

Getting max values from MySQL tables

I have three tables: competitions (id, name, date) athletes (id,name) results (place, id_athlete, id_competition, ranking_points) where: results.id_athlet=athlet.id ...
1
vote
1answer
165 views

GROUP_CONCAT with ORDER BY , but results are not ordered

The following query works fine SELECT *, (SELECT GROUP_CONCAT(url SEPARATOR '$$' ) FROM project_photos WHERE project_id = projects.id ORDER BY priority) AS images FROM projects WHERE catID = 2 LIMIT ...
1
vote
1answer
51 views

Query from database

I have a table named mip. The table has primary key id, a foreign key product_id and a column usage_type. product_id is a primary key id to a table named product. It is one to many relationship from ...
3
votes
1answer
2k views

SELECT LIMIT 1 per column value?

Lets say I have the following table ----------------------------- | user_id | comment | ----------------------------- | 2 | thats cool | | 2 | awesome | | 3 | ...
3
votes
1answer
4k views

MySQL: Optimize UNION with “ORDER BY” in inner queries

I just set up a logging system which consists of multiple tables with the same layout. There is one table for each data source. For the log viewer, I want to UNION all the log tables, filter them ...
3
votes
2answers
214 views

Do MySQL sub-queries essentially use as much overhead as separate queries?

If I have a query like this: SELECT name FROM ( SELECT * FROM `table` WHERE id IN (1,40,300) ORDER BY AnotherColumn ) From a bandwidth and overhead standpoint, is that the same thing as doing ...
5
votes
1answer
666 views

Mysql sub query returns more than 1 row

SELECT * FROM wp_posts WHERE ID IN ( (SELECT courses FROM wp_category WHERE CatID =401) OR (SELECT meta_value FROM wp_postmeta WHERE post_id IN (SELECT courses FROM wp_category WHERE CatID =401) ...
3
votes
2answers
2k views

How to GROUP_CONCAT DISTINCT values in a MySQL query that gets number of records and min/max values?

I'm running MySQL 5.0.88 (Coldfusion8) I have a product search which I'm querying number-of-results as well as min/max prices/rebates across the product table. I also want to include a string of ...
4
votes
2answers
905 views

Is there a way to hint to query optimizer to MySQL which constraints should be done first?

This is my current query: SELECT BusinessID as ID, 111151.29341326*SQRT(pow(-6.186751-X(LatLong),2)+pow(106.772835-Y(LatLong),2)*0.98838574205337) AS Distance from ( SELECT * FROM ...
2
votes
1answer
73 views

Merge queries into one

The following 2 queries are working as expected and they show the count. Is it possible to merge them into single query? I need sum_hits columns to be part of the rest of the column shown in the ...
1
vote
2answers
3k views

MySQL: Delete all but last N records

Consider the following table: mysql> DESCRIBE pixels; +---------------+-------------+------+-----+-------------------+----------------+ | Field | Type | Null | Key | Default ...
3
votes
3answers
7k views

How to select/count rows where the same value exists in a column but not in other column all at the same table

I have this table structure and data: CREATE TABLE IF NOT EXISTS `default_relations_users` ( `id_user_rq` int(11) NOT NULL, `id_user_ap` int(11) NOT NULL, UNIQUE KEY `rusers_rq_ap_idx` ...