New answers tagged query
1
vote
How to query nested unamed jsonb objects and order?
Assuming your values are really JSON arrays, jsonb_path_query_first() with this path expression would do it:
SELECT *
FROM tbl
ORDER BY jsonb_path_query_first(audits, '$[*] ? (@.status == "...
- 152k
0
votes
Accepted
Percentage based scan on a timeseries data
(Discussing a implementation using MySQL)
CREATE TABLE Trades (
trade_time DATETIME(3) NOT NULL, -- millisecond resolution
price DECIMAL(8,2) NOT NULL, -- or possibly something else
...
- 68.8k
1
vote
Accepted
How can I correct this mysql join query and put 1 to many in the same row?
You can first take a row-number, then conditionally aggregate (pivot) over that.
The joins and grouping/partitioning columns are unclear, you may need to modify.
SELECT
grh.*
d....
- 5,788
5
votes
Accepted
Select only rows where all values in a column match a value for an id
Yet another alternative:
# All non-NULL seq_status values per seq_id contain 'A'
SELECT s.seq_id
FROM sequence AS s
GROUP BY s.seq_id
HAVING SUM(s.seq_status = 'A') = COUNT(*);
That works because ...
1
vote
Select only rows where all values in a column match a value for an id
Another option, slightly different from @Charlieface is using HAVING with GROUP_CONCAT. It will return only the seq_id where distinct seq_status is equal to A.
Try:
SELECT s.seq_id
FROM sequence s
...
- 1,165
3
votes
Select only rows where all values in a column match a value for an id
Seems like you just need a conditional count in a HAVING
SELECT s.seq_id
FROM sequence s
GROUP BY
s.seq_id
HAVING COUNT(CASE WHEN s.seq_status <> 'A' THEN 1 END) = 0;
db<>fiddle
- 5,788
0
votes
Select only rows where all values in a column match a value for an id
For MySQL 8.* this can be made with CTEs:
WITH cte AS (
SELECT seq_id, seq_status
FROM sequence
GROUP BY seq_id, seq_status
)
SELECT w.seq_id
FROM cte AS w
LEFT JOIN cte AS z ...
- 4,134
0
votes
Convert Long list of IN clause into a temporary table
A faster solution (no looping, no splitting):
Setup:
DROP PROCEDURE IF EXISTS Spray;
DELIMITER //
CREATE PROCEDURE Spray(IN _table VARCHAR(64),
IN _col VARCHAR(64),
...
- 68.8k
1
vote
Multiple inner JOIN on the same table
I think this is maybe what you want based on what I understood from the tables:
SELECT a.famille_id,
a.iid1,
b.prenom prenom1,
b.nom nom1,
a.iid2,
c.prenom prenom2,
...
- 96
1
vote
Convert Long list of IN clause into a temporary table
In MySQL it is quite hard to split a string, because there is no built in function.
One not so optimal way is create a stored procedure which split the string like below:
DELIMITER //
CREATE ...
- 1,165
1
vote
Applying conditional statement in Oracle DB
As you were already told, data model is wrong.
Anyway, here's what you might try to do.
Sample data:
SQL> with
2 people (id, name, adore) as
3 (select 1, 'Stone' , 'dog' from dual union ...
- 735
1
vote
Applying conditional statement in Oracle DB
Depending on the result of a query from the 'People' table, how can I proceed with a different query to request data further either from 'Dogs' or 'Cats'.
You shouldn't.
The two table structure (...
- 6,593
2
votes
Can Somebody help me understand this query
The below SQL query gets all the ids which have different last names.
No it doesn't.
Why the query in the question doesn't return all the names which have
different last names?
Because it doesn't ...
- 1,165
3
votes
Can Somebody help me understand this query
The SQL given is an example of a correlated subquery.
It is correlated because there is a reference to the outer table inside the subquery.
Some people find this form more intuitive than writing the ...
- 71k
1
vote
Accepted
How to compute the average score for each user, by time interval?
You can check a DB fiddle of the solution here (without a function just raw SQL): https://www.db-fiddle.com/f/fR2JtQKVcQzrnuZ9SfpcNS/0
For the solution, I am providing some dummy data to show some ...
- 959
Top 50 recent answers are included