Tag Info

New answers tagged

0

Better yet: where orders.created >= '2015-07-01' and orders.created < '2015-07-01' + INTERVAL 1 MONTH; This works for DATE, DATETIME, DATETIME(6), leap year, etc. (Notice the <, not <=.)


2

In your first query you do ...orders.created <= '2015-07-31'; which is actually orders.created <= '2015-07-31 00:00:00'; since you have a timestamp or datetime datatype. Either use the DATE() function like you did in your other attempt, or write it like orders.created <= '2015-07-31 23:59:59';


1

What you must do is increase the join_buffer_size. What is join_buffer_size ? The minimum size of the buffer that is used for plain index scans, range index scans, and joins that do not use indexes and thus perform full table scans. Normally, the best way to get fast joins is to add indexes. Increase the value of join_buffer_size to get a faster full ...


0

select x.* from member as x where x.id IN ( select id from member group by id having count(distinct email) > 1 )


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.


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

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');


0

I found the better solution: on php-level surround user's input by % and remove spaces: Regex search for numbers: preg_match('/\d+/', $search, $matches); Replace substring with numbers to substring with numbers surrounded by %: $search = str_replace($matches[0], '%'. $matches[0] . '%', $search); Remove spaces: $search = str_replace(' ', '', $search);


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.


-4

If the group by clause is missing (could be a copy & paste error) , this could be the reason


0

You can use MAX function to get the max value from create_time of user_balance_transactions table. SELECT ua.user_id, ubt.regular_tournament_id, MAX(ubt.create_timestamp) as event_date, ua.audit_event FROM test.user_audit AS ua LEFT JOIN test.user_balance_transactions AS ubt ON (ubt.audit_event_id=ua.id AND ubt.user_id=ua.user_id AND ...


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 ); ...


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 ...


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 ...


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 ...


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 ...


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 ...


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 ...


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 ...


0

You open two cursors but close the outer one before the inner one open gen_row; -- open the outer cursor...this semicolon seems odd for data in(SELECT * from testnirmal) loop fetch gen_row into a1; open gen1_row(a1); fetch gen1_row into a2; dbms_output.put_line(a2); close gen1_row; end loop; close gen_row;--here you close the outer cursor close gen1_row; ...


0

Figured it out. A bad script granted database-wide SELECT permissions to the user. Found it using this script: WITH perms_cte as ( select USER_NAME(p.grantee_principal_id) AS principal_name, dp.principal_id, dp.type_desc AS principal_type_desc, p.class_desc, OBJECT_NAME(p.major_id) ...


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 = ...



Top 50 recent answers are included