First we get the max id from the ProductFileLocalName and then - 1000 (as we don't want to delete the most recent additions as they might not be inserted in ProductFileInfo yet) Then we pass the max id to this stored procedure: DELETE TOP (10000) FROM ProductFileLocalName WITH (ROWLOCK) FROM ProductFileLocalName LEFT OUTER JOIN Pr...
Hey there I'd like to create a synonym on a local db that points to a db on the web that's only accessible with a user name and password. Is there a way to set up a synonym for that kind of situation? I would be very grateful for an example if this is even possible... Thanks ...
I see that in SQL, the GROUP BY has to precede ORDER BY expression. Does this imply that ordering is done after grouping discards identical rows/columns? Because I seem to need to order rows by a timestamp column A first, THEN discarding rows with identical value in column A. Not sure how to accomplish this... I am using MySQL 5.1.41 ...
What is the best way to create an SQL index in SQL database? CREATE INDEX idx ON sometable (col1, col2, col3); Or CREATE INDEX idx1 ON sometable (col1); CREATE INDEX idx2 ON sometable (col2); CREATE INDEX idx3 ON sometable (col3); Which is the difference between these 2 methods? Does it depends on the SQL implementation? (SQLite, M...
Hello, I need to select some rows from second table and concatenate them in comma-separated string. Query works well except one problem - It always selects all rows and ignores LIMIT. This is part of my query which gets that string and ignores LIMIT: select group_concat(value order by `order` asc SEPARATOR ', ') from slud_data ...
I have a table which holds ~1M rows. My application has a list of ~100K IDs which belong to that table (the list being generated by the application layer). Is there a common-method of how to query all of these IDs? ~100K Select queries? A temporary table which I insert the ~100K IDs to, and Select query via join the required table? Tha...
How can I use a parameter inside sql openquery, such as: SELECT * FROM OPENQUERY([NameOfLinkedSERVER], 'SELECT * FROM TABLENAME where field1=@someParameter') T1 INNER JOIN MYSQLSERVER.DATABASE.DBO.TABLENAME T2 ON T1.PK = T2.PK ...
I need to do subquery in subquery what causes "Unknown column 't1.product_id' in 'where clause'". It's on line 7. in my example. How to solve this problem? SELECT *,product_id id, (SELECT GROUP_CONCAT (value ORDER By `order` ASC SEPARATOR ', ') FROM ( SELECT `order`,value FROM slud_data LEFT JOIN slud_...
I have a table with an itemID and a categoryID columns. Both columns are primary keys because each item can have more than 1 category: itemID | catID ----------------- 1 | 2 1 | 3 1 | 4 2 | 2 2 | 3 2 | 4 I want to select items with the same categories (based on all the item categories,...
Hi! I'm new to C# and Subsonic. I'm trying to solve the following case: public class UnknownInt { public int val; public bool known; } public class Record { public int ID; public UnknownInt data; } I'm using SimpleRepository. Is there a way I can get UnknownInt serialized before storing it in the SQL database (perhaps as ...
Given that I have a table with a column of TEXT in it (MySQL or SQlite) is it possible to use the value of that column in a way that I could find similar rows with somewhat related text values? For example, I if I wanted to find related rows to row_3 - both 1 & 2 would match: row_1 = this is about sports row_2 = this is about study row...
Hello, A clueless Python newbie needs help. I muddled through creating a simple script that inserts a binary file into a blog field in a SQLite database: import sqlite3 conn = sqlite3.connect('database.db') cursor = conn.cursor() input_note = raw_input(_(u'Note: ')) input_type = 'A' input_file = raw_input(_(u'Enter path to file...
What data type should i use for a SQL column to store Product version eg. Version 0.1 0.1.1 0.2 1.1 1.1.647 2.0 ..... In query i should be able to sort them based on version number and i want an optimal query to find highest number. Thanks ...
CREATE USER [anc] WITHOUT LOGIN WITH DEFAULT_SCHEMA=[anc] GO what is the difference between the user and the login. i am not clear as to y do we create these kind of users?? ...
I have a following SQL table with data ProductList id order productname 79 1 name1 42 2 name2 67 3 somename 88 4 othername 99 5 XYZ 66 6 ABC Display order is very volatile, it will change frequently, users will add or remove items and reorder the items. How should i handle this situation without ...
I have this line of code byte[] field1 = (reader.GetSqlBinary(reader.GetOrdinal("Field1")).Value; which is a SqlDataReader I am trying to convert the code to use the enterprise library data access block but cant figure out how to get a byte[] using the IDataReader. I've had a good look through the MS docs but failed to find anythin...
I know char, int, numeric, but i don't know what is used for percentage ? ...
Need to rank the below by salary, with highest salary having rank 1. RANK column shown is what I'm after. Empname sal address RANK Ram 3411 45,east road 2 Anirban 2311 34,west wind 4 Sagor 10000 34,south 1 Manisha 3111 12,d.h road 3 ...
Hello, I have a very specific query. I tried lots of ways but i couldn't reach the performance i want. SELECT * FROM items WHERE user_id=1 AND (item_start < 20000 AND item_end > 30000) i created and index on user_id, item_start, item_end this didn't work and i dropped all indexes and create new indexes user_id, (item_sta...
How do I combine the resultsets to return a single result in SQL? For example - SELECT * FROM Table1 SELECT * FROM Table2 I want to combine the two resultsets with the columns from the second resultset appended to the first. Table 1 and Table 2 are not related to each other in any way. If Table 1 has 2 columns and Table 2 has 4 colum...