Tag Info

New answers tagged

0

Assuming name of fields that enter in action for the query are id, distance and date SELECT id, SUM(distance) AS sum_distance FROM raporty WHERE YEAR(date) = YEAR(CURDATE()) AND MONTH(date) = MONTH(CURDATE()) GROUP BY id ORDER BY sum_distance DESC LIMIT 3; LIMIT 3 is a MySQL specific syntax but you can replace this by TOP (3) if you use ...


0

What you are looking for is known as the transitive closure of your parents relation. A. Aho and J. Ullman states back in 1979 that ”There is no relational algebra (or relational calculus) expression that defines the Transitive Closure of a given - binary - relation”. In SQL99 Recursive Common Table Expression's where introduced which extends the ...


0

No simple select exists because MySQL does not support Recursive CTE queries like SQL Server. This being the case, I wrote up some Stored Procedures to run iterative techniques for gathering children of a parent out of a hierarchical data design. Here is my old post from Oct 24, 2011 : Find highest level of a hierarchical field: with vs without CTEs I ...


1

Did you try chaining your WITH statements together? N.B. This may not be the most-optimal method of solving this problem, but should work based on your existing approach CREATE VIEW dbo.MyViewName AS WITH R(N) AS ( SELECT 0 UNION ALL SELECT N+1 FROM R WHERE N < 12 ), MonthYears AS ( SELECT ...


0

This is a workaround based on the above suggested approach. DECLARE tables CURSOR FOR SELECT * FROM information_schema.tables WHERE table_schema = 'public' ORDER BY "table_name" ASC LIMIT ((SELECT count(*) FROM information_schema.tables WHERE table_schema = 'public')-1); --Because the following ...


0

Note the extra brackets. SQL> variable d varchar2(100); SQL> exec :d := '09-DEC-2014'; PL/SQL procedure successfully completed. SQL> select '''' || (to_date(:d, 'DD-MON-YYYY') + 1) || '''' from dual; ''''||(TO_DATE(:D,'D -------------------- '10-DEC-14' Changing date format: SQL> alter session set nls_date_format='DD-MON-YYYY'; Session ...


0

This is very simply a case where your server does not have sufficient capacity (often disk I/O, but sometimes memory or CPU) to handle the workload. Poorly-written queries and insufficient or sub-optimal indexes are a common contributor, as are applications that give up too soon and re-send the same query that's already running, and an improperly sized ...


1

Please create the following index ALTER TABLE movie.movies ADD INDEX sid_mgid_ndx (s_id,mg_id); This index will help the WHERE and GROUP BY Perhaps a different index would help ALTER TABLE movie.movies DROP INDEX sid_mgid_ndx, ADD INDEX sid_mgid_id_ndx (s_id,mg_id,id) ; and adjust the query SELECT SQL_NO_CACHE m.id, m.sku, m.movie_url FROM ...


4

The SELECT ... INTO syntax is only for new tables. I think you want: INSERT dbo.TableB ( <column list> ) SELECT <column list> FROM dbo.TableA WHERE ID IN ( SELECT ID FROM dbo.TableC ) ;


1

The easiest way I know of is creating a table in tempdb with a single column (id for instance), then expand the tempdb node in SSMS and find the table. Right click and select "Edit top 200 rows". A grid will appear. Select the whole first row with the row header on the left, then paste the whole contents of the file. BCP is an alternative way to load the ...


0

I generally use PL/SQL collections for this. Essentially you create a table type, then create a variable with that table type, then do a select for update to get all of the data that you want to update, then do a forall update to update the data. Since you are updating the a table the record type should be that table. Such as: TYPE sku_tab_type IS TABLE OF ...


2

You asked "why this is taking too long?". You also said "Unfortunately, this took more than 5 seconds to retrieve the data and show them to me". Also, you reported the profiling output of your query. As you can see yourself, the sum of times reported by the profiler for each steps count to 0.000154 seconds. So, from the point of view of the profiler, the ...


0

You can use an alias (which is always a good idea for calculated columns in your result). SELECT COUNT(*) AS " " FROM my_table; In this case you're using a single whitespace as an alias. That may be sufficient for your needs. As noted in the comments, depending on your database and your client, you can try SET HEADING OFF (on Oracle) or an equivalent ...


1

SQL Server stores all the columns for one row together in on a single disk page. (It's more complex than this but for int and char etc. this is more-or-less true.) To retrieve any column's value the whole page is read into RAM. So once you have any column available for a given row, all of the columns for that row are available. There are several buffers ...



Top 50 recent answers are included