I am having trouble optimizing a relatively simple query involving a GROUP BY, ORDER BY, and LIMIT. The table has just over 300,000 records. Here's the schema (I added some extra indexes to experiment with): CREATE TABLE `scrape_search_results` ( `id` int(11) NOT NULL auto_increment, `creative_id` int(11) NOT NULL, `url_id` int(...
I have a problem similar to the stackoverflow question posed in the link below except that I need to exclude certain fields from the comparison but still include it in the result set. I'm penning the problem as locally symmetric difference. For example Table A and B has columns X,Y,Z and I want to compare only Y,Z for differences but...
Why in the first case displays debit = 'null', and the second the right amount ~ SELECT o.kadastr, /* Кадастровый номер */ d.ar_inn, /* ИНН арендатора */ o.adres, /* Адрес участка */ r.name, /* ФИО арендатора */ o.pl_common, /* Общая плащад...
hi .. i am trying to generate a script from sql 2005 database to sql 2000 database . After determining all the objects of database it is stopped and shows a message in error report that "Script failed for Server 'HOME\SQLEXPRESS'. (Microsoft.SqlServer.Express.Smo)" anyone can suggest me what should i do to convert sql 2005 to sql 2000 ...
I have the following store proc SET @sql = 'RESTORE DATABASE ' + quotename(@dbname) + ' FROM DISK = N''E:\sql\template_' + @dbnamebak + '.bak'' WITH FILE = 1, MOVE N''FromTemplate' + @dbname + '.Data'' TO N''E:\sql\' + @dbname + '.mdf'', MOVE N''FromTemplate' + @dbname + '.Log'' TO N''E:...
I have the table Articles -------- id,name,type_id Type--------------id,name article_type ----------article_id , type_id I using this query select A.name from types A inner join article_type B on ( B.type_id = A.id and article_id = 10) Now this query is working but it gives me separate rows but i want all types in one variables so...
I have a table:- SKU DATE VALUE ABC 2 Aug 10 100 ABC 15 Aug 10 150 ABC 20 Aug 10 180 DEF 2 Aug 10 130 GHI 3 Aug 10 120 JKL 2 Aug 10 140 JKL 20 Aug 10 160 I want it to be:- SKU DATE VALUE ABC 20 Aug 10 180 DEF 2 Aug 10 130 GHI 3 Aug 10 ...
I have a Table1: ID Property 1 Name 2 City 3 Designation and Table2: ID RecordID Table1ID Value 1 1 1 David 2 1 2 Tokyo 3 2 1 Scott 4 2 3 Manager The Table1ID of Table2 maps to Table1's ID. Now I wish to show the Table1 Property column values as colum...
My Query does not work when I have not set the ProcessorID in myComputer table How can I make a query that will show me the ProcessorName when I have a value assigned in ProcessorID or NULL when I don't? CREATE TABLE myProcessor ( ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, Name VARCHAR(255) NOT NULL ) ENGINE=InnoDB; INSERT IN...
As I am new for the REGEX i am not able to solve below thing. And please share some parser related links so the i can learn it. I am facing problem in solving int below SQL statement. Its more line added to the previous INPUT. Please help me to slove this. DECLARE numerator NUMBER; BEGIN SELECT x, y INTO numerator, denominator FROM...
Hello everybody ::- ). I'm having a weird issue concerning Microsoft SQL Compact Edition. In a Windows.Forms application, I try to modify a database which was created by the same (.Net 2.0) application. The database gets sent to a Windows Mobile Phone later, but this is not important. What's important is that on the normal regional setti...
I was watching this screencast at RailsLab where the presenter claims that it's possible to have a master DB for write operations and a slave DB for read operations. While for certain types of Web sites (e.g. blogs, social networks, Web 2.0 sites, etc.) it is acceptable for the master and slave DBs not to be 100% synchronized for short p...
If i use Natural joins than what is the use of primary key and foreign key? ...
I maintain a product that has a home-grown data access layer, programmed in C++/COM for use with a Windows-based web application designer and it is at least 10 years old. This DAL is modularized with something akin to Data Providers so that it can generate basic SQL and DDL for a specific database type. In the process of migration anal...
Hi everybody, I am doing some tests here and write some SQLs. I try to write some reusable code and therefore I want to declare some variables at the beginning and reuse them in the script, like this: DEFINE stupidvar = 'stupidvarcontent'; SELECT stupiddata FROM stupidtable WHERE stupidcolumn = &stupidvar; I tried so far: Use a DEC...
I have 2 databases with the same data, but slightly different data types in some fields (eg SQLite DB has TEXT where MySQL DB has varchar(50)) I run the following query: SELECT * FROM audio a, audiocodec ac, fileproperties f, transportstream t, transportservice ts, video v, videocontent vct, videoFrameRate vf, ...
Will queries internal having one or more subquery result in cursors at SQL Server level. In otherwords I am not explicitly using cursors but i have query with a sub query. Now for processing this query will SQL server create cursors internally. ...
Hi, I need to execute an SQL query. If i have a query with multiple tables in From clause with Join condition in Where clause, And i have Group by statement, Should i perform Join operation first followed by Group By ? OR should i perform Group By first then Join ? Which one would be better ? Note: In my environment, whichever operator ...
It seems pretty simple i have a table 'question' which stores a list of all questions and a many to many table which sits between 'question' and 'user' called 'question_answer'. Is it possible to do one query to get back all questions within questions table and the ones a user has answered with the un answered questions being NULL value...
i have four queries, all of which are valid and they do the same work. how to find out which one is the most optimal? i'm using oracle 10g. is there any method like finding time complexity of a program? ...