Tag Info

New answers tagged

0

I assume you mean two rows per user_id since that is what your result indicates. You can use the technique described in: http://blog.sqlauthority.com/2014/03/09/mysql-reset-row-number-for-each-group-partition-by-row-number/ to mimic: row_number() over (partition by ... order by ...) In your case that would be something like: SELECT user_id, comment ...


-3

Delete managers who has HourlyWage more than 100.


1

In addition to what @jkavalik's solution: SELECT count(col) AS totalCount , count(distinct col) AS uniqueCount FROM ... You can use case: SELECT count(col) AS totalCount , count(distinct col) AS uniqueCount , count( case when ... then 1 end ) as conditional_count FROM ... Not sure this is what you are after but you mention a where ...


1

Each query in UNION ALL is executed separately, union only "merges" the result sets so if each query needs to read entire table, it will be done twice. You can check that with EXPLAIN in MySQL or similar commands in other RDBMS. One usage for UNION (ALL) is actually optimizing queries with OR conditions, where separate queries can use indexes better than ...


0

I think NOW() is what should be stored into a TIMESTAMP. As it is stored, the time is modified according to the timezone, and UTC is stored in the table. Upon retrieving, the reverse modification is performed. (This, of course, assumes the system has the 'correct' timezone established. See SHOW VARIABLES LIKE '%zone%';.) That way, if you have users ...


4

That SQL dialect is from Oracle but this answer will be for MySQL. If datum is DATE select count (*),datum date from tx_feeder where datum between (CURDATE() - INTERVAL 31 DAY) and (CURDATE() - INTERVAL 1 DAY) group by datum; If datum is DATETIME or TIMESTAMP select count (*),DATE(datum) date from tx_feeder where datum between (CURDATE() - INTERVAL 31 ...


0

Got the answer. To remove "FROM" away from INNER JOIN thx


3

This would be a good place to use window functions if your RDBMS supports them. The example below is T-SQL so if you are not on SQL Server you may have to adapt the syntax and/or keywords. Window Functions are ANSI Standard starting with SQL:2003, and as such you likely have access to them with any modern RDBMS. If you are on SQL Server read about their ...


2

You should perform an UPDATE LEFT JOIN and set enabled based on the right side being NULL UPDATE table1 A LEFT JOIN table2 B ON A.code = B.id SET A.enabled = 1 - ISNULL(B.id); Why should this work ? If ISNULL(B.id) is 0, that means A.enabled is set to 1 (1 - 0) because it is in table2 If ISNULL(B.id) is 1, that means A.enabled is set to 0 (1 - 1) ...


1

The problem with a FromDate / ToDate format is that the query generates an index scan. For example, a staff member takes vacation and returns two weeks later. ID FromDate ToDate 15 2011-01-03 2011-01-17 Imagine many staff members, some with 20 or 30 years of such work history, maybe taking only a day or two at a time several times each year. ...


0

Using @a1ex07 answer, I used this query: SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE (table_schema, DATA_TYPE) = ('my_schema', 'timestamp') AND COLUMN_DEFAULT IS NOT NULL; That showed that many were using CURRENT_TIMESTAMP instead of UTC_TIMESTAMP.


3

I will start with a link: What Every Programmer Should Know About Floating-Point Arithmetic. In short, float-point arithmetic types, like the float and double mysql types should never be used for precise arithmetic. And your col mod 0.1 is trying to do exactly that, a precise arithmetic check. It is trying to find if the value in col is an exact multiple of ...


1

lat and lng are obviously numbers, so you should store them as appropriate numeric data type, not as text. That makes for smaller storage and disallows invalid input, it simplifies your query syntax and is also a bit faster overall. It's not going to do much for your query, though. For instance: lat | numeric lng | numeric So ...


2

Double check that the paths you are trying to restore to exist on the D drive. SQL Server will not create the directory path if it doesn't exist. If you haven't already, run the restore command with the verify only option MSDN. If that is successful here are a few additional things you can try to isolate why it is failing If you have the space on the ...


4

The clause after on is the condition which is if met, the row will be returned with the result set. In your example, the condition is always met, so the result is the Cartesian product of the joined tables. This is similar to saying ON 1=1, which is always true. You will get the same result if you don't add the condition with the inner join.


1

Has anyone done an research as to the overhead created by enabling AWE on the 64-bit OS/SQL combination? This is one of the Myths that Paul Randal clearly debunks in his famous series - A SQL Server DBA myth a day On 64-bit systems (2005+): AWE is not required (and in fact enabling it does nothing). Also refer to Fun with Locked Pages, AWE, Task ...


0

The tool I needed to figure it out was RAMMAP, which showed the AWE pool as the memory hog. Yes, someone enabled AWE on a 64-bit OS with 64-bit SQL.


0

You have a table that contains All (Active and Inactive) Doctors and you have a file that contains Active Doctors. The question then becomes how can you determine who isn't in the file? The SSIS way to do this is to use the Lookup Task, just as you are already doing but instead, you're going to use a Cache Connection Manager to allow you to use the flat ...


0

Before you go re-inventing the wheel there, have you actually tried the XML Source (in Data Flow)? I created a 50GB XML file (40 million users with 20 skills each totalling 800 million rows) and it worked perfectly well: This was on my laptop with 16GB RAM using SQL Server 2014 and there were no out-of-memory errors. This does suggest the XML Source ...


2

I think this could be a whitespaces issue. The problem is that MySQL ignores trailing whitespace when doing string comparison. See http://dev.mysql.com/doc/refman/5.6/en/char.html All MySQL collations are of type PADSPACE. This means that all CHAR, VARCHAR, and TEXT values in MySQL are compared without regard to any trailing spaces. ... For ...


1

You can easily find the subsequence when you cast the arrays to strings and replace the curly brackets with commas: translate(cast(sequence as varchar(10000)), '{}',',,') {1,3,17,25,377,424,242,1234} -> ',1,3,17,25,377,424,242,1234,' Do the same for the array you're searching for and add a leading and trailing %: '%' || translate(cast(searchedarray ...


1

A semi-solution: They will be in column. (But they are also in a single row, sort of.) SELECT CONCAT_WS("\n", her_name, his_name, other_name) AS name FROM foo For example: mysql> SELECT CONCAT_WS("\n", province) FROM Provinces; +---------------------------+ | CONCAT_WS("\n", province) | +---------------------------+ | Alberta | | ...


0

After further research, all I needed to do was drag the Colors table into the design view TWICE. Then link one to each ID (Top, Bottom) in the outfit table. Then set the alias and done.


3

Basic answers Since you select a couple of big columns (info in comment) an index-only scan are probably not a viable option. This code works (if no NULL values in data!) Add NULLS LAST to make it work in any case, even with NULL values. The added clause won't hurt either way. Ideally, use the clause in the accompanying index as well: SELECT <some ...


1

OK, I think I found a way. This may not be the best way (using the GUI) I create Query for each sub query. On TopQuery: SELECT outfits.TopColorID, Colors.Color FROM Colors INNER JOIN outfits ON Colors.ID = outfits.TopColorID; On BottomQuery: SELECT outfits.BottomColorID, Colors.Color FROM Colors INNER JOIN outfits ON Colors.ID = outfits.BottomColorID; I ...


1

Put this in the SQL View of the query design and you can then see the GUI required to build what you want. select ID , (select colorname from colors where id = outfits.topcolorid) as TopColorName ,(select colorname from colors where id = outfits.bottomcolorId) as BottomColorName from outfits;


1

The answer from @Mister Magoo is great. I used SQL Server Management Studio to do the same task. I followed the instructions here from MSDN. Pasting below as well for convenience: To create a filtered index In Object Explorer, click the plus sign to expand the database that contains the table on which you want to create a filtered index. Click the plus ...


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


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


0

How about using INSERT ... ON DUPLICATE KEY UPDATE or REPLACE INTO on master itself?


0

Generate a calendar table (that's googleable) and join your other tables to it. This makes it easy to do overlaps, etc. in an efficient way. In case it is not clear this is the standard way. I haven't gone into example usage because it is too involved for an answer here. Search for "calendar table" will give lots of examples and a full explanation to ...


1

I love Aaron Bertrand's answer. Although I don't understand it completely, it looks really elegant. In the past I've ran into problems with permissions when using sys.objects. Combined with the need for me to troubleshoot the code, I've come up with a variation on Aaron's code, and added it below. This is my procedure: CREATE PROCEDURE ...


0

you can also achieve this result by using Pivot as below. ;with cte as ( SELECT sq.survey_cat_id, sd.Store_id,answer FROM survey_details sd INNER JOIN survey_question sq ON sd.ques_id = sq.ques_id ) Select * from cte PIVOT( COUNT(answer) FOR answer IN ([excellent], [very ...


3

You can use a filtered index to enforce uniqueness on id and is_primary when is_primary is 1. An example here: create table Computer( id char(20) not null , NIC_name varchar(100) not null , is_primary bit not null , constraint pk_computer primary key ( id asc, NIC_name asc ) ); create unique index ix_one_primary on Computer(id, ...


5

It is unclear to me what is a "more elegant way". Oracle you can use the following statement to make columns to rows select all_name from foo unpivot (all_name for col_name in ( his_name, her_name, other_name)); This is the syntax diagram of the select statement SELECT [ALL | DISTINCT | DISTINCTROW ] [HIGH_PRIORITY] ...


0

You may have already attempted this, but I must ask: Have you attempted to initialize mirroring via the SSMS GUI? In SSMS on principle server, right-click datbase --> Mirror --> Configure Security. I'm curious if the same error message is generated from the GUI. Can you establish a connection to your mirror server from the principle server via SSMS?


1

Given the structure of multicolumn B-tree index it is not viable to do a sort on position when using IN on category_id. But as the test data suggest the position is not "global" but seems to have a meaning only for given category_id. So as I suggested in comments, it is instead possible to ORDER BY (category_id, position) - that can use two-column index on ...


0

select x.* from member as x where x.id IN ( select id from member group by id having count(distinct email) > 1 )


1

All three options you are looking at are bound to perform poorly in a concurrent environment. Wrapping SELECT (MAX(wid)) ... in a transaction won't prevent race conditions; you'd need to write-lock the entire table for that. If you don't need the sequence numbers to start from 1 for every branch, as long as the values are increasing and unique, you might ...


0

Well, I think this is a good example when SET operations might not be the best tool in the box. I'd go for a simple cursor here! :) Both easier to read, maintain and scans the table only once! An RDBMS does not really have the concept of 'ordered table' unless you explicitly specify it with an ORDER BY. I do, however, guess your table is 'ordered' by < ...


0

You could setup a history database (using nzhistcreatedb) which acts as a detailed access log and then consult the views and tables that it provides (e.g. $v_hist_table_access_stats). If you haven't already got this in place then I'm not aware of any way to find out who accessed a table unless you trawl through the logs in /nz/kit/log/postgres/.


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


0

If the string is being thrown directly out to a HTML stream then you could do Select 'Your submitting date is 2<sup>nd</sup> January 2016'; but that really isn't recommended practise - you should avoid making presentation layer decisions like that within your data layer. If you tightly couple your back-end to your presentation layer in ways ...


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.


0

Create a script component as a data source. You can probably re-use a lot of your code that you used to create the script task. Then you can set your batch size.


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


0

You could make a start by using sed, awk and grep to change MySQL data types to SQL Server ones - but without a server to test on, you're really flying blind.


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.



Top 50 recent answers are included