Tag Info

Hot answers tagged

15

Whether the two queries you posted are logically equivalent is irrelevant; you shouldn't use either of them. I will try to steer you away from a couple of things: Whenever possible, try to avoid applying functions to columns. It's always as good, and mostly better, to keep those calculations against constants and not columns - this can destroy SARGability ...


12

Another way to avoid temp tables would be this: ;WITH tmpTest AS ( SELECT Num as [UserId] , Row_Number() OVER (ORDER BY (SELECT 1)) as [RowNo] , COUNT(*) OVER() AS Quant FROM dbo.StringToNumSet('2,3,1', ',') ) SELECT * FROM tmpTest as T1 INNER JOIN ( SELECT Top 10 Id , Row_Number() OVER (ORDER BY ...


8

I would use the following sargeable query: SELECT * FROM MyTable WHERE LogInsertTime < DATEADD(DAY, -7, @DateTime) The reason: I believe that the result of @DateTime-7 is not documented. Even if it just happens to be equivalent to DATEADD(DAY, -7, @DateTime), it may break in a later release.


6

Yes, you can do this without any temp tables: with w as (select usr_id, row_number() over (order by usr_id) as usr_ordinal from usr) select record_id, ( select usr_id from w where usr_ordinal=1+(record_ordinal%(select count(*) from w)) ) as usr_id from ( select record_id, row_number() over (order by ...


6

They are not equivalent. Records that are 7 days full days ago but are before the current time of day will only be returned in query #2: When comparing days using the DATEADD function, it does not take the time part into consideration. The function will 1 if the you are comparing Sunday & Monday regardless of the times. Demonstration: DECLARE @MyTable ...


5

The two scripts in RThomas' answer are both useful. You could also use GROUP BY, which gives a similar advantage to RThomas' methods, but keeping a similar form to your original query. select country from Users inner join countries on users.CountryID=countries.CountryID GROUP BY countries.CountryID, countries.country; The reason why you group by ...


3

To find rows where at least three out of four of those columns match you can use. SELECT D1.ID, D2.ID FROM DATA D1 JOIN DATA D2 ON D1.SSN = D2.SSN AND D1.ID > D2.ID AND 2 <= CASE WHEN D1.FNAME = D2.FNAME THEN 1 ELSE 0 END + ...


3

The Outer Apply is working like a Left Outer Join so when no row is returned by the Outer Apply a row filled with NULLs is returned. I would question the need for the Outer Apply but placing a Coalesce(GIVE_BACK1.CLUB_TOT,0) in the SELECT list would fix the immediate issue.


3

If you're not averse to having a function do the dirty work, this helps make the statement cleaner: CREATE FUNCTION LocalDateFromUTCTime ( @UTCDateTime datetime ) RETURNS datetime BEGIN DECLARE @diff int; SET @diff = datediff(hh,GetUTCDate(), GetDate()); RETURN DATEADD(day, DATEDIFF(day, 0, DATEADD(hh, @diff, @UTCDateTime)),0); END You ...


3

You could get that same result set with the following: select country from countries where exists (select countryid from users where users.countryid = country.countryid) OR select country from countries where countryid in (select countryid from users) Neither of these use distinct but other than that I don't know how much "better" ...


3

The subquery works; here's how you would do it without a subquery: SELECT `entity_id`, SUBSTRING_INDEX(GROUP_CONCAT(`date` ORDER BY `date` DESC), ',', 1) AS last_log_date, SUBSTRING_INDEX(GROUP_CONCAT(`comment` ORDER BY `date` DESC), ',', 1) AS last_comment FROM `log_table` GROUP BY `entity_id` The query above uses GROUP_CONCAT to generate a long ...


3

Create a Subquery that Gathers Keys from the the log_table with Maximum Date Per Entity. Then, perform an INNER JOIN of that Subquery back to the log_table. SELECT B.entity_id,B.last_log_date,B.last_comment FROM ( SELECT entity_id,MAX(last_log_date) last_log_date FROM log_table GROUP BY entity_id ) A INNER JOIN B USING (entity_id,last_log_date); ...


3

Joins across database links can lead to sub-optimal execution plans as Oracle doesn't have all the information available about both sites. Queries across db links can (and do) perform just fine when joined to local tables though. If writing a new query with local and remote tables I'd start joining it all together (set-based) then only break it into ...


2

Normally, I use this expression CAST(LEFT(GetDate() - GetUtcDate() + MyUtcDate, 11) AS DATETIME) But on SQL Server 2008, that simplifies to CAST(GetDate() - GetUtcDate() + MyUtcDate AS DATE) Other than giving alternative expressions here, if you're a T-SQL purist married to DATE functions to manipulate datetimes, I don't think there's a more concise ...


2

Your left outer join looks to be condensing your 100,000 rows in tblstatistics into approximately 2,643: 2 DERIVED tblstatistics ALL NULL NULL NULL NULL *2643* Using temporary; Using filesort and then it places that grouping in into a temporary table. At this point, it applies the aggregate functions (SUM, AVG, etc). Unfortunately, ...


2

SELECT A.id FROM A LEFT JOIN B ON B.id_A = A.id AND B.c = x WHERE B.id_A is null; Moving the test of B.c into the join condition and out of the where clause causes it to be used to only eliminate non-matching B-rows from consideration for the left join rather than eliminating rows from the result.


1

Since you only want to aggregate for a tiny number (only 12) of the presiid values, use the mantra LIMIT first, JOIN later: SELECT ps.id, ps.title, COALESCE(SUM(s.likes), 0) AS num_likes, COALESCE(SUM(s.comments), 0) AS num_comments, COALESCE(SUM(s.ratings), 0) AS num_ratings, COALESCE(SUM(s.views), 0) AS num_views, ...


1

And there is the basic approach to GROUP BY in the n-table and then JOIN to the 1-table in a 1:n relationship. This is often faster than joining before the grouping, but I am not sure whether it makes a difference for SQL-Server 2008. SELECT country FROM ( SELECT CountryID FROM Users GROUP BY CountryID ) AS u JOIN countries AS c ON ...


1

You may need to completely refactor the query to be something like this: select ps.id, ps.title, IFNULL(s.likes,0) as num_likes, IFNULL(s.comments,0) as num_comments, IFNULL(s.ratings,0) as num_ratings, IFNULL(s.views,0) as num_views, IFNULL(s.avg_rating,0) as avg_rating from ( select id,title from ps where active=1 ...


1

You can try to rewrite your query so it calculates the aggregations after the LEFT JOIN, and see if that gives you a different execution plan (but the optimizer can choose to ignore that). SELECT ps.id, ps.title, IFNULL(SUM(s.likes), 0) AS num_likes, IFNULL(SUM(s.comments), 0) AS num_comments, IFNULL(SUM(s.ratings), 0) AS ...



Only top voted, non community-wiki answers of a minimum length are eligible