New answers tagged

0

Plan A: Create temp table with num and max(id). 400K rows Chunk that table to do only 1000 deletes at a time; there would be a JOIN in the DELETE to the temp table. More on Chunking for DELETE. Plan B: You need to keep 60% of the table, correct? So, it may be faster to rebuild the table with only the desired rows. CREATE TABLE new LIKE activity; ...


2

You have to do with users the same thing you're doing with days: WITH days AS ( SELECT generate_series(current_date-7, current_date, '1d')::date AS day ), eves AS ( SELECT user_id, created_at::date AS full_day, COUNT(*) as evs FROM logged_events WHERE logged_events.created_at >= current_date-6 GROUP BY user_id, full_day ), ...


0

I figured it out and got it confirmed by the lecture, hopefully this will help someone else: i) The key is {A,B,E}. This means that FD1 and FD3 are both partial — but note that FD2 is not, because {D,E} is not a partial key. The decomposition creates one table for each of the FDs, and one for the PK FD: R1(A,B,E) R2(A,B,C) R3(B,D) - ...


4

You have at most 1000 rows from the natural join of R1 and R2 because there are two possible cases: either 1) all the values of R1.C are present in R2.C (i.e. R1.C is a “foreign key” for R2), or, 2) there are values in R1.C not present in R2.C. In the first case you have exactly 1000 rows in the join, since the for each row of R1 there is exactly one row ...


1

Two remarks: This is called an associative table, not pivot. You already do a join, you just use the old syntax: SELECT * FROM tbl_profile_follow A JOIN tbl_profile_follow B ON (B.profile_id = A.profile_id AND B.followed_id = A.followed_id) AND (B.profile_id = A.followed_id AND B.followed_id = A.profile_id) This is another way ...


2

The query can be made easier: select least(profile_id,followed_id), greatest(profile_id, followed_id), count(*) from tbl_profile_follow group by least(profile_id,followed_id), greatest(profile_id, followed_id) having count(*) > 1; Using this approach you can also create a unique index on the table create unique index idx_unique_pair on ...


5

There is no difference: your two examples are completely equivalent but using different versions of SQL syntax. The database engine will handle them in exactly the same way. Your first example is using an explicit join and is the preferred syntax these days. It was introduced in the SQL-92 standard and is supported by pretty much every SQL-style query ...


2

There are quite a few ways to achieve your desired results. Undeterministic methods (in the event that many rows in table 2 match one in table 1) UPDATE T1 SET address = T2.address, phone2 = T2.phone FROM #Table1 T1 JOIN #Table2 T2 ON T1.gender = T2.gender AND T1.birthdate = T2.birthdate Or a slightly more concise ...


0

Making the correct join is key. UPDATE table1 INNER JOIN table2 ON table1.birthdate = table2.birthdate AND table1.gender = table2.gender SET table1.address = table2.address, table1.phone = table2.phone ; As ypercubeᵀᴹ has mentioned, if you have duplicates in the second table then an arbitrary record will be picked for the update..


0

Here is a query that should give you what you are looking for. This is Oracle SQL syntax, but may work in other databases as well. SELECT dau.datetime, count(*) FROM DAU WHERE EXISTS (select 1 from Payer WHERE Payer.user_id=DAU.user_id AND Payer.payment_timestamp BETWEEN trunc(dau.datetime)-90 AND trunc(DAU.datetime)) GROUP BY dau.datetime ORDER BY ...


0

Thanks all, I suppose there's multiple ways to skin the cat on this one. @spthorn's didn't work on the production data set since it was too big. I found that similar to ypercube's suggestion, the LEFT JOIN works much more efficiently. My final query was slightly different than suggested but will add it here as a third (or fourth) option: SELECT m.id, ...


2

@spthorn's answer is correct but IN gives sometimes unexpected results when the columns involved are nullable. If you want to avoid such surprises, it's better to use NOT EXISTS or the LEFT JOIN / IS NULL structure you already have. The change you need is minimal. Just add the s.status_message = 'Delivered' condition in the joining ON clause: SELECT ...


1

Something like this: SELECT id FROM main WHERE id NOT IN ( SELECT main_id FROM status_history WHERE status_message IN ('Delivered') ) will do the trick.


0

You can use NOT IN in such situations, or LEFT JOIN, as follows (not sure what you mean by [CourseID] in your select list, though): NOT IN This may not be efficient for very large numbers of students. Select [StudentID], [StudentName], [StudentEmail], 11 AS [CourseID] -- This is the missing course ID, specified as a constant from ...


0

Assuming the following table structures and that you want to return students that have NOT registered for CourseID 11...you need to change your LEFT JOIN to include CourseID = 11 and then add CourseID is NULL in the WHERE clause to pull the students that are not registered for that course. In your query having CourseID = 11 in your WHERE clause limits the ...


0

I have one workaround going which is making a seperate query for each 1:n-relation which connects DNS and SUM(foo) and one query which has the results of all the queries. Then in the little ERM for the query I connect every DNS entry of the queries. For 1:1 relations in the DB I can just use the standard table in the overallQuery. still looking for other ...


1

EXISTS don't actually return any rows. They check for the existence and then move on. Your problem is probably a duplicate caused by the JOINs to the second two tables. Try running this: SELECT p.FirstName, p.LastName, e.JobTitle, COUNT(1) AS Cnt FROM Person.Person AS p JOIN HumanResources.Employee AS e ON e.BusinessEntityID = p.BusinessEntityID ...


1

When doing your last test trying to pop out the errant row with an except, you need to do a select of all columns (*) not just the few you want. This will almost certainly reveal something. Based on the table name it will likely be that an employee was in one department and then moved to another department, and so with the joins query you were accidentally ...


3

Here is a solution that uses CROSS APPLY to find the value for the latest quarter that ends on or before the daily date. If your table is indexed by (ID, Date), this query will be quite efficient with a one-row seek to look up the quarterly value for each daily date. This solution also does not require a calendar table and makes no assumption about the ...


2

Since you have defined quarters using the traditional calendar definition, you don't need to store the "end" of a quarter anywhere, particularly once for every single day in a ~90-day period. (And this could be dangerous if the underlying data is ever expanded to include time, as you may lose all data from the last day of the quarter except that stamped at ...


1

A date table would be very useful in constructing your query. Look into how to create a data table. Here is a quick video on that, https://www.brentozar.com/archive/2014/12/simply-must-date-table-video/ This query uses a very limited date table to list the Quarter start and end dates for the date you have listed. A real date table would have many more dates ...


3

Using the PIVOT clause, you can achieve the result like this: SELECT Vehicle_ID, Doors, [Engine Size], [Tire Size], [Color] FROM dbo.YourView PIVOT ( MAX(Value) FOR Property IN (Doors, [Engine Size], [Tire Size], [Color]) ) AS p ; Now, to join the results to your main vehicle table, you can use the above as a derived table (same as ...


1

The any Sql DBMS fast runnig pivot is just (MS SQL, you may need replace [] with "" or something for other Sql DBMS) select Vehicle_ID ,max(case Property when 'Doors' then Value end) as [Doors] ,max(case Property when 'Engine Size' then Value end) as [Engine Size] --... from theView group by Vehicle_ID Which MS SQL abbreviates to select ...


1

Answering my own question because I found an answer on StackOverflow that was immensely helpful: http://stackoverflow.com/a/1343174/123729 Using the logic in the question above, the query that works for me doesn't need a PIVOT operation, just a GROUP BY and MIN (or MAX) with a CASE statement, like so: Select Vehicle_ID, Min(Case Property When 'Doors' ...


4

Seems like you need all combinations of products and customers (so a CROSS JOIN between the two tables) and then a correlated EXISTS subquery. This could either be a correlated subquery in the SELECT list: SELECT c.customer_id, p.product_id, CASE WHEN EXISTS ( SELECT * FROM "order" o WHERE ...


2

Provided customer_id and p.product_id are primary keys in customer and product select c.customer_id, c.name, p.product_id, nvl((select 'Y' from dual where exists (select null from order o where o.product_id=p.product_id and o.customer_id=c.customer_id)),'N') order_stat from customer c full outer join product p on 1=1


4

You want to filter out groups of rows rather than individual rows. That is, you want to keep only the groups that have ue.user_id = 1. Therefore use HAVING, rather than WHERE or ON, to add that condition, because HAVING is used for group filtering: SELECT SUM(owe) FROM ( SELECT (expenses.amount/count(*)) AS owe FROM expenses ...


2

You can't directly without drawing in all the tables. You would need to first pick out the table name needed then use ad-hoc SQL or call a specific procedure per table to get the data out. Drawing in all the tables as follows would work: SELECT ac.carID, ac.type, ac.commonAttribute1, ac.commonAttribute2 , ct.specialAttributeA , ...


1

try this - you might get the best of both select manyfield from ( select manyfield , row_number() over (partition by t1.id order by t1.timestamp desc) as rownum from inserted left join sometable t1 on inserted.id = t1.id ) tt where rownum = 1 or t1.id is null


0

LOB Rules and Restrictions ... You cannot specify LOB columns in the ORDER BY clause of a query, the GROUP BY clause of a query, or an aggregate function. You cannot specify a LOB column in a SELECT... DISTINCT or SELECT... UNIQUE statement or in a join. However, you can specify a LOB attribute of an object type column in a SELECT... ...


2

This might run faster due to "lazy evaluation". Note that you want to fetch some large columns, yet thousands of rows need to be looked at before deciding which 10 are desired. Instead of gathering all the columns needed, let's get just the PRIMARY KEYs, then reach back into posts only 10 times to get the bulky columns. Note that bulky columns are stored ...


1

Let's say that your tables are called (in order of you mentioning them): Segment, FK, JC_type_A, JC_type_B, etc The main problem that I can see is that you don't know which junc_type will be for which junc. So you will have to left join back to their respective tables and select the one that is not null in your select statement. Something along the ...


-1

I hope I am not reading this wrong but if you want the top 10 most recent posts of a single user wouldn't it be better to run your query on the relations table and join the posts to that query? You would restrict your search to only the posts for the signle user you are attempting to look up then sort the data. So it would look like this: SELECT p.id, ...


4

ORA-00913 too many values Cause: The SQL statement requires two sets of values equal in number. This error occurs when the second set contains more items than the first set. For example, the subquery in a WHERE or HAVING clause may return too many columns, or a VALUES or SELECT clause may return more columns than are listed in the INSERT. Action: ...


2

With your datasets, MySQL has to obtain those 450,000 records from posts (in 1000 little chunks from each matching source_id), sort it, and then return the top 10. It is a costly exercise. You could resort to using a stored procedure, and accumulate results going back in time, say daily or weekly, looping until obtaining at least 10 records, and then ...


2

You can make use of the FOR XML PATH() construct to create the list of features. For instance: USE tempdb; IF OBJECT_ID('dbo.Features') IS NOT NULL DROP TABLE dbo.Features; IF OBJECT_ID('dbo.Versions') IS NOT NULL DROP TABLE dbo.Versions; GO CREATE TABLE dbo.Versions ( VersionID INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY(1,1) , ...


0

Start by changing IN ( SELECT max... ) to = ( SELECT max... ). Change f.Food_name to ( SELECT Food_name FROM Food WHERE Food_therm_id = t.Therm_id ) AS Food_name and get rid of the LEFT JOIN Food ... (I am unsure whether the LEFT JOIN before JOIN is causing trouble.) If those suggestions do not speed it up enough, then... Please provide EXPLAIN SELECT ...


2

What you are looking for is a 0-many relationship. This is usually referenced by a third table. Publication_Scientists : Publication_Id (int) | Scientist_Id (int) with 1:1 relationships back to the other two tables. Preferably using something like an enforced foreign key.



Top 50 recent answers are included