Tag Info

New answers tagged

0

Are strings in table columns represented as bit patterns or Unicode? Computers only deal with 1's and 0's (i.e. binary); datatypes indicate how to interpret that info. Would the queries perform faster if the column in the table was designed to have the year at the left of the name in the strings that are stored? (assuming the year component of the ...


2

You need conditional aggregation using sum(case): select cat_id, sum(case when status = 1 then 1 else 0 end) as published, -- only count status 1 sum(case when status = 0 then 1 else 0 end) as unpublished,-- only count status 0 count(*) as totals from tab group by cat_id Edit: My crystal ball was apparently broken, but now it's fixed :-) If ...


0

You can't do this in a single step. The locking required to truncate the table precludes you querying the table at the same time. The best option you would have is to declare a global temporary table (DGTT) and insert the rows you want into it, truncate the source table, and then insert the rows from the DGTT back into the source table. Something like: ...


0

If you are doing SQL, then you probably would need to do a TRUNCATE/DELETE on the table and then INSERT into. If you are using LOAD and/or IMPORT, they both have options for clearing the table before getting data into the table.


4

Generally, you can always wrap a query as subquery if you don't want to output all rows: SELECT title FROM (SELECT ...) sub; But you can also use expressions in ORDER BY, not just input or output columns. So there is no need for this (like @ypercube already commented). For the case at hand, it must be mentioned that you are using the additional module ...


0

This query will return 8 similar posts to the current post we are viewing. The number 5 is placeholder. It need to be overridden by variable. SELECT SIMILARITY(title, (SELECT title FROM gallery WHERE id=5)) AS sim, title, uri FROM gallery WHERE title % (SELECT title FROM gallery WHERE id=5) AND id!=5 ORDER BY sim DESC LIMIT 8;


0

You want 3 different searches and the results combined. I'd use UNION: SELECT u.id AS user_id, u.name AS name, mm.value AS mail, mt.value AS tel FROM `user` AS u LEFT JOIN user_media AS umt JOIN media AS mt ON mt.id = umt.media_id AND mt.type = 'T' ON u.id = umt.user_id LEFT JOIN user_media AS umm ...


-1

In Relational Algebra Projection means a vertical subset of a relation which contains the attribute values of given columns


1

WITHOUT INDEXING SELECT * FROM actor_info WHERE film_info LIKE '%Animation%' OR film_info LIKE '%Documentary%'; or SELECT * FROM actor_info WHERE LOCATE('Animation',film_info)>0 OR LOCATE('Documentary',film_info)>0; WITH INDEXING Create a FULLTEXT index on film_info ALTER TABLE actor_info ADD FULLTEXT (film_info); Query using the FULLTEXT ...


0

The only permissions you granted that user are create table and alter schema. Unless you specifically grant the permissions you want that user to have, they won't have it. Or you could grant a role.


1

Granting schema modification is not, and should not be, the same as granting DML rights. Grant the user the exact rights that user needs, including 'SELECT', etc, as necessary. See https://msdn.microsoft.com/en-us/library/ms178569.aspx for details.


0

Another approach: LOAD DATA into a new (temp) table UPDATE real JOIN new ON ... SET real.x=... WHERE ... -- Update any existing rows that need updating INSERT INTO new SELECT new LEFT JOIN real ON ... WHERE real.id IS NULL -- Add any rows that do not already exist


2

Since there is a unique constraint/index on number, you can use ON DUPLICATE KEY UPDATE syntax - which can be combined with INSERT ... VALUES so all the rows are inserted/updated in one statement. The additional requirement that new null values do not update existing ones can be satisfied using COALESCE() function. Example: INSERT INTO Contacts (number, ...


2

Your best bet will be to use SSIS or BULK INSERT. There are various performance improvements that you can do when using them and they are very well documented in The Data Loading Performance Guide. At SSIS level, you can look into below things to speed up data read and data load : Fast Parse Option along with its limitations. Use the SQL Server Native ...


0

All of this could have been accomplished with the INFORMATION_SCHEMA database The table names are in the INFORMATION_SCHEMA SELECT table_name FROM information_schema.tables WHERE table_schema='conntrack'; You could then line up the tables that exist against the tabidx table SELECT B.table_name FROM (SELECT date FROM tabidx) A INNER JOIN (SELECT ...


1

Are you just looking for distinct Agents that have at least one null invoice and at least one non-null invoice? SELECT [AC].[Agent] FROM [AgentCommTMP] AS [AC] GROUP BY [AC].[Agent] HAVING Count(CASE WHEN [AC].[InvoiceID] IS NULL THEN 1 ELSE Null END) > 0 AND Count(CASE WHEN [AC].[InvoiceID] IS NULL THEN Null ELSE 1 END) > 0


0

Please try the following. If you need most recent date for every song over all meeting instances: select song_id, max(meeting_date) from song_instances si join meeting_instances mi on mi.meeting_instance_id = si.meeting_instance_id group by song_id If you're interested only in particular meeting (meeting_id = 4): select song_id, max(meeting_date) ...



Top 50 recent answers are included