Tag Info

New answers tagged

-1

Add one composite index with two columns: pid, version: ALTER TABLE auc_daily_histories ADD INDEX pid_version_ix -- pick a name for the index (pid, version) ; And buy new server or move it to AWS RDS.


0

This is not possible without using PL/SQL or a loop to generate the SQL in the program you're using to execute the SQL.


0

That sounds like it is using an inappropriate query plan. There are two obvious paths this query could take and it is presumably deciding between them depending on the values of @row_offset and @row_count: for a small offset it is probably better to scan SomeCol,SomeOtherCol in order and apply the complex filtering clauses (in the WHERE and JOIN ON clauses) ...


1

The 2 queries that you show are different in meaning. The first one will select all upvotes for that ip and content in that timeframe. If you create the index: mysql> ALTER TABLE content_votes_tmp ADD INDEX(up, ip, content, datetime, record_num); Query OK, 753676 rows affected (3.85 sec) Records: 753676 Duplicates: 0 Warnings: 0 You can get good ...


2

You are confusing PL/PgSQL with SQL. You do not need the RETURN QUERY, nor is it valid in normal SQL. Simply SELECT text FROM eightks ... Additionally, if you want to direct output to a particular file with psql's \o, you must run the \o command before the statement you want to write the output for. Finally - I wonder if you should be looking into the ...


0

I just loaded this into MySQL 5.5.37 on my Windows Laptop use test drop table if exists tbl1; create table tbl1 (id int not null, col1 int not null, col2 int not null, col3 int not null, type int not null); insert into tbl1 values (1, 12, 12, 23, 1), (2, 23, 33, 1, 0), (3, 333, 2, 21, 1); Then, I tried out the query mysql> ...


2

Two options. a) Use sub query select concat(10*floor(age/10), '-', 10*floor(age/10) + 10) as `range`, gender, count(*) as count from ( select *, TIMESTAMPDIFF(YEAR,birth_year,CURDATE()) AS age from transaction left join patient on patient_id = patient.id ) as t group by `range`, gender; Gives +-------+--------+-------+ | ...


0

;with [Users](Id,Name,Other_Columns) AS( SELECT 1,'John','Blah' UNION ALL SELECT 2,'Ricky','Blah' UNION ALL SELECT 3,'Stella','Blah' UNION ALL SELECT 4, 'Bob','Blah') , Saldo (Id,[User_id],Saldo) AS (SELECT 1, 3 , 0.00 UNION ALL SELECT 2, 1, 9.00 UNION ALL SELECT 3, 2, 0.15 UNION ALL SELECT 4, ...


3

You can use a cross apply and values to create a row for each value and an extra column in the values clause to keep track of what value is a major and what value is a minor. SQL Fiddle MS SQL Server 2008 Schema Setup: CREATE TABLE ApplicantCourseDetail( [Major] [nvarchar](50) NULL, [MinorI] [nvarchar](50) NULL ) INSERT INTO ApplicantCourseDetail ...


2

SELECT * FROM (SELECT Major, COUNT(Major) AS MajorCount FROM #ApplicantCourseDetail group by Major) A FULL JOIN (SELECT MinorI, COUNT(MinorI) AS MinorCount FROM #ApplicantCourseDetail group by MinorI)B ON A.Major = B.MinorI Result ╔═══════════╦════════════╦════════════╦════════════╗ ║ Major ║ MajorCount ║ MinorI ║ MinorCount ║ ...


0

The counts are using the primary keys, but the joins are not. If there aren't indexes on x_user_images.user_id x_allies.to x_allies.from x_user_likes.profile_id x_trybe_owner.profile_id x_trybe_invites.to x_subgoal_post.profile_id x_ambition_owner.profile_id x_ambition_invites.to you're going to be doing a lot of table scans. I don't know how MySQL ...


1

Hi as per your requirement try below query with out dynamic query SELECT Table_name AS TablesInDatabase ,table_rows AS NumberOfRows FROM information_schema.tables WHERE Table_schema=DATABASE();


1

I have a very aggressive approach using brute force Dynamic SQL SET group_concat_max_len = 1024 * 1024 * 100; SELECT CONCAT('SELECT * FROM (',GROUP_CONCAT(CONCAT('SELECT ',QUOTE(tb),' Tables_in_database, COUNT(1) "Number of Rows" FROM ',db,'.',tb) SEPARATOR ' UNION '),') A;') INTO @sql FROM (SELECT table_schema db,table_name tb FROM ...


4

This SQL works fine in SQL Server (with appropriate syntax modifications): UPDATE Visits SET Duration = TimeDiff( ( SELECT ReqTime FROM Visits N WHERE n.ReqTime > R.ReqTime ORDER BY ReqTime LIMIT 1) ,R.ReqTime ) FROM Visits R ; Unfortunately, the syntax hits a limitation of MySQL that an updated table cannot be referenced (again) ...


1

First and foremost only ever name the columns you actually need in the select list. Never ever use SELECT *. That said, you may already be in a hole out of which you cannot dig. You could create a view to return only the active rows and "data" columns: create table T(c1 int, c2 int, active bit); create view V as select c1, c2 from T where active = true; ...


0

If you're trying to make your lines shorter then you could use IIF( ..., 1, 0 ) instead of CASE ... THEN 1 ELSE 0 END. SELECT Column1 = IIF(@Something = 2 AND @SomethingElse IS NULL AND @AnotherThing IN (1,2), 1, 0) Column2 = IIF(@Something = 3 AND @SomethingElse IS NOT NULL AND @AnotherThing IN (2,3), 1, 0) ; Note: Only available from SQL ...


1

Unfortunately you are not going to really get around SQL's verbosity here. You could write a function (of family of functions) to reduce the amount of text in the query, so you have something like: select Column1 = dbo.YeyOrNay(Something, 2, SomethingElse, 1, AnotherThing, 1, 2), Column2 = dbo.YeyOrNay(Something, 3, SomethingElse, 0, AnotherThing, ...


5

T-SQL doesn't support Booleans like that. But you could use CROSS APPLY with a subquery that uses CASE to produce a bunch of 1s and 0s, and then combine them in the SELECT clause using the bitwise operators & and |.


1

SELECT INTO is usually used to select specific set of data into a table, esp., during scenarios when the data in the table is the priority and not the constraints. It automatically creates a table if there is no such table already. But, INSERT INTO is used when you already have a table that has specific defined constraints and need to add data from a ...


1

then u can use this one SELECT * FROM tablex WHERE Number<>123 OR VAT<>15


-2

Try this one, SELECT * FROM tablex WHERE Number<>123 AND VAT<>15



Top 50 recent answers are included