New answers tagged

0

Okay, the performance here is being limited by the index of "urlShort" even though the field isn't being updated. Another reason to normalise the schema so that this is no longer necessary.


2

Your query can be simplified (regardless of SQL flavor) not to use an inner query (subselect). This is not tested, but it should be closer to what you need: SELECT tPLAN.Jahr, tPLAN.Monat, tPLAN.Land_ID, tPLAN.Kanal, T1.tPLAN, tPLAN.Planstand, tZ.Zeitraum, T2.IST_ITEMS_PER_HC_EXCL_EASY FROM t_Plan_Items_per_HC tPLAN JOIN t_PLAN_ZEITRAUM tZ ON ...


0

The following will get you the assignment answer. There are many ways to solve this, and it is not necessarily bad to group several times in sub-queries. Most RDBMS engines will be able to optimize your query (some) and performance is likely not an issue for this particular question. SELECT TOP (1) Department ,SumSalary ,AvgSalary FROM ( SELECT ...


2

This query SUM all the distances between 6 and 7: SELECT AssetId, [Event], GeofenceId , DistanceCoveredK = SUM(DistanceCoveredK) , TimeSpentDuringVisitSeconds FROM ( SELECT AssetId = vm.iAssetId , [Event] = c.eEventCode , DistanceCoveredK = p.sptGeoLocaitonPoint.STDistance( LEAD(p.sptGeoLocaitonPoint) OVER(PARTITION ...


1

SELECT a, b ... GROUP BY a is asking for trouble. You may have been 'lucky' in 5.5 and 'unlucky' in 5.6. The problem is that any value of b can be shown for each 'group'. Sure, there are cases where a given a maps uniquely to a given b (eg, in a normalization table), but that does not feel like the case here. To see that there is a problem: SET ...


1

Another way to rewrite the query, without having 2 correlated inline subqueries is to have instead one LEFT JOIN in the FROM clause. The problem is that MySQL does not have LATERAL joins so it looks a bit clumsy and is quite confusing to get it right: SELECT *, topics.createdate AS TopicCreateDate, lastpost_user.username AS LastPost_UserName, ...


3

The cause of the problem was identified by @Phil in the comments: Probably because it's nested too deep You have 2 layers of nesting and the reference of table e cannot "see" through these 2 layers in MySQL. Correlated inline subquery can usually be converted to derived tables and then LEFT joined to the other tables in the FROM clause but they have ...


3

Perhaps it is easier to see with the aid of some rewrites. Your first query can be rewritten to (using the same alias at different levels makes it a bit difficult to understand): select * from parent p1 where exists ( select 1 from parent p2 join child c on c.parentId = p2.id where p2.age > 50 and p1.id = p2.id ) Assuming id is unique ...


1

why do you want to save a join? this is efficient this assume not duplicate in parent select distinct p.* from parent p join child c on c.parentId = p.id and p.age > 50 No join select p.* from parent p where exists (select 1 from child c where c.parentId = p.id) and p.age > 50 select p.* from ...



Top 50 recent answers are included