New answers tagged

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 == "...
user avatar
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 ...
user avatar
  • 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....
user avatar
  • 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 ...
user avatar
  • 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
user avatar
  • 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 ...
user avatar
  • 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), ...
user avatar
  • 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, ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 (...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar

Top 50 recent answers are included