New answers tagged

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