So assuming we can exclude the user table, it could be rewritten as: select * from expense, transaction where expense_id = transaction_expense_id Now if you want to apply a limit, you could do it like this: select * from expense, transaction where expense_id = transaction_expense_id and expense_id in (select expense_id from expense limit 1) Would … Read more
“From what I have seen, Oracle applies the WHERE predicate before determining what rows to skip.” Yup. It is the only possible way. You can’t skip a row from a resultset until you have determined the resultset. The answer is simply not to limit the number of rows returned by the SELECT statement. You can … Read more
Have you seen this matrix as well? http://msdn.microsoft.com/en-us/library/cc645993.aspx From what I can tell Web excludes a lot of the more advanced features, especially those related to development and data mining, but nothing that would stop you from using it for a non-enterprise web site’s data source. It seems to be between the Express version and … Read more
One difference is that the first option hides the intent by expressing the join condition in the where clause. The second option, where the join condition is written out is more clear for the user reading the query. It shows the exact intent of the query. As far as performance or any other difference, there … Read more
I don’t think MySQL supports materialized views which is what you would need, but it wouldn’t help you in this situation anyway. Whether the index is on the view or on the underlying table, it would need to be written and updated at some point during an update of the underlying table, so it would … Read more
To add the column with a default and then delete the default, you can name the default: ALTER TABLE tbl ADD col INTEGER NOT NULL CONSTRAINT tbl_temp_default DEFAULT 1 ALTER TABLE tbl drop constraint tbl_temp_default This filled in the value 1, but leaves the table without a default. Using SQL Server 2008, I ran this … Read more
If you relax the constraint that the Position column must contain integers from 1 to N and instead allow it to contain any numbers then you can do both searches and updates efficiently. You can insert an item between two other items with position A and B by calculating the average (A + B) DIV … Read more
Per the mySQL documentation you should be able to set up a foreign key mapping to composites, which will require you to create the multiple columns. Add the columns and put this in your group table FOREIGN KEY (`beggingTime`,`day`,`tutorId`) REFERENCES tutorial(`beggingTime`,`day`,`tutorId`) As Steven has alluded to in the below comments, you SHOULD try to re-architect … Read more
Instead of using window functions and partitioning, use a query-level GROUP BY and aggregate with a DISTINCT clause: SELECT rnp.grp_id, array_to_string(array_agg(distinct rnp.cabinets),’,’) AS cabinets, array_to_string(array_agg(distinct ips.address),’,’) AS addresses FROM rnp JOIN ips ON rnp.grp_id=ips.grp_id GROUP BY rnp.grp_id, ips.grp_id; Result: grp_id | cabinets | addresses ——–+————————-+———– 11 | cabs1,cabs2,cabs3,cabs4 | CA,NY 22 | c1,c2 | DC,LA … Read more
If you look at the ALTER TABLE SYTAX you’ll see this ALTER TABLE [ database_name . [ schema_name ] . | schema_name . ] table_name { ALTER COLUMN column_name { [ type_schema_name. ] type_name [ ( { precision [ , scale ] | max | xml_schema_collection } ) ] [ COLLATE collation_name ] [ NULL … Read more