New answers tagged query
1
One way to approach this problem is by defining intervals, cross joining together the two tables, keeping the rows with overlapping intervals, and calculating the overlap between each interval. The query below does that without paying much mind to performance:
WITH Resources_CTE AS (
SELECT
ResID,
ResName,
ResCnt,
RT - ResCnt IntervalStart,
...
1
PostgreSQL
This can be easily done with PostgreSQL using generate_series
SELECT t.x
FROM generate_series(1,10) AS t(x)
CROSS JOIN LATERAL generate_series(1,4) AS g(x)
ORDER BY t.x;
Here we generate a series [1..10] and then repeat it four times.
Output
x
----
1
1
1
1
2
2
2
2
3
3
3
3
2
An experiment/improvement on Erwin's recursive solution:
-- experiment 3
-- variation on Erwin's recursive function
CREATE OR REPLACE FUNCTION word_permutations_yper(_word text)
RETURNS SETOF text
LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE STRICT AS
$func$
declare
word_length int := length(_word);
BEGIN
IF word_length > 1 THEN
RETURN ...
0
If the table is well-VACUUMed and you had an index on all 4 columns, with metering_point_id and is_consumption leading, then you could get an index-only scan which should be fast, as it shouldn't have to jump around the table at all.
Or if you CLUSTERed the table on the index so that the table rows were in the same order as the index entries, then you would ...
1
This is what I have, and I believe it's wrong
You are correct, and that's because you're still getting to grips with SQL syntax (I've assumed we're talking MySQL here; you didn't actually say).
You can't just "invent" it - you have to take the time to familiarise yourself with it.
SUM points should be SUM( points ) - the braces identify the ...
0
To provide a general answer to this question, I did the following (all the code below is available on the fiddle here).
Just a note before we start. This is a signal lesson about UI design. NEVER, EVER let users enter text (if at all possible)! For example, the currency, if there's a choice, have a dropdown - don't let users enter a three-letter code! ...
answered Nov 25 at 19:00
Vérace - get VACCINATED NOW
26.4k7 gold badges59 silver badges74 bronze badges
0
The problem is that an empty string '' isn't a valid number and thus the cast fails. One way to deal with this is to use nullif() to turn an empty string into a null value:
cast(nullif(split_part("cost", ' ', 2), '') as double precision)
Maybe even combine that with trimming leading and trailing spaces:
cast(nullif(trim(split_part("cost",...
1
It stores the costs of each service a client gets in this format "KES 0.80".
This looks to be two data items stored in a single field.
That breaks very basic, Data Normalisation rules.
If this represents Currency and Value then you should store these in two separate fields (of the correct Data Types) and put the two back together again when you ...
0
Not all your rows contain exactly that format, some may have two spaces between "KES" and the value. eg.:
ghp=> select sum(cast(split_part('KES 0.80', ' ', 2) as double precision));
ERROR: invalid input syntax for type double precision: ""
0
I do not think you need a self-join. LEFT JOIN allows the 'right' table to have no matching row, hence NULLs.
SELECT b.name AS Band,
b.genretitel AS Genre,
g.discreption AS 'Main Genre Description'
FROM music_band AS b
LEFT JOIN genre AS g ON g.title = b.genretitel
Or...
SELECT b.name AS Band,
b.genretitel AS Genre,
( ...
0
If I understand your schema and goal, there are a couple of different ways you can achieve this. This is one way you can get the output you're looking for:
SELECT
FS.id AS submission_id,
FirstName.value AS first_name,
LastName.value AS last_name,
Age.value AS age,
FS.submitted_at
FROM form_submissions FS
LEFT JOIN ...
0
I can't believe that I am able to pull this off by myself. SQL is soooooo hard. Turn out I have to use range to deal with this kind of data.
select * from sessions_workers
join sessions on sessions.id = sessions_workers.session_id
right join workers on sessions_workers.worker_id = workers.id
where (sessions.id <> 1 or sessions.id is null)
and not ...
0
The option was "Text Qualifier". In the export data option, set the "Text Qualifier" to ".
1
It sounds like you want to join the table to itself on the customer_id and purchase_date offest by one day. Give this a try.
select a.purchase_date
, a.customer_id
, case when b.customer_id is null then 'FALSE'
else 'TRUE'
end flag
from test_one a
left join test_one b on b.customer_id=a.customer_id and b....
1
Found an answer to my problem.
SELECT allPacks.id_pack, allPacks.name, allPacks.price, allPacks.user, allPacks.discount, allPacks.preview_url, allPacks.type, allPacks.description,
CASE
WHEN allPaid.status = 'paid' THEN 'Paid'
WHEN allPaid.status = 'pending' THEN 'Available'
WHEN allPaid.status ISNUll THEN 'Available'
END ...
1
You have several syntax error in there
You mix-up simple case and search case syntax. Do refer to documentation
The expression is CommonLastName = 'Unknown', '', 'Null' You can't check for multiple match like that. Use IN ( . . . ) . Also you need to use IS NULL when checking for NULL value
There is an extra comma after ValidNaming
Corrected query
Select
...
2
It can be useful to provide your own names for query blocks in place of the SEL$1, SEL$2 etc generated by the optimiser. It may also be more reliable, if for example your top-level hints refer to sub-blocks by name, but the system-generated numbering later changes due to edits to the query, making nonsense of your /*+ full(e@sel$5) */ hint.
Unhinted query:
...
0
You could aggregate the status2. You would want to keep the max value (in most collations, maybe in all). Your join to users doesn't seem to do anything.
select p.id_pack,
max(CASE
WHEN pa.status = 'paid' THEN 'Paid'
WHEN pa.status = 'pending' THEN 'Available'
WHEN pa.status ISNUll THEN 'Available'
END Status2)
from packs ...
3
The binomial problem can be translated to this simple pseudo-code algorithm:
take the first letter
while more letters, loop
make two copies, one with trailing space
append next letter
end loop
Recursive function
This can be implemented elegantly with a recursive function in any decent procedural language. With PL/pgSQL:
CREATE OR REPLACE FUNCTION ...
1
The Documentation says it's for naming blocks of SQL.
Use the QB_NAME hint to define a name for a query block.
This seems to be more for the person that's tuning the SQL more than it's for the CBO.
Answers
No
I was unaware of it
Use it when your query is very complex and you need help reading The Plan
I'll have to check. but, If the CTE name doesn't show ...
2
No performance impact at all, but it can make it easier to follow a complicated query plan and hint at specific subqueries (ie potential to allow you to make performance improvements easier).
I use it from time to time, mainly when I’m demoing execution plans as subqueries will retain query block names when you add bits.
There is no situation which requires ...
0
This can be achieved using trigger. Based on limited information on the schema of both tables, you may have to create Insert trigger on rejection_reason table which will update the table applications.
Assuming the rejection table has a application_id column which can be used as a link between the two tables.
e.g.
CREATE TRIGGER i_rejection_reason ON ...
3
DISTINCT will remove duplicate result rows, that is rows where all columns are equal.
If you want only one result row per payment_id, there are two options:
Use SELECT DISTINCT ON (payment_id) to return only the first row for each payment_id.
Use GROUP BY payment_id and aggregate the results for each group.
What is the correct solution will depend on your ...
9
I guess that is just a challenge for fun, but here is my solution:
WITH s(s) AS (VALUES ('ABCD'))
SELECT substr(s, 1, 1) ||
string_agg(
CASE WHEN i & (2::numeric ^ p)::bigint = 0 THEN '' ELSE ' ' END ||
substr(s, p + 2, 1),
''
)
FROM s
CROSS JOIN generate_series(0, (2::numeric ^ (length(s) - 1) - 1)::bigint) ...
0
An SQL engine must determine the names and types of all output columns of a query before executing it.
If the output columns are not known in advance and thus cannot be hardcoded into the query, then the problem must be solved in two separate steps:
generate the crosstab query with another query
execute the crosstab query
Or the problem must be changed to ...
1
Your explain plan looks like it's reading from the credits_history table 7 times. In general, it's better to only read from the table once if you can help it. Multiple reads will definitely add a lot of run time to a query.
I would try rewriting it so that you read only once from credits_history. Make the where clause general enough to read all the rows that ...
1
You need HAVING COUNT condition =3 and type in (2,5,7) to produce your expected result.
Try:
SELECT custid
FROM test_tbl
WHERE type in (2,5,7)
GROUP BY custid
HAVING COUNT(custid)=3;
DEMO: https://www.db-fiddle.com/f/qff694udysNgqbyJyFcDzn/7
-1
With SQL Server HandyD's solution works but you will need to add the alias after the DELETE statement or you will get an error.
DELETE t2
FROM [Table2] t2
INNER JOIN [Table1] t1 ON t1.[Name] = t2.[Name]
WHERE t1.[ExecutionDate] < DATEADD(DAY, -30, GETDATE());
AND t2.[Name] NOT IN (
SELECT [Name] FROM [Table1]
WHERE [ExecutionDate] >= ...
Top 50 recent answers are included