Tag Info

New answers tagged

0

So, as mentioned in the comments, since you apparently have the authority to modify the data model itself, there are perhaps better solutions available to you at this point. The direct answer to your question is to update your new prices table to reference your preferred products record in each duplication case, then purge the other duplicated products ...


0

In the absence of GROUP BY clause the query considers the whole relation as one group. e.g. select count(*) from dual having count(*) > 5;


0

Your inner query should look like SELECT DISTINCT rp.post_id, CASE WHEN pm.meta_key = 'htk_premiere_date' THEN pm.meta_value ELSE '' END AS premiere_date FROM wp_related_people AS rp JOIN wp_posts AS p ON rp.post_id = p.ID JOIN wp_postmeta AS pm ON pm.post_id = p.ID WHERE ...


1

Well, Apologies to all those SQL guru's - this might be a simplistic answer. Since the product table will only have one record per product you don't need the ID column and can just use the 'sku' as the key. Insert into that table a distinct list of the products e.g INSERT INTO products (sku,ProductName) SELECT DISTINCT (sku,ProductName) FROM ...


1

in ANSI SQL the DISTINCT is totally pointless here. UNION automatically filters duplicates: test=# SELECT 1 UNION SELECT 1; ?column? ---------- 1 (1 row) There is a sharp distinction between UNION and UNION ALL: test=# SELECT 1 UNION ALL SELECT 1; ?column? ---------- 1 1 (2 rows) in your case the second subselect can never ...


0

Be happy that your users seem to be going to use SQL to do the analysis, instead of pumping to excel. Make sure you have separated tasks and don't give privs to update the source data. Resource manager is your friend to prevent runaway queries. give them resources that they need, make sure other users don't get pushed out.


0

I think there is no problem, but you need to take care about data. For example, you can grant them privileges to create tables and procedures for their needs in theirs schemas, and privileges to select data from another schemas, but not to grant privileges on insert/update/delete. If they don't have privileges on update and delete data in main tables, ...


0

Get all radiant_name where radiant_win is true and union that with all dire_name where radiant_win is false. Use the result of the union in a query where you group by Team and count the number of occurrences. Some pseudo code that you might need to modify a bit to make it work in your DBMS. select T.Team, count(*) as Wins from ( select ...


0

It looks like what you want is row-level security. PostgreSQL will support this in version 9.5, but that's due to come out in over a year. For now, you'll need to use views. If you don't mind the fact that a clever user can trick the system into revealing information they're not meant to be able to see using malicious functions in the WHERE clause, you can ...


0

You can use COLLATE : concat(COL1 COLLATE latin1_general_ci,COL2 COLLATE latin1_general_ci) latin1_general_ci is an example, try to figure it out what collation do u have. But i think this should work, you could try latin1_general_cs too.


0

The following will work: sqlcmd -U user -P pass -S Server -Q "sp_who" -r1 1> test.log The output from the query will be put into the log and nothing is printed on the screen. In the case of an error, it will run like this: sqlcmd -U user -P pass -S Server -Q "sp_notreal" -r1 1> test.log Msg 2812, Level 16, State 62, Server XXXX, Line 1 Could ...


-2

create table notification_temp as select * from notification where CreatedAt < DATE_SUB(CURDATE(), INTERVAL 30 day) ; drop table notification; RENAME notification_temp TO NOTIFICATION;


2

Yes, you can choose instance to apply service pack. Instance components along with shared components between side by side instances will be upgraded to higher service pack. Sql server has two types of component\features. 1. Instance specific features 2. Shared Features When you upgrade one component with a given instance name to a new version or ...


2

Yes, but you have to understand that there are certain shared components. See Work with Multiple Versions and Instances of SQL Server.


1

Table layout It would be more efficient to reverse the column order in nodes: fixed length NOT NULL columns first. This is just a tiny optimization. It's only the first item because table layout comes first. CREATE TABLE nodes( id bigint PRIMARY KEY , type text NOT NULL , name text ); Index Replace substring(name,1,2700) with left(name, 2700) ...


1

There is no other way to enable service broker except taking that database out of AlwaysON Availability group, enabling service broker and then joining it back. If you know that your source database has service broker enabled, then when you restore the database, use RESTORE .. with ENABLE_BROKER, so that you dont have to go with such trouble again. If you ...


1

Make a temp table, switch it in and out, and copy the last 30 days data into it. # # Make empty temp table # CREATE TABLE NOTIFICATION_NEW LIKE NOTIFICATION; # # Switch in new empty temp table # RENAME TABLE NOTIFICATION TO NOTIFICATION_OLD,NOTIFICATION_NEW TO NOTIFICATION; # # Retrieve last 30 days data # INSERT INTO NOTIFICATION SELECT * FROM ...


1

My favorite is pt-archiver from Percona Toolkit. It takes care of MySQL load, replication lag.


3

For the simple case, GREATEST() on sub-selects may perform best: SELECT GREATEST((SELECT userid FROM guestuser ORDER BY userid DESC LIMIT 1) ,(SELECT userid FROM olduser ORDER BY userid DESC LIMIT 1)) AS userid; If you want to change query using max function: SELECT GREATEST((SELECT max(userid) FROM guestuser) ,(SELECT ...


3

Try it this way: select userid from ( (select userid from guestuser order by userid DESC LIMIT 1) union all (select userid from olduser ORDER BY userid DESC LIMIT 1) ) sq order by userid DESC LIMIT 1;


1

ORDER BY (case when `cat` = 'mgr' then 1 when `cat` = 'dev' then 2 else 3 end) This structure help me in Wordpress. For example to select custom posts first theh other: poster and project is custom SELECT post_type FROM wp_posts ORDER BY (case when post_type = 'poster' then 1 when post_type = 'project' then 2 else 3 end)


0

The reason we specify keys for a table is primarily to improve the data integrity and usefulness of the data. Keys guarantee the table is free from duplicate data and therefore they allow the user/consumer of the data to identify information correctly. DBMS query optimizers and storage engines are designed to take advantage of keys so having a key will also ...


0

I'm not familiar enough with Access' syntax to give you a cut-and-paste answer. In pseudo-code, though, you can use something of this form: WHERE ( (Appointment.[First Name] = Forms![Search_frm]![FirstName_tbx].Value) OR (Forms![Search_frm]![FirstName_tbx].Value = <an empty value>)) AND ( (Appointment.[Last Name] = ...


0

I admit this is sleazy but if you're in a pinch try returning the top number of rows in the subquery. Returning the top 100 percent doesn't work but if you want to go through the trouble you can query the number of rows and pass that to TOP as a variable. I tested this on a database set to compatability level 80 so I think it should work with SQL 2000. ...


1

Something like the following should be good enough to get you started. It should be quite lightweight as well. DECLARE @Name NVARCHAR( MAX ), @SQL NVARCHAR( MAX ); DECLARE @t_BindingErrors TABLE ( ViewName NVARCHAR( MAX ), ErrorMessage NVARCHAR( MAX ) ); DECLARE c CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY FOR ...


0

You can generate SELECT statements for all the views in a database and just run the results: SELECT 'SELECT * FROM dbo.' + name from sys.sysobjects WHERE type = 'V' Which should return SELECT * FROM dbo.xxxx SELECT * FROM dbo.yyyy etc... For all the views in the current database. Customize as needed for a different schema or columns to SELECT to avoid ...


0

On Button Click, I got to do something like this, as suggested by @MarkSinkinson. protected void btnSubmit_Click(object sender, EventArgs e) { SqlCommand cmd = new SqlCommand("Insert into CategoriesForMerchant (CategoryName) values (@CategoryName)", conn); cmd.Parameters.AddWithValue("@CategoryName", txtCategory.Text); conn.Open(); ...


0

The Redgate backup tool does this very well, see here.


0

If you have access to the database, Echo (instead of run) your variable with SQL command and then put that command into SQL server and see what error you have there. $tsql2 = "INSERT INTO pedidos (numero_local, emailUser,codigopapel,cantidad, color) VALUES ('$local', '$emailOn', '$codigopapel', '$cantidad', '$selectcolor')"; /* $stmt2 = ...


0

You may find that the following index and query rewrite performs better, because it sorts per person rather than once over the whole set, and row estimates are more likely to be accurate: -- Index CREATE INDEX IX_Salaries_PersonId_SalaryDate_Inc_ID_Salary1_Salary2_Salary3 ON rdd.Salaries (PersonId, SalaryDate) INCLUDE (ID, Salary1, Salary2, Salary3); -- ...


0

Here's one way: mysql> #select all in reverse mysql> select id, createdAt from post order by createdAt desc, id desc; +------+---------------------+ | id | createdAt | +------+---------------------+ | 16 | 2014-11-16 09:11:18 | | 15 | 2014-11-16 09:11:03 | | 14 | 2014-11-16 09:10:32 | | 13 | 2014-11-16 09:10:05 | | 12 | ...


0

Here is a guide: select 'columnA', lead('columnB', 1) over () as next_b, lead('columnC', 1) over () as next_c ... from table where clause; I believe that you need to have lead(expression,1) as many columns as you need. I don't know if there is a shortcut for multiple columns.


1

Yes, it is possible to split this 100 million row table into any number of tables, even without it having any indexes. Now, you might could do it purely in T-SQL but that would require loading each of the split tables via individual insert statements in a transaction, and that can be a bit heavy on the tran log, especially if splitting to tables of 2 million ...


3

If I understand correctly, I think this should work... We're checking that TableA.id+100 is in TableC.eid and then counting the number of times TableA.id+100 appears as TableB.char. It's always good to create a SQLFiddle though so that others can easily pick up your schema and code to debug. SELECT a.id, a.name, a.comment, COUNT(b.char) AS count ...


0

I am not so familiar with mysql. BUT. You should always connect relations on primary - foreign key. You join PRODUCT_CATEGORIES with ORDERS_PRODUCT on product_id which are foreign key and I am not sure if they can be NULL. Run EXPLAIN select and you will know what your query planner is doing.


0

one thing you got to keep in mind is: if you materialize the count, you might have some fun on the concurrency side. however, a materialized column is of course always a lot faster.


2

Essentially, you are asking if you can perform a single ordered scan through the data overall, while making no copies of the data, and returning 'x' disjoint sets of rows from the full set on each call. This is exactly the behaviour of an appropriately-configured API cursor. For example, using the AdventureWorks table Person.EmailAddress to return sets of ...


1

Does this do what you're after? SELECT TOP (1) WITH TIES TX.COL1, TX.COL2, TX.COL3, TX.COL4 FROM dbo.TESTX AS TX ORDER BY ROW_NUMBER() OVER ( PARTITION BY TX.COL1 ORDER BY -- Priorities CASE COL2 WHEN 'dog' THEN 1 WHEN 'cat' THEN 2 WHEN 'bird' THEN 3 ...


0

Option a is much more likely to return the correct value. Option b requires a new table and implementing code to maintain it. This would definitely be a sign of premature optimization. I would expect you want a query something like. SELECT attribute, COUNT(attribute) FROM mytable GROUP BY attribute If the attribute column is ...


1

select sum(case when issue_date = return_date then 1 else 0 end) SAME_DAY, sum(case when return_date is null then 1 else 0 end) NOT_RET, sum(case when issue_date <> return_date then 1 else 0 end) SEP_DAY from books;


0

As in http://stackoverflow.com/questions/17148144/insert-into-sqlsrv-query-statement-not-working .. you might try die('Errors: ' . print_r(sqlsrv_errors(), TRUE); .. or something like that. (I don't know PHP.) Also, use placeholders (?) for values, then pass your parameters. If you just interpolate strings into your SQL literally, you may be vulnerable to ...


1

Perhaps you need to run this as a GROUP BY aggregation using the GROUP_CONCAT function. SELECT TICKER,GROUP_CONCAT(RETURN1) ticker_returns FROM STOCK_DATA WHERE TDATE BETWEEN 20130101 and 20131231 GROUP BY TICKER; CAVEAT #1 In your DB Session, you may need to extend the length of [GROUP_CONCAT} output because its default limit is 1024. Run this before ...


1

This does not make sense: INSERT INTO pedidos VALUES((SELECT numerolocal FROM localidad WHERE numerolocal = $local)) You haven't specified a column list, so this should fail with a syntax error (since your table clearly has more than one column). Formats for INSERT are typically: INSERT dbo.table(col1, col2) SELECT col1, col2 FROM dbo.othertable; Or: ...


0

Use the query wizard to create a new "temporary" table - a 'view' (a query). This you can use then as source for your letter.


0

I think you can move your subquery onto the join condition, as you're just looking for customers' transactions before or on 1st Jan 2013. SELECT C.Customer_id, C.tran_id, t.date FROM CUSTOMERs C INNER JOIN TRANSACTIONS T ON c.Customer_id= t.Customer_id AND t.tran_Date <= '2013-01-01' WHERE C.Customer_id NOT IN(1,2,3)


0

Without knowing the purpose behind the windowing it's going to be difficult to be specific. Considering you're looking at twenty thousand rows at a time, I'm guessing this is a batch process and not for human viewing. If there is an index on the email address then it is sorted. Indexes are BTrees and they maintain an order internally. This will be the ...


1

Having a primary key per se will not speed up queries. Primary key constraints are usually accompanied by a unique index. If this index matches query predicates or join conditions then those queries are likely to run faster.


3

No, having a surrogate key would not speed things up. You should consider what your queries are doing, and whether your indexes are sufficient. For instance, if you are trying to find out the total sales for Widgets in Timbuktu on Christmas Day, then do you have an index on ProductID, StoreID, TransactionDate that also includes the SalesAmount? If not, how ...


0

If you are simply concerned with sort order stability over time in the presence of DML consider using Snapshot Isolation to query the table. You can leave a SNAPSHOT transaction open until you are done extracting pages. This has the usual drawbacks associated with Snapshot Isolation. That said this technique will require sorting the entire table for each ...



Top 50 recent answers are included