Tag Info

Hot answers tagged

17

You might prefer something like this: select * from foobar where (subject,term) in ( ('STAT','111') ,('STAT','222') ,('ENGLISH','555') ,('COMM','444') ,('COMM','333') ,('STAT','222') ,('STAT','666') ...


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 ...


10

In terms of a pure code cleansing, the following looks cleaner: SELECT * FROM foobar WHERE (SUBJECT = 'STAT' and TERM IN ('111','222','666') ) OR (SUBJECT = 'COMM' and TERM IN ('333','444') ) OR (SUBJECT = 'ENGLISH' and TERM = '555' ) Depending on the application and how often the logic will be reused, it may also be worth setting up a ...


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 ago, but 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 return 1 when comparing Sunday & Monday, regardless of the times. Demo: DECLARE @MyTable TABLE(pk INT, ...


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.


2

Here is another way to do it. Using the where (col1,col2) might cause Oracle to not use any indexes, but this looks like a table to the query, so it might work better. You will know once you test the various versions. WITH subject_terms AS ( SELECT 'STAT' subject, '111' term FROM dual UNION ALL SELECT 'STAT' subject, '222' term FROM dual ...


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

This might help you to create simple dynamic query for partitioned tables using COALESCE function of SQL Server Step1: Create function to split tokens CREATE FUNCTION [dbo].[fnSplit] ( @List VARCHAR(8000), @Delimiter CHAR(1) = ',' ) RETURNS @Temp1 TABLE ( ItemId INT IDENTITY(1, 1) NOT NULL PRIMARY KEY, Item VARCHAR(8000) NULL ) AS BEGIN DECLARE @item ...


1

I'm assuming that product_inventories has a one-to-many relationship with both product_locks and orderlineitems, and that is the reason you are using the subqueries. In that case (and not knowing anything else about the schema), I believe this query should work. SELECT i.available - ( SELECT COALESCE(sum(pl.quantity), 0) FROM ...


1

I have refactored the query to use left joins and IF statement checks for the joins so that you do not have to use nested sub-queries. The group by for sku and site will give the totals by site if needed, but this can be changed to suit your needs. I am not sure of how the responses_authnets table joins to the orderlineitems for the siteid so this may need ...


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