New answers tagged query
0
If you need to use a full outer join, use a full outer join. It exists for a reason. In general, though, it should be an uncommon tool in the toolbox.
When designing systems, you generally try to minimize the number of outer joins that you need to do. In this case, it would seem to make more sense to have a general transaction table that both ...
0
You can calculate the overall average rent using a window function, for example:
SELECT RentDuration,
LEFT(StaffName, CHARINDEX(' ', StaffName)-1) AS StaffName,
GroupName,
LineRented,
-- calculate overall average duration:
AVG(RentDuration) OVER () AS AverageRentDuration
FROM RentTransaction AS RT
INNER JOIN Staff AS S ON ...
1
If you are looking for a different solution, you could always unpivot the data, aggregate and then join back on the source table. Here is an example.
Also, thank you so much for including sample table and data to work with, so much easier.
;WITH CTE_Unpivot AS
(
SELECT StudentID, MarkSubject, Mark
FROM dbo.Students AS S
UNPIVOT (Mark ...
0
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID('Comments') AND name = 'IX_CreationDate')
CREATE NONCLUSTERED INDEX IX_CreationDate
ON dbo.Comments(CreationDate)
GO
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID('Posts') AND name = 'IX_PostID_CreationDate_Title')
CREATE NONCLUSTERED INDEX ...
0
Since this was tagged for oracle-sql-developer, I'm guessing you are having problems using SQL-Developer for detecting and solving this common syntax problem.
SQL-Developer highlights potential syntax problems with a yellow squiggly line under the problematic syntax. SQL-Developer will use a magenta color squiggly line for major syntax errors.
Your entire ...
0
Assume you have the following table TAB(name,value)
name value
a 2
a 3
a 2
b 4
b 5
b 5
c 6
c 6
c 6
what are the result sets of the following queries
query 1:
select name, sum(value)
from TAB
group by name
order by name
query 2:
select name, sum(distinct value)
from TAB
group by name
order ...
7
SELECT record_id, rec_date
, CASE WHEN rec_date::time < '08:00' THEN 'Night'
WHEN rec_date::time >= '20:00' THEN 'Night'
ELSE 'Day' END AS indicator
FROM records;
Why?
It's a matter of date-math rather than date-format. You want to do the math correctly and efficiently. The format of 'Day' and 'Night' are not in ...
2
According to docs BETWEEN transforms into:
a BETWEEN x AND y
is equivalent to
a >= x AND a <= y
That means that x must be lesser than y.
Also in the docs you can find there is a BETWEEN SYMMETRIC, that can help in your question.
BETWEEN SYMMETRIC is like BETWEEN except there is no requirement that
the argument to the left of AND be ...
1
INSERT INTO Pairs
SELECT DISTINCT NULL, t1.KeyID, t1.ValueID, t2.KeyID, t2.ValueID
FROM ItemKeyValues t1
JOIN ItemKeyValues t2 USING (ItemID)
WHERE t1.KeyID < t2.KeyID;
INSERT INTO ItemPairs
SELECT t1.PairID, t2.ItemID
FROM Pairs t1
JOIN ItemKeyValues t2 ON t1.Key1 = t2.KeyID
AND t1.Value1 = t2.ValueID
JOIN ItemKeyValues t3 ON t1....
0
OR does not optimize well; don't use that form.
One of these indexes will be optimal; add both:
INDEX(company_id, period_code, paydate)
INDEX(period_code, company_id, emp_id)
{d '2020-05-01'} can be simply '2020-05-01'
You might find this easier to deal with
paydate >= '2020-05-01'
AND paydate < '2020-05-01' + INTERVAL 1 MONTH
COUNT(...
1
As mentioned in the comments, your original problem is the unnecessary semicolon ( ; ).
Code Review
By building the query the way you did, you are introducing SQL Injection Vulnerability. (obligatory XKCD reference). To prevent SQL Injections, use BIND variables.
Most RDBMS allow you to use ? as a place holder. Oracle supports this and Named place ...
0
You can achieve that by using this select:
select ef.employee_number,ef.full_name,ef.agency_code
from Employment_Form ef, HumanResources hr
where ef.employee_number = hr.employee_number
and ef.agency_code <> hr.agency_code;
Here you select all the columns of the Employment_Form table, fetching only those rows with matching employee_number and wrong ...
1
SELECT *, LEAD(time) OVER (ORDER BY time) - time AS delta
FROM RANK_LIST_T
ORDER BY 4;
fiddle
0
# horror film rental(answer)
select cus_dtl.name, cus_dtl.phone, hf_cus.title, hf_cus.rental_date, hf_cus.name
from (select c.customer_id, concat(c.first_name, ',', c.last_name) as name, a.phone
from customer as c, address as a
where c.address_id = a.address_id) as cus_dtl, (select r.customer_id, r.rental_date, hf_inve.title, hf_inve.name
...
1
Assuming all the columns are compatible:
SELECT * from table1
UNION ALL
SELECT table2.* from table2 left join table1 using (customer_id, date)
where table1.customer_id is null
If not all the columns are compatible, you will have to do a bit of tedious manipulation of the select lists, rather than using *.
0
I have not knowledge working with Vertica but you can try using If exists like in SQL Server
If exist (Select 1 FROM sys.objects Where type = 'U' and name in ('YourTableName1', 'YourTableName2'))
Begin
Select * From Public.Table1
Union
Select * From Public.Table2
End
0
The folling query will provide one column, where each column name of the respective table is displayed in a separate row. The order seems random.
select column_name
from information_schema.columns
where table_name = 'questionnaire'
and table_schema = 'fi';
1
Something like this should work (it's hard to verify without a fiddle):
WITH temp AS (
SELECT code, sma_9, sma_26, rsi,
RANK() OVER (PARTITION BY code ORDER BY date DESC) AS rank
FROM Stock_price_Table
)
SELECT t1.code
FROM temp AS t1
INNER JOIN temp AS t2
ON t1.code = t2.code
AND t1.rank = 1
AND t2.rank = 2
WHERE t1.sma_9 >= t1....
2
Being on the Standard Tier (S0) is causing your queries to be throttled significantly, which is affecting the total runtime. Here are the times for all 5 statements in the batch, as viewed in Sentry One Plan Explorer:
As you can see, most all of the queries have a duration that's much longer than CPU time. This often means the queries are waiting on some ...
1
Avoid Row_Number because first it process whole table then again you select row
where rn=1 for each group
DECLARE @Customer_Channel_Branch TABLE (
Customer_Num VARCHAR(10)
,Channel_Num INT
,Branch_Code INT
,Issue_date INT
)
INSERT INTO @Customer_Channel_Branch
VALUES (
'x'
,1
,1000
,1990
)
,(
'x'
,2
,...
0
I found a query to solve this :
first of all I should say that I change the structure of the first table and addedChannel_gnrl_Code the first table Customer_Channel_Branch and then used the below query .
select t2.customer_number ,
t2.Channel_Num,
t1.Branch_Code,
t1.Channel_gnrl_Code
from (
select b.*
from (select a....
1
Create a proper timestamp from the two columns:
select *
from airquality
where validdate + validtime = (select max(validdate + validtime ) from airquality)
This assumes that validdate is corrected defined with the data type date and validtime is correct defined as a time column.
Top 50 recent answers are included
