New answers tagged

0

JSON is always tricky and you have to search a lot of tutorials to get the hang of it. But you can use normalized table instead of JSON. CREATE TABLE tab ( `id` INTEGER, `name` JSON ); INSERT INTO tab (`id`, `name`) VALUES ('1', '{"vals":[{"id":123,"name":""}],"additional":"text"}'), ('2'...


1

That's as simple as adding the tables you are joining to the FROM part of your query. select firstname, middlename, lastname from table_c, table_b, table_a where table_b.licno = table_a.licno and table_c.employeeid = table_b.employeeid; The statement can only join information from tables ...


1

Your syntax is wrong you need parenthesis around the value(s) Please remember not to post Images for data, queries and errors CREATE TABLE table1 ( id VARCHAR(50) ); INSERT INTO table1 VALUES ('PL4418.01301006-H01-D-03013O322241'); INSERT INTO table1 VALUES ( '04l801301006-I401•0013ccO27I0'); INSERT INTO table1 VALUES ( 'P104I8.013016-H0l-...


0

A views can be selected like a table. If you need more columns, simply add them after the SELECT and with comma as a delimiter SELECT [InitiatedDate] FROM dbo.calldetail_viw. WHERE [DNIS] = '/5555' AND [InitiatedDate] >= '2020-09-11 13:00:00.000'


0

Group by Header and Subheader and take the sum like below SELECT Header, SubHeader, Sum(Regular), Sum(Irregular), Sum(FRegular), Sum(FIrregular), Sum(MrRegular), Sum(MrIrregular) FROM (SELECT Header, SubHeader, Type, 'F'+Type AS FType, 'Mr'+Type as MType, 'Ap'+Type AType, ...


0

Results coming from the Query cache take about 1ms, regardless of the query. The QC is simply a hashed lookup of SQL --> resultset. On busy production systems, turning off the QC usually helps overall performance. This composite index would speed up that query: INDEX(col4, col8, col3) -- col3 in 3rd place


0

It's a bit hard to follow your sample, but something along the lines should do it: select to_jsonb(main)||ch.data from main_table main join ( select main_id, jsonb_agg(to_jsonb(child)) as data from child_table child group by main_id ) ch on ch.main_id = main.id If you want everything as one gigantic JSON array, use select jsonb_agg(...


0

Having two "ranges" is not easy to optimize. This may help: INDEX(foo_id, enabled, start_time, end_time) INDEX(foo_id, enabled, end_time, start_time) (As you said...) The optimizer will pick between those two "composite" indexes based on statistics that may tell it which range is shorter. It will quickly check through the two = tests (...


2

Such an approach makes sure you don't get too many duplicates as your query does (so you removed them with a distinct in line #4; without it, you'd get 12 rows as a result): SQL> with customer_info (cust_id, cust_name, cust_address) as 2 (select 1, 'A', 'Tehran,Tehran' from dual union all 3 select 2, 'B', 'Rasht,Tehran' from dual union all 4 ...


1

The query can be next: SELECT -- here we concatenate articles by tags count GROUP_CONCAT(ArticleID ORDER BY cnt DESC) FROM ( -- here we calculate different tags cout per article SELECT ArticleID, COUNT(DISTINCT Tag) cnt FROM TagMap WHERE Tag IN('Tag1', 'Tag2', 'Tag3') GROUP BY ArticleID ) t; Working example here: PHPize.online


1

This would helpful, Give a try once SELECT GROUP_CONCAT(ArticleID) ,COUNT(1) AS CNT FROM (SELECT Tag ,ArticleID ,COUNT(1) AS CNT FROM TagMap Tag IN (...) GROUP BY Tag ,ArticleID) tmp GROUP BY Tag ORDER BY cnt


0

The table now has two rows: ( 1, 'test' ), ( 3, 'test2' ) ... I expect and want ( 1, 'test' ), ( 2, 'test2' ) Your expectation is incorrect. Your desire requires explanation. Sequence-generated values are guaranteed to be unique, not sequential. Further, you should not care what their actual, numerical value is, merely that each is different from every ...


3

This is Working As Designed™. Firstly, serial data types are not "real" types, they are just syntactic sugar for integer columns populated from sequences. Then about sequences the manual says so: To avoid blocking concurrent transactions that obtain numbers from the same sequence, a nextval operation is never rolled back; that is, once a value has ...


1

Basically you want this. The problem is that john has 2 user_ids 1 and 6 Else i would put the user_id also in the GROUP BY AND if you only the highest 3 You have to add also LIMIT 3 A MySQL 5.x Solution to your problem CREATE TABLE sumprice ( `id` INTEGER, `name` VARCHAR(4), `price` INTEGER, `email` VARCHAR(9), `user_id` INTEGER ); INSERT INTO ...


3

Use a HAVING clause: SELECT user_id ,(max(ts) - min(ts)) as tsdiff FROM analytics GROUP BY user_id HAVING max(ts) - min(ts) > interval '1 day' See: https://www.postgresql.org/docs/current/tutorial-agg.html


0

Without knowing where TypeId is coming from this won't be 100% complete. It looks like there may be an error in the data model, but I understand these are often abstractions/homework questions so the parent entity might not exist. You need to Cross Join TypeId and InvtId to create all possible combinations, then left join ContractType: SELECT tp.TypeId ,...


1

You have two ways of doing this (see the fiddle here): CREATE TABLE ex ( id INTEGER AUTO_INCREMENT PRIMARY KEY, value VARCHAR(10) NOT NULL ); Populate the table: INSERT INTO ex (value) VALUES ('example1'), ('example2'), ('example3'); And then the "magic sauce"... SELECT * FROM ex ORDER BY field (id, 1, 3, 2); Result: id value 1 example1 ...


Top 50 recent answers are included