Hot answers tagged

3

As Lennart has suggested, use a minimal example and see what happens. create table accounts(account_id int primary key, sales_rep_id int); create table orders(order_id int primary key, account_id int); insert into accounts values (1, 321500), (2, null), (3, 2000), (4, 3000); insert into orders values (1, 1), (2, 1), (3, 2), (4, 2), (5, 2), (6, 3); There ...


2

Using a Foreign Data Wrapper is generally preferable to using dblink. Once you set up the link and import the table definition, then you can write the query just as if the table were local. Your English description of the problem doesn't make any sense to me, so I can't tell you if the specific query you wrote does what you want.


1

The UPDATE syntax differs a lot from DBMS to DBMS, especially when there are joins. For Oracle this would work: update crs_regd r set marks = marks + 5 where marks < 50 and exists ( select 1 from crs_offrd o where r. crs_cd = o. crs_code and o. crs_name = 'DBMS' ) ;


1

Use grouping to get the count per country, and a window function to get the rank: WITH t1 AS ( SELECT CASE WHEN Country IN ('France', 'Germany') THEN 'Europe' WHEN Country IN ('China', 'Japan') THEN 'Asia' WHEN Country IN ('Egypt', 'South Africa') THEN 'Africa' END AS Region, GenreName, ...


1

Joining (or the expression in SELECT) looks similar to: FROM #A FULL OUTER JOIN #PS ON #A.ACCOUNT_NUMBER = #PS.ACCOUNT_CODE__C AND ( (#A.ID = #PS.I AND INSTR(#A.Id_Type, 'I') > 0) OR (#A.ID = #PS.T AND INSTR(#A.Id_Type, 'T') > 0) OR (#A.ID = #PS.C AND INSTR(#A.Id_Type, 'C') > ...


1

I fixed it by adding the feed_id field to the ORDER BY clause like that: mysql> EXPLAIN SELECT a.id FROM articles a WHERE a.feed_id IN (6826,6827) AND a.date < 1564469723.424363 ORDER BY a.date DESC, a.feed_id DESC LIMIT 20; +----+-------------+-------+-------+-------------------+--------------+---------+------+------+----------------------------------...


1

This is yet another installment of the MySQL sql_mode = only_full_group_by saga. <rant> The problem is that MySQL's default (till 8) will return results which, depending on the order of the records being INSERTed into the table (and/or PRIMARY KEY ordering), will vary and be correct or wrong DEPENDING. This is absolutely the worst sort of difficult to ...


1

Overall, your query will work as you describe, but I wanted to show you a possible re-write that looks cleaner, as well as to point out some possible issues that you may want to address first. The use of DISTINCT with GROUP BY is redundant. Are Account_Number and Account_Code_C the same datatype? Is the AMOUNT and VALIDATED the same datatype? Are they ...


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