New answers tagged query
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
How to make MySQL query performant WHERE one column uses greater than and one column uses less than?
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
