Hot answers tagged

50

The query is syntactically correct SQL even if table_b does not have a name column. The reason is scope resolution. When the query is parsed, it is first checked whether table_b has a name column. Since it doesn't, then table_a is checked. It would throw an error only if neither of the tables had a name column. Finally the query is executed as: select a....


7

Because Oracle performs a correlated subquery when a nested subquery references a column from a table referred to a parent statement one level above the subquery. http://docs.oracle.com/cd/E11882_01/server.112/e41084/queries007.htm#SQLRF52357 It means in order to determine whether subquery is correlated Oracle must try to resolve names in ...


5

What you see in your execution plan is Sybase performing a DISTINCT operator. When you do a SELECT DISTINCT in Sybase it first builds a Worktable (as you can see in step 1 of your execution plan) and then it selects sorted data removing duplicates from that worktable (which is what you see in step 2). This is documented The sort algorithm doesn't appear to ...


4

Two major improvements: SELECT * FROM certificates c WHERE c.expires_on <= current_date + 30 -- sargable! AND NOT EXISTS ( SELECT 1 FROM certificates WHERE common_name = c.common_name AND expires_on > c.expires_on AND state = 'issued' ); Make the first predicate sargable, so that an index can be used. You second ...


3

There is no name field in table_b so Oracle takes the one from table_a. I tried the EXPLAIN PLAN but this gave me only that there is a TABLE ACCESS FULL. I presume that this will generate some kind of Cartesian Product between both tables that result in a list of all the names in table_a is returned by the sub-query.


3

To address the immediate question: Any reason why the convert function isn't working the way it should? The CONVERT function works with various types. What kind of conversion to apply is determined principally by the types specified by the first argument (explicitly) and the second argument (implicitly as the type of the expression). The presence of ...


3

SELECT a AS result FROM tableX UNION DISTINCT SELECT b FROM tableX ;


3

You can use user-defined variables for this. In your first example this would be as simple as this: SELECT name, @rn := @rn + 1 AS ordinal FROM your_table CROSS JOIN (SELECT @rn := -1) AS var_init_subquery ORDER BY whatever_column; Here we initialize the variable in another query. This is often more comfortable than doing this: SET @rn := -1; SELECT ...


2

The typo in ORDER BY aside, you need descending sort order to get the ingredient with the maximum amount first. SELECT Iname, Amount FROM Composition WHERE Name = 'Pancake' ORDER BY Amount DESC FETCH FIRST 1 ROWS ONLY; Assuming Amount is defined NOT NULL. FETCH FIRST 1 ROWS ONLY is the syntax defined by the SQL standard and supported by several ...


2

I have the following way to get the IDs of the rows that have at least two not null values; however, I am afraid this is not efficient when it comes to few million rows per table. I'd like to share it anyway: mysql> select * from l; +------+------+------+------+------+------+------+------+ | id | var1 | var2 | var3 | var4 | var5 | var6 | var7 | +------+...


2

MySql has no XML data type so it's hardly can help ( more help then app layer code) to generate HTML markup. It can help to create ordered stream of cells. Kind of select r.row_number, h.column_number, c.content from table_cells c join table_rows r on c.row_id = r.row_d join table_cols h on c.column_id = h.column_id order by r.row_number, h.column_number ...


2

Must be something like: select s1.id, s2.id from surveys s1, surveys s2 where (s2.start_date between s1.start_date and s1.end_date or s2.end_date between s1.start_date and s1.end_date) and s1.id!=s2.id; This fetches you the IDs where the START_DATE or END_DATE (of s2) is in the period of another START_DATE and END_DATE (s1).


2

this would be exact select min(id), max(id) from table group by start_da, end_date having count(*) > 1 start in select t1.*, t2* from table t1 join table t2 on t1.startdate between t2.startdate and t2.end_date


1

Unless you are using SSMS to just present the dates, your presentation layer should handle the part of presenting the date in whatever format you want to display. I normally use the date-format help to find out the style :-) SELECT [borrower#], -- use square brackets when you have special characters in the output column name location, ...


1

Thank you @Greg for answering the question. The problem required that the SQL statement fully qualify the columns in the where clause by referencing the tables for the columns. In this case. I needed m_econ_fred_source=attrib_id. The complete solution is: SELECT [update], [value] FROM m_econ_fred_source JOIN s_econ_fred ON m_econ_fred_source.param_id = ...


1

To get only the rows that have at least 2 non-null values in the 5 columns: SELECT * FROM db1 WHERE (var1 IS NOT NULL) + (var2 IS NOT NULL) + (var3 IS NOT NULL) + (var4 IS NOT NULL) + (var5 IS NOT NULL) >= 2 ; To delete the rows that have less than 2 non-null values, we can just use the reverse condition: DELETE FROM db1 WHERE (var1 IS ...



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