Tag Info

Hot answers tagged

22

This pattern column = @argument OR (@argument IS NULL AND column IS NULL) can be replaced with EXISTS (SELECT column INTERSECT SELECT @argument) This will let you match a NULL with a NULL and will allow the engine to use an index on column efficiently. For an excellent in-depth analysis of this technique, I refer you to Paul White's blog article: ...


7

Subtract one from each other in YYYYMMDD format, and divide by 10000 using integer division (which rounds down). So: (YEAR(@CurrentDate) * 10000 + MONTH(@CurrentDate) * 100 + DAY(@CurrentDate) - YEAR(@DateOfBirth) * 10000 - MONTH(@DateOfBirth) * 100 - DAY(@DateOfBirth) ) / 10000 20150725-20000727 = 149998, and 149998 / 10000 = 14 You can also use ...


4

You need to find both times, one using what you have (LIMIT 1) and the second using LIMIT 1 OFFSET 1. I'd also use ORDER BY ... DESC, not ASC. Your wording suggests you want the rows between the last 2 appearnces of 'user': SELECT * FROM user WHERE lastmodifieddate > (SELECT sync_time FROM _sync_history WHERE object_name = ...


4

You can generate a customized index table, for example, that has a row for all year&week pairs that the date range (a certain vacation etc) encompasses. Then you can join dateranges by going through that index table. It will be large, but does avoid large scans as you can just list all vacations that have any weeks that are in common with another ...


3

You can use not exists clause to do this. SELECT u. ID AS user_id, s. ID AS song_id FROM songs s, users u WHERE NOT EXISTS ( SELECT 1 FROM user_songs us WHERE us.user_id = u. ID AND us.song_id = s. ID ) ORDER BY 1, 2


3

You just need to specify the first SiteInfo node using [1] update [dbo].[Sites] SET [SiteInfo].modify('insert <Anothernode>ABC123</Anothernode> into (/SiteInfo[1])') where Siteid = 1


3

Operation 1 Insert records into Table1 that only exist in Table2: INSERT INTO Table1 (address, city, state, zip) SELECT address, city, state, zip FROM Table2 EXCEPT SELECT address, city, state, zip FROM Table1 ; Operation 2 Delete records from Table1 where the record is not in Table2 but the city is in Table2. (assuming that the columns ar ...


3

You could do it using UDV: #MIN sync_time of user SET @StartDate=(SELECT MIN(sync_time) FROM test._sync_history WHERE object_name='user' LIMIT 0,1); #MAX sync_time of user SET @EndDate=(SELECT MAX(sync_time) FROM test._sync_history WHERE object_name='user' LIMIT 0,1); #Query: SELECT * FROM test.user WHERE lastmodifieddate BETWEEN @StartDate AND @EndDate; ...


3

USING UNION Others have submitted answers trying aggregation to collect data without using UNION May 12, 2014 : Query improvements without UNION May 05, 2015 : Calculating values from three related tables, without using join or union Feb 20, 2012 : SQL Data aggregation In this instance, what makes UNION an absolute must is the merging of three columns ...


2

From Itzik Ben-Gan's Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions (Developer Reference): Logical Query Processing Order: 1 FROM 2 WHERE 3 GROUP BY 4 HAVING 5 SELECT 5.1 Evaluate Expressions 5.2 Remove Duplicates 6 ORDER BY 7 OFFSET-FETCH/TOP Window Functions operate in steps 5.1 and 6. Obviously then, you cannot access ...


2

Let me try for a pure SQL solution: SELECT id FROM ( ( SELECT *, 0 AS chck FROM tableX WHERE id > @givenID ORDER BY id LIMIT 3 ) UNION ALL ( SELECT *, 1 FROM tableX WHERE id <= @givenID -- just in case of a small table (< 3 rows) ORDER BY id LIMIT 3 ) ...


2

If you prefer not using stored procedures, here is my way: set @my_id=7; set @rank=0; select id from ( (select id, @rank:=@rank+1 as x from t where id>@my_id order by id) union (select id, @rank:=@rank+1 as x from t where id < @my_id order by id) ) my_table limit 3; You change the value of @my_id accordingly. ...


2

It sounds like you want something like this: SELECT invent.code_product,products.supp,products.price, MAX(products.date) AS Date FROM products LEFT JOIN list_product ON products.supp = list_product.supp LEFT JOIN invent on list_product.id_product = invent.fk_product Group by invent.code_product,products.supp,products.price Order by products.date ...


2

Another way would be to use EXCEPT like this: SELECT u.id AS user_id, s.id AS song_id FROM users AS u CROSS JOIN songs AS s EXCEPT SELECT user_id, song_id FROM songs_played ; The result would be all u.id, s.id pairs that are not found in songs_played.


2

Overlapping date ranges can be found thus WHERE FromDate <= QToDate AND ToDate >= QFromDate You will get one row returned for each partially or fully overlapping range. For example, if you had separate pay rates for morning, afternoon and evening shifts and someone worked all three, there would be three rows returned. The same basic pattern ...


1

I've modified your query and I used CASE statement to COUNT: Query: select survey_categories.survey_cat_id, survey_question.ques_id, survey_details.store_id, count(CASE WHEN survey_details.answer = 'excellent' THEN 1 END) AS Excellent, count(CASE WHEN survey_details.answer = 'very Good' THEN 1 END) AS Very_Good, count(CASE WHEN ...


1

The only difference between the two queries is that the 2nd will always return 1 row. Even if the table is empty or just has 0 rows with reference=X, the result will be 1 row with null in the min(date) column. The first query will return no row in this case. The and rownum=1 is useless and does not affect anything for the specific query. Test at SQLfiddle. ...


1

SELECT MAX(Description) as MAX_Description,a.Structure ,a.Description FROM (select ,CASE WHEN Type = 'Test' THEN Structure ELSE '' as Structure CASE WHEN Type = 'Test' THEN Description ELSE '' END as Description from <Table_Name> ) a GROUP BY a.Structure ,a.Description


1

Typing from my phone, you could do something like: select x.* from t as x join ( select id from t group by id having count(distinct email) > 1 ) as y on x.id = y.Id You seem to be missing a primary key, but I assume this exercise is part of fixing that.


1

You can query v$sqlarea, which holds sql history for a little longer than v$sql (holds info only about current running sql). Both have similar columns, but depending on the report you want to extract, you might wanna join v$sqlarea with other views. But V$sqlarea is also limited to a period of time, and if you need to go more in depth, the view is ...


1

Have you considered using a Data Flow Task with a Script Component acting as a source? You could just do an AddRow for each row you consume out of the file, and let SSIS handle the batches.


1

I've made a Stored Procedure where you can send the STARD_ID and the QUANTITY of rows. Example: mysql> SELECT * FROM test.tbl; +----+ | ID | +----+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | | 7 | +----+ 7 rows in set (0.00 sec) mysql> CALL test.rows(6,4);; +---------+----------+----------+ | STR | START_ID | QUANTITY | ...


1

Something like this should get you started. The procedure will get the correct rows. It would have been a lot easier if you hadn't wanted to restart at the beginning when you reached the end. :) delimiter $$ create procedure getRows(in inID int) begin declare maxID int; declare IDPlusOne int; declare IDPlusTwo int; declare ...


1

The condition: AND A.name = B.name only allows identical names, while you obviously want to match names with the same soundex value. So make that: AND soundex(A.name) = soundex(B.name) If gid is the primary key, you can also simplify to: GROUP BY a.gid, b.gid However, since there obviously is no aggregation at all, you can simply remove the ...


1

If you are expecting a single latest record per product code, then sort them first and filter for the expected results. Example: SELECT product_code, product_supplier, product_price, product_date FROM ( SELECT CASE WHEN @prev != ( @curr := i.code_product ) THEN 1 ELSE 0 END AS ...


1

The blog post you refer to is wrong in using [INNER] JOIN between post and posts_tags. Nothing enforces that at least one tag exists, so posts without tags are excluded from the result. (Also, why is there no PK for posts_tags? A dubious DB design.) You copied that error and made it much worse by combining multiple relationships that way. Each many-to-many ...


1

For SQL Server, you can use IF EXISTS.. if exists ( select 1 from [dbo].[table1] where col1 = 1) and exists (select 1 from [dbo].[table2] where col1 = 1) begin -- do your insert / update /delete here print 'the value is 1' end else print 'the value is not 1..sit idle ..' Make sure you use proper TRY/CATCH blocks for proper error handling.


1

Although it's all opinion I strongly favour using pull techniques for the following reasons: I think it's easier to keep track of the dependencies by using a purely pull-approach. In a way it's similar to the whole concept of queries on tables or queries on views on tables. It's all "pull", not "push". Most DBMSes have better control of transactions etc in ...


1

You can use the DUAL table. DUAL is purely for the convenience of people who require that all SELECT statements should have FROM and possibly other clauses. MySQL may ignore the clauses. MySQL does not require FROM DUAL if no tables are referenced. Quoting from MySQL doc-ref


1

Better to use IN and JOIN i.e. SELECT table1.A, table2.B, table3.C, table3.D FROM table1 JOIN table3 ON (table1.DDD = table3.EEE) JOIN table2 ON (table2.BBB = table3.CCC) WHERE table2.AAA IN ('XXX', 'YYY') AND table3.FFF = '0' AND table3.GGG = 'HHH' As the only difference is table2.AAA. JOIN is a nicer than WHERE clauses.



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