A message from our CEO about the future of Stack Overflow and Stack Exchange. Read now.

New answers tagged

0

Two things that virtually force a temp: GROUP BY <<columns from multiple tables>> ORDER BY <<columns from multiple tables>> This str_to_date(CONCAT(DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 WEEK),'%Y-%m-%d'),' 00:00:00'),'%Y-%m-%d %T') can probably be simplified to CURDATE() - INTERVAL 1 WEEK Note: DATE and DATETIME formats can be ...


0

I did some research and found official guidance on this situation. The issue results from the SELECT INTO query as suspected, this is because it is within the scope of the first BEGIN and END statement which has the following HANDLER declared: DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1; Thus, when the SELECT INTO does not find any rows, ...


0

You will have to do it the way you proposed in your question. In cases of many columns, you can get the code generated for you. I find string manipulation in Excel really useful for generating SQL code.


1

I want to select all the columns in my table with a SELECT * statement This is a Bad Idea. Databases are intrinsically shared resources and, typically, are maintained over time, by more than one person. You write a query today, using "select *" that pulls back all three columns in a table and everything is right with the World. Your Application ...


0

I figured out the solution. I simple had do repeat the table name, as follows: SELECT bin_to_uuid(id) as uuid, table_name.* FROM table_name But if you have a solution that does not require to repeat the table name i will upvote your answer, since repeating all the table names in all the queries will bloat my the code a little. EDIT: As suggested in the ...


0

Try this SELECT * FROM list WHERE DATEDIFF(ls_bday, CURRENT_DATE()) < 7


1

It is not surprising that temp tables would come into play. Why ??? According to the MySQL 5.5 Documentation on join_buffer_size (Italics mine): 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 ...


0

There is a smaller variant to do the same: select a.* from data_20200113 a, data_20200114 b where b.account=a.account and b.id is null Maybe you like it more than the previous variant? :-)


1

That sounds like a simple EXISTS condition select * from data_20200113 d1 where exists (select * from data_20200114 d2 where (d1.account, d1.name) = (d2.account, d2.name) and d2.id is null);


1

Try to use the following: select * from website a where not exists (select null from page where site_id=a.id and deleted=0) Probably this is what you're asking for


-1

You could try this WITH cte AS ( SELECT dp.product_name, SUM(fs.units_sold) AS SumOfunits_sold FROM dim_product dp INNER JOIN fact_sales fs ON dp.product_code = fs.product_code GROUP BY dp.product_name ) SELECT * FROM ( SELECT TOP 1 product_name, SumOfunits_sold FROM cte ORDER BY SumOfunits_sold DESC ) a UNION SELECT * FROM (...


0

I've worked on an answer to my own question: First i created a query for every year in table top2000. The queries have been saved with the name Qry20xx for example Qry2015: **SELECT** T.artist, T.title, T.Year **FROM** Top2000 AS T **WHERE** (((T.Year)=2015)); Then I created a query with Top2000 and Qry2015 relate with a left join test on null every ...


0

Something like this would work (I use dummy data in @tbl and not your Top2000 table): --Dummy table declare @tbl table(a varchar(max) not null,b varchar(max) not null,c integer not null,d integer not null) --Dummy data insert into @tbl(a,b,c,d) values('Abba','Dancing Queen',1720,2015) insert into @tbl(a,b,c,d) values('Abba','Dancing Queen',1340,2016) ...


0

Thanks for clarification - unfortunately MS-Access (and SQL-Server put up some limits .. Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. So I only have a "half" solution for you SELECT DISTINCT Year FROM Top2000 AS T3 WHERE Year NOT IN ( ...


0

What you're looking for is the SYSUTCDATETIME function, which is used like this: SELECT SYSUTCDATETIME(); It returns the current system date and time, adjusted to UTC time. This seems similar to what the postgres equivalent does (except it looks like current_timestamp includes timezone information, while SYSUTCDATETIME does not). If you need the time ...


Top 50 recent answers are included