Please help me with writing a sql query - I have a table with id, name and marks columns. If I arrange the table in ascending order of marks, how can I fetch 5 names whose marks are close to a particular name. ...
I need to move a huge amount of data from a couple tables in an old database to a couple different tables in a new database. The databases are SQL Server 2005 and are on the same box and sql server instance. I was told that if I try to do it all in one shot that the transaction log would fill up. Is there a way to disable the transaction...
I have a table of purchases containing a user_id and a date_of_purchase. I need to be able to select all the users who have made 2 purchases within 12 months of each other. The dates can be any point in time as long as they are less than 12 months apart. e.g. user_id date_of_purchase 123 01/Jan/2010 124 01/Aug/...
Okay, I need help. I'm usually pretty good at SQL queries but this one baffles me. By the way, this is not a homework assignment, it's a real situation in an Access database and I've written the requirements below myself. Here is my table layout. It's in Access 2007 if that matters; I'm writing the query using SQL. Id (primary key) P...
Part of a complex query that our app is running contains the lines: ...(inner query) SELECT ... NULL as column_A, NULL as column_B, ... FROM ... This syntax of creating columns with null values is not allowed in DB2 altough it is totally OK in MSSQL and Oracle DBs. Technically I can change it to: '' as column_A, '' as column_B, B...
I have two tables with the same structure: id name 1 Merry 2 Mike and id name 1 Mike 2 Alis I need to union second table to first with keeping unique names, so that result is: id name 1 Merry 2 Mike 3 Alis Is it possible to do this with MySQL query, without using php script? ...
This is probably an amateur question but I'm an amateur ! ;o) I have several tables: Account table, Officer table, Location table, Web table... The Officer table doesn't give me the number of officers per account. I need to retrieve only the accounts that have more than 20 officers. Should I use the COUNT ? If so, how ? Your help is...
Hi, sorry for formatting this wonky but hope you can understand it. I have two MySql tables, and I want to find and replace text strings in one using data in another. Texts - one column: messages 'thx guys' 'i think u r great' 'thx again' ' u rock' Dictionary - two columns: bad_spelling, good_spelling 'thx' 'thanks' ' u ' ' you ' ' r ...
I'm trying to figure out how to define the schema of a database on with Postgresql 8. I have 2 tables: Journals, Books that define the publications I have Journal: id_j, name, issn, other fields Book: id_b, name, isbn, author, other fields and I have another table Scans that logically refers both the previous tables. Scans: id, m...
Compare these 2 queries. Is it faster to put the filter on the join criteria or in the were clause. I have always felt that it is faster on the join criteria because it reduces the result set at the soonest possible moment, but I don't know for sure. I'm going to build some tests to see, but I also wanted to get opinions on which would ...
Hi folks, I have a few item listed in a database, ordered through Reddit's algorithm. This is it: def reddit_ranking(post): t = time.mktime(post.created_on.timetuple()) - 1134000000 x = post.score if x>0: y=1 elif x==0: y=-0 else: y=-1 if x<0: z=1 else: z=x return (log(z) + y * t/45000) I'm wonde...
Why this query doesn’t working: SELECT name FROM ( SELECT name FROM table1 UNION SELECT name FROM table2 ) q It returns nothing, even no error or empty table. But SELECT name FROM table1 UNION SELECT name FROM table2 Is ok. It returns table with name field full of rows from two tabl...
I have this function: CREATE OR REPLACE FUNCTION CREATE_AIRSPACE_AVAILABILITY_RECORD (cur_user VARCHAR, start_time VARCHAR, start_date VARCHAR, end_time VARCHAR, end_date VARCHAR, airspace_name VARCHAR) RETURNS VOID AS ' DECLARE c_user ALIAS for $1; BEGIN IF start_time IS NULL OR start_date IS NULL OR end_time IS N...
Hi folks, I was working on a project a few months ago, and had the need to implement an award system. Similar to S*tackOverflow's badge system*. Badges I might have not implemented it in the best possible way, and I am curious what your say in it would be. What would a good way to track user activities, needed for badge awarding be?...
Does anyone knows if removing the field names from an INSERT query results in some performance improvements? I mean is this: INSERT INTO table1 VALUES (value1, value2, ...) faster for DB to be accomplished rather than doing this: INSERT INTO table1 (field1, field2, ...) VALUES (value1, value2, ...) ? I know it might be probably a...
I'm trying to LEFT JOIN two tables, to get a list of all rows from TABLE_1 and ONE related row from TABLE_2. I have tried LEFT JOIN and GROUP BY c_id, however I wan't the related row from TABLE_2 to be sorted by isHeadOffice DESC. Here are some sample tables TABLE 1 c_id Name ---------------- 1 USA 2 Canada 3 Engla...
If I try this statement: INSERT INTO TerminalEventChild (id,stringValue) VALUES (64,'version123|'); MySQL fail with : Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''version123' at line 1 SQLState: 42000 ErrorCode: 1064 If I remove the ...
Hello I have these tables: businesses :name :rating categories :name business_categories :business_id :category_id reviews content business_id Now I want to do a search, when type keywords, match it in these columns: business.name business.category.name business.review.content Then order the results by the weight,...
If I have a field in a table of some date type and I know that I will always be searching it using comparisons like between, > or < and never = could there be a good reason not to add an index for it? ...
Looking for a simple SQL (PostgreSQL) regular expression or similar solution (maybe soundex) that will allow a flexible search. So that dashes, spaces and such are omitted during the search. As part of the search and only the raw characters are searched in the table.: Currently using: SELECT * FROM Productions WHERE part_no ~* '%sear...