New answers tagged

0

See groupwise max; it includes "top-N".


2

I see some issues: category_pointers is missing a unique key on its natural primary key. Try using (category, isbn) as the primary key. It may be helpful to have the reverse index ('isbn', 'category'). This query can run strictly on the index. category_pointers has a surrogate key for its primary index. I would drop it and use the natural key (category,...


2

Logic is very simple. We are taking two instance of same table; second in a subquery. We pick first salary of main table and compare it against all salaries in subquery table to get a count of salaries greater than the salary in main table under consideration. If count is N-1; then it implies that salary in main table is Nth max salary because there are N-1 ...


0

It sounds like you're talking about a self-referential relationship, especially with the inclusion of "manager" titles. This is a common pattern, especially with employee tables. While they may implement it differently the main RDBMS vendors typically offer recursive capabilities. This means that with a special syntax you can easily query this kind of a ...


-3

select * from (select rownum as rn,salary from employees order by salary desc )x where x.rn = 5 -- nth highest salary


4

I did manage to do this through SQL. However, I think that this is a task that is far better better suited for TRIGGERs - i.e. if a record insertion/deletion is made on the project_map table, then a TRIGGER should fire adding +/- 1 to the relevant company's project field. I gave this a +1 because it was trickier than it seems initially. Giving the DDL was ...


0

One SQL sentence can't have variable fields per row; every row has same fields. But every field can have or haven't (null) value. If you want that in pure SQL you can group_concat the fields of wp_usermeta, like: SELECT use.name, use.email, group_concat(concat('{', meta_key, '##', meta_value, '}')) AS metadata FROM wp_users use INNER JOIN wp_usermeta met ...


14

They are different. In the first option you get 2 times Table2 into your query. Once as t2 and once as t3. Both have a different content and you must put them somehow back together. To me this is more an OR instead of an AND. In the second option you get only the Table2 rows that meet both criteria. Suppose you have Table2 with the following content: | ...


0

Before version 5.6, the optimizer did not know how to automatically add an index to a subquery. Temp tables are (often) the workaround. CREATE TEMPORARY TABLE o ( PRIMARY KEY(customer_relacional_id) ) ENGINE=InnoDB SELECT customer_relacional_id, COUNT(*) AS orders, MIN(created_at) AS created_at FROM order_2016 ...


1

Would it be better to use a NOT IN or NOT EXISTS query? Yes, you would most likely benefit from an IN() (or EXISTS()) instead of the join and IS NULL because you're effectively only using the tableREPORTS data as a filter. This should work for what you need to do: SELECT [Call_Num], [Call_Cust_Num], [Call_Cust Name], [Call_Status]...


0

How about somethig like this : SELECT a.id , user_login as 'last_active_user' , discussion_sub , todo_list_title , todo_title , count(comment) , MAX(CAST(comment_posted AS CHAR)) FROM abc_discussions a LEFT JOIN abc_todo_list b ON comment_table_type = 'todo_list' AND comment_table_id = b.id ...


1

Sounds like you're trying to insert rows to a table that already exists, in which case you'll need something like... INSERT INTO new_records SELECT * FROM new_table t JOIN new_record_ids r ON(r.id = t.id) --this assumes the result of the select has the same column layout as new_records... --which seems unlikely. replace (SELECT *...) with (SELECT column1, ...


0

why you need use subqueries ? You can use joins for get data SELECT u.name AS user, d.name AS departement, m.year FROM main AS m INNER JOIN department AS d ON m.departement_id = d.id INNER JOIN users AS u ON m.user_id = u.id ; I created a sample fidle http://sqlfiddle.com/#!9/74a4e2/1


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

Try this SELECT a.descendant_id , CASE a.path_length WHEN 0 THEN null ELSE a.ancestor_id END ancestor_id , CASE a.path_length WHEN 0 THEN null ELSE a.path_length END path_length FROM webineh_prefix_nodes_paths AS a JOIN ( SELECT descendant_id, MIN(CASE path_length WHEN 0 THEN 1000 ELSE path_length END) min_path FROM ( SELECT a.* ...


1

"to enforce the fact that each of the lifecycle steps must happen in a particular sequence." If you want to enforce referential integrity of an item's life-cycle, then I would recommend using essential integrity enforcing tools built into the product, namely constraints. Having independent keys is good for quickly getting a record specific to a table, but ...


2

That should give you what you want: SELECT UT.Name, T1.*, T2.membership_no as 'Unmatching in membership_no Second table' FROM T1 INNER JOIN UT ON T1.user_id = UT.user_id INNER JOIN T2 ON T1.user_id = T2.user_id and T1.membership_no != T2.membership_no


0

Your view will join all the tables involved in the view definition in order to create the required view. First, of course, you should check that your tables are properly indexed so that the joins are as efficient as possible when creating the view. However, joining 25 tables with 30 inner joins is a relatively expensive undertaking, resulting in your case ...


1

Unless mysql is different from mssql I don't think that is the correct query SELECT c.id AS 'id', count(o.customer_relacional_id) orders, o.created_at FROM customer c LEFT JOIN order_2016 o ON (o.customer_relacional_id = c.id) GROUP BY o.customer_relacional_id ORDER BY o.created_at DESC this turns it into a regular join GROUP BY o....


0

You can probably improve your Select by doing an early aggregation, i.e. group before the join: SELECT c.id AS 'id' ,COALESCE(o.orders, 0) AS orders ,o.created_at FROM customer c LEFT JOIN ( SELECT customer_relacional_id, COUNT(*) AS orders, MIN(created_at) AS created_at -- or MAX FROM order_2016 GROUP BY customer_relacional_id ) AS ...


5

The issue of the amounts that are multiplied is easy to solve. You just have to use a derived table that does the calculation (group by) first and then join that (derived table) to the other one, that stores the hierarchical structure. The derived table to use: ( SELECT userid, SUM(amount) AS sele_descendant_amount FROM webineh_user_buys ...


1

So from what I can infer, your desired output is a column of all the dates from all 3 tables, with the 3 tables joined onto it. So something like Select dates.date, tbl1_col, tbl2.col, tbl3.col from (select distinct Date from table1 union select distinct date from table2 union select distinct date from table3 )dates --Now we have ...


2

I looked at the table structures and as Aaron had previously pointed out TimeSheets does not have any indexes and the other tables do not have any nonclustered indexes. I created the two databases and their tables and generated the estimated execution plan and I get the same execution plan that you are currently getting. I created a clustered index on ...


2

SQL is not making up rows. If that join is producing more rows than you expect then figure it out. Are you sure ts.JobSubTypeId is a task code? That name does not sound like a task code. Try this - it will show you were the volume is coming from In your query each count is a row -- this is the raw count from Timesheets SELECT ts.JobSubTypeId, count(*...


0

SELECT SUM(...) FROM ... JOIN ... usually computes an inflated value for SUM. This is because the JOIN inflates the number of rows over which SUM applies. See if you can write something like SELECT ( SELECT SUM(...) FROM ... WHERE ... ) -- a correlated subquery FROM ... If you want more discussion, show us your code.


-3

Check this out; SELECT [subject_id],[subject_name] FROM [relationship_table] ON subject_id = subject_id JOIN [subject] ON [subject_id] = [relationship_table_id] WHERE subject_id = [Any_key]


2

mdbo and @ are hints it's MS Sql Server. Use a splitter table valued function of choice, Jeff Moden's one http://www.sqlservercentral.com/articles/Tally+Table/72993/ performs very good. So select * from Test.dbo.Test1 a INNER JOIN @TestTable T on T.Name in (select item from [DelimitedSplit8K](a.Name, ',')) But may I say the best way to solve the ...


1

SELECT A.id, BFather.codigo, BFather.Name, BSon.codigo, BSon.Name FROM TABLEA A JOIN TABLEB BFather ON A.codigoFatherTABLEB = BFather.codigo JOIN TABLEB BSon ON A.codigoSonTABLEB = BSon.codigo AND BSon.idPadre = BFather.id



Top 50 recent answers are included