Tag Info

Hot answers tagged

5

If you don't need the "optimal fill", you could use a window function and something like this: select * from ( select id, name, price, category, sum(price) over (order by price, date_created) as so_far from my_table where price > 40 and category = 'buy' ) t where so_far <= 12; If you are mixing "groups" of orders, you might ...


4

For the specific query: select sum(diff_ms) from writetest_table where time_on > '2015-07-13 15:11:56' ; -- use single quotes, not double an index on (time_on, diff_ms) would be the best option. So, if the query runs often enough or its efficiency is crucial to your application, add this index: ALTER TABLE writetest_table ADD INDEX ...


3

Your UPDATE statement is wrong. I've edited your TRIGGER and the modifications I did and errors I saw: Errors: You tried to drop a different TRIGGER that the one you're going to create. DROP TRIGGER IF EXISTS veevan.account_ai$$. Why do you need the variable @TargetLastMod?. You didn't use it in the whole trigger. Modifications: I edited the DROP ...


3

Instead of issuing those checks as separate SELECT statements, make them part of the INSERT statement. You will need to replace the INSERT...VALUES syntax with the INSERT...SELECT one to be able to use a WHERE clause: INSERT INTO waypoint_checkins (runner_id, waypoint_id, time) SELECT runners.id, %waypoint_id, %time FROM runners WHERE name = ...


2

SELECT DISTINCT ID FROM tbl WHERE DUES = 0 will get the IDs of the naughty people, correct? So now you need to do a "self-JOIN" back to get the rest of the data: SELECT DISTINCT a.* FROM tbl AS a JOIN tbl AS b USING(ID) WHERE b.DUES = 0 ORDER BY a.ID, a.DATE Or... SELECT * FROM tbl AS a WHERE EXISTS ( SELECT ID FROM tbl ...


2

The measured difference is almost certainly noise. Run some more iterations, you won't get consistent result. The difference in performance (if any exists) won't be measurable. You can use either method here. Both are equally good for the purpose. There are often multiple ways in SQL. And sometimes there is no clear winner. The more important issue ...


2

This is a "relational division" problem and there are several ways to solve. One way is with two EXISTS subqueries: SELECT p.id FROM photos AS p WHERE EXISTS ( SELECT 1 FROM photos_tags AS pt WHERE p.id = pt.photo_id AND pt.tag = 'cute' ) AND EXISTS ( SELECT 1 FROM photos_tags AS pt WHERE p.id ...


1

Typing from my phone, you could do something like: select x.* from t as x join ( select id from t group by id having count(distinct email) > 1 ) as y on x.id = y.Id You seem to be missing a primary key, but I assume this exercise is part of fixing that.


1

The easiest way would be group by and having something as select p.id from photos p join photos_tags pt on pt.photo_id = p.id where pt.tag in ('cute','cool') group by p.id having count(*) = 2 ;


1

You can REPLACE the spaces in xxx yyy and xxxyyy, so when you make a search it will look for xxxyyy: SELECT * FROM test.tbl1 WHERE REPLACE(tbl1.searchfield,' ','') = REPLACE('xxxyyy');


1

Replace space and compare: ... where replace('xxx yyy',' ','') = 'xxxyyy' or generally it can be: ... where replace(column,' ','') = 'xxxyyy' But not that this may not like your indexes on the columns.


1

Try this: SELECT t.user_id, t.audit_event FROM (SELECT ua.user_id, ua.audit_event FROM user_audit ua, user_balance_transactions ubt WHERE (audit_event = 'RGL_TOURNAMENT_ENTRANCE_PAID' OR audit_event = 'RGL_TOURNAMENT_ENTRANCE_REFUNDED') AND ubt.regular_tournament_id='{$tournament_id}' AND ...


1

I hope to have fully understood your request. With this query you get a concatenation of tables (a UNION) with all records. SELECT parent_id, target_id, start_date, end_date, NULL AS jobposition_id FROM study UNION ALL SELECT parent_id, target_id, start_date, end_date, jobposition_id FROM job You can play with UNION to obtain the merging concept you have ...


1

Your query uses SELECT * ... which will select every column from the tables (in the order that they exist within the tables). So if you have: CREATE TABLE t1 ( id bigserial primary key, -- bleh - never use just "id" .. this is only for example desc text ); CREATE TABLE t2 ( id bigserial primary key, t1_id bigint references t1 (id), desc text ); ...


1

I optimized your query. I use IN instead OR because your point to the same field default_users.group_id and I added the brackets after the AND with (default_users.email LIKE '%polar%' OR default_users.username LIKE '%polar%'). You can use || instead OR (works the same). SELECT default_users.*,default_profiles.* FROM test.default_users LEFT JOIN ...


1

I think I am starting to understand. When I asked you to run SELECT time_on FROM writetest_table ORDER BY time_on LIMIT 1; You said it was 2015-07-13 15:11:56 which you have in your WHERE clause When you did the query select sum(diff_ms) from writetest_table; It performed a full table scan of 35.8 million rows. When you did the query select ...


1

The following query is derived from that posted by a_horse_with_no_name. His answer hit's the core of the question (filtering against a sub-query's window function) but missed an important point where I need to select all orders that have any fillable value (hence in my python snippet the loop exit happens after the order processing). The following query is ...


1

Here's your query: SELECT T.location_id, GROUP_CONCAT(T.service_id) AS serviceids FROM `Table` AS T GROUP BY T.location_id HAVING serviceids IN ('2,3'); You could use ORDER BY inside the GROUP_CONCAT(): SELECT T.location_id, GROUP_CONCAT(T.service_id ORDER BY service_id ASC) AS serviceids FROM `Table` AS T GROUP BY T.location_id HAVING ...



Only top voted, non community-wiki answers of a minimum length are eligible