Tag Info

Hot answers tagged

3

It's possible for a SELECT statement to modify the database, for example: select mysequence.nextval increments a sequence select myfunction executes a PL/SQL function which could have any sort of side effects The last point is a bit of a corner case since it only works with autonomous transactions, otherwise errors such as ORA-14551: cannot perform a ...


3

Yes, you give permissions only required users in SQL Server using GRANT like this GRANT SELECT ON <schema>.<tablename>(<Col1>,<Col2>) TO [Domain\User] For more info, refer Grant


3

You explain: I get result 1453 only when all items in column attributeY are empty for 1453. But that's incorrect. Bold emphasis mine. The aggregate function count returns (per documentation): number of input rows for which the value of expression is not null The same is true for SQLite (per documentation): The count(X) function returns a ...


2

The following is your common denominator, which, not seeing the data and assuming I am reading your question correctly, would have a count of 3074 rows. SELECT DISTINCT [AC2].[Agent] FROM [AgentCommTMP] AS [AC2] WHERE [AC2].[InvoiceID] IS NOT NULL which is different from the following which has the potential to have more rows. SELECT ...


2

It works for me with a similarly created structure. You didn't provide DDL and INSERTs for your table so I just made up a shorter one that should have the same effect: $ sqlite3 test.db SQLite version 3.8.8.3 2015-02-25 13:29:11 sqlite> CREATE TABLE test(x integer, y text); sqlite> INSERT INTO test(x, y) VALUES (1, ''); sqlite> INSERT INTO test(x, ...


2

You can use INTERSECT which is available in both HSQLDB and SQL Server SELECT SERVICE_SID FROM SQLTable WHERE attribute_sid = 5 AND Value='VAL1' INTERSECT SELECT SERVICE_SID FROM SQLTable WHERE attribute_sid = 4 AND Value='VAL8'; You can then join the result of this query with your table and group by SERVICE_SID


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 ...


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


1

Broadly speaking, on a MyISAM Table with a range scan, the process is: Find the first Index result using the BTREE (inside the .MYI file) and access the row result (on the .MYD file) - Handler_read_key Get the next result, using the index (and in the same order), until the value retrieved is larger than the one defined (multiple instances of ...


1

If you already have the PK value of a certain row in A, a simple SELECT is the fastest possible way: SELECT * FROM b WHERE a_id = ? If you need to retrieve a_id first, a join is your best options. Make that a LEFT [OUTER] JOIN only if there can be cases where there are no matching rows in B and you still want a_id in that case (or whatever, you didn't ...


1

PROPOSED QUERY SELECT B.firstname,B.lastname FROM ( SELECT CONCAT(lastname,LEFT(firstname,1)) nametag,COUNT(1) rcount FROM names GROUP BY CONCAT(lastname,LEFT(firstname,1)) HAVING COUNT(1) > 1 ) A INNER JOIN ( SELECT CONCAT(lastname,LEFT(firstname,1)) nametag, firstname,lastname FROM names ) B USING (nametag); YOUR SAMPLE DATA ...


1

You are just missing the GROUP BY YOUR QUERY WITH GROUP BY SELECT student.StudentID, student.`Name`, COUNT(attendance.AttendanceID) AS Total FROM student LEFT JOIN attendance ON student.StudentID = attendance.StudentID GROUP BY student.StudentID,student.`Name`; SAMPLE DATA DROP DATABASE IF EXISTS alishaikh; CREATE DATABASE alishaikh; USE ...



Only top voted, non community-wiki answers of a minimum length are eligible