Tag Info

New answers tagged

1

The WHERE clause needs a condition, if you type: SELECT Name FROM MyTable WHERE MAX(Age); there is no condition. Try to translate it to a sentence: Select the name from MyTable where maximum age... Now compare it with this statement: Select the name from MyTable where the age field is equals to (select the maximum age) And now in SQL code: ...


0

I believe Teradata supports window functions, so you can use them to pick the larges value. select name, age from ( select name, age, dense_rank() over (order by age desc) as rn from MyTable ) t where rn = 1; If there are multiple names with the same age, all would be shown. If you only want to pick one, use row_number() instead of ...


0

There could be more than one names that share the same maximum age. To find them all, you can use the fairly standard SQL statement, which should work in almost all DBMS: SELECT Name FROM MyTable WHERE Age = (SELECT MAX(Age) FROM MyTable) ; Only in some DBMS, like SQL-Server (and Teardata I think), you could use this as well: SELECT TOP (1) WITH TIES ...


0

The SQL term you are looking for is Join. This will allow you to combine records from the two tables. Using your example above the SQL syntax to produce your required results would be: SELECT roid,uid,uname,problem,prostatus from table1 JOIN table2 ON table1.uid = table2.uid


2

A numeric column without a value is null: select * from the_table where column_numeric is null; There is no equivalent concept of an "empty string" for numbers.


1

My team once had to build a database for reporting financials with alternative time windows, including fiscal years, fiscal quarters, and fiscal months. The relationship between dates and these units was documented, but really messy. So here is what we did. We created a table, call it Almanac, with one row per date. (In reality, we had one row per work ...


4

You missed closing bracket in line: user VARCHAR(100 NOT NULL, should be: user VARCHAR(100) NOT NULL,


1

create table `certificates`( `id` INT NOT NULL AUTO_INCREMENT, `common_name` VARCHAR(100) NOT NULL, `csr` LONGTEXT NOT NULL, `type` VARCHAR(100) NOT NULL, `certificate` VARCHAR(100) NOT NULL, `user` VARCHAR(100) NOT NULL, `creation_date` DATE, PRIMARY KEY ( id ) )


0

Looks like you have multiple entries in PS_AL_CHK_HRS_ERN that meet the criteria. To Summarize you will have to use SUM(CheckViewHrsErn.EARNINGS) and add a GROUP BY clause. SELECT CheckViewHrsErn.EMPLID, JobDta.JOBCODE, CheckViewHrsErn.CHECK_DT, SUM(CheckViewHrsErn.EARNINGS) FROM PS_AL_CHK_HRS_ERN CheckViewHrsErn LEFT JOIN PS_JOB JobDta ON ...


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


4

I think as a DBA you will inevitably lose the fight to keep hands out of your database. Having said that I think we owe it to our customers to try and provide a product that they can use. There are dangers and pitfalls of even read-only access that any DBA should be aware of: You admitted that you are working with large record counts in your tables. What ...


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

It might be worth looking at XACT_STATE instead of @@TRANCOUNT. From BOL: Both the XACT_STATE and @@TRANCOUNT functions can be used to detect whether the current request has an active user transaction. @@TRANCOUNT cannot be used to determine whether that transaction has been classified as an uncommittable transaction. So there may be some cases ...


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;


3

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


1

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.


3

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



Top 50 recent answers are included