DO NOT USE this for questions targeting Microsoft SQL Server, use the tag sql-server instead. "SQL" is an acronym for "Structured Query Language", it is used in relational database management systems (RDBMS) to query, update, delete, and insert data as well as modify the structure of the database. ...

learn more… | top users | synonyms (1)

0
votes
1answer
33 views

Count in an other table and show only theese records in query which has more than they used in other table

I have for example 2 tables: used(us_id, code_id); codes(code_id, amount) I want something like this: If for example I have 5 eggs, and egg code is 1, then if the used table have reached 5 of ID ...
0
votes
0answers
15 views

how to get tag record with its descriptor from pi database?

I want to get the data with tag, time ,value, descriptor from pi database, but it seems tag record and its descriptor are stored in different database. tag, time ,value is stored in database ...
1
vote
2answers
152 views

Group or Order SQL output into a single row

I want to display each persons contact info and their commissions in a single row as oppose to multiple rows. Commission Table ID COMMISSION DATE ...
4
votes
2answers
67 views

Grouping by sequential relationships with Postgres

This is quite similar to this question about continuous ranges, and this question about grouping by sequential numbers, but differs in that the sequences are not numeric. Given the following ...
2
votes
1answer
28 views

Detect circular relations in a distribution table

I have the following two tables, that will define distribution rates from items on table A to other items on table A (I think I can say B defines a many-to-many relationship for A with itself). ...
3
votes
2answers
2k views

SQLite writing a query where you select only rows nearest to the hour

I've got a set of data where data has been taken approximately every minute for about three month and the time has been stored as a UNIX timestamp. There is no regularity to the timestamp (i.e. the ...
3
votes
2answers
120 views

Filter and append data to row

My query and my data set (copied from sqlfiddle): CREATE TABLE one ( id int4 primary key, p_id int4, k_id int4 ); CREATE TABLE two( id int4 primary key, p_id int4, k_id int4, ...
18
votes
8answers
6k views

Why should an application not use the sa account

My first question ever, please be gentle. I understand that the sa account enables complete control over a SQL Server and all the databases, users, permissions etc. I have an absolute belief that ...
1
vote
2answers
60 views

How can I run a SQL job every 1 second? [duplicate]

I have an SQL Job which processes minimum amount of rows at a given time. Currently its running every 10 seconds which is the minimum available in Job Scheduler. But because of that the table used ...
0
votes
1answer
37 views

PostgreSQL window functions very slow on big table

I have a PostgreSQL table constructed as device_id | point | dt_edit ----------+-----------+---------- 100 | geometry | timestamp 101 | geometry | timestamp 100 | geometry | ...
0
votes
1answer
26 views

Oracle: Recursively self referential join with nth level record

I have self referential table like this: id |level | parent_id ---------------------- 1 |1 |null 2 |1 |null 3 |2 |1 4 |2 |1 5 |2 |2 6 |3 |5 7 |3 |3 ...
1
vote
0answers
5 views

JDBC - maximum allowable cursors - how do I see my open statements?

I've got some resource leaking and I'm trying to find what's causing them. I think I have some statements or resultsets that aren't being closed properly. Is there any way to see all of the cursors ...
1
vote
1answer
138 views

Query does not use index (according to EXPLAIN SELECT)

Can anyone give me an indication, why this query does not use the Key on the table 'gtg'): ...
3
votes
3answers
196 views

Do I want an index if all I care to query is field not null

In SQL, do all the common considerations about indexes apply if everything I'll ever want to query (in the where condition) is when a field is not null? Is there a special configuration of the index ...
2
votes
1answer
22 views

How to Choose Between VALUES and SELECT for INSERT?

This answer raised the question for me how to choose between VALUES and SELECT in such a function . Using PostgreSQL 9.4.3 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit: ...
1
vote
0answers
27 views

How to use variables in a PL/pgSQL function with dynamic queries?

...and when must I use EXECUTE ... quote_ident(variable)? Taking an input to the function as text and storing part as a variable: wtype := split_part(message, ' ', 4); realval := ...
0
votes
1answer
17 views

Slow fill database

The source sends in my program a large number of rows. When the number of rows such as two thousand per minute for one table, all is saved in the database properly. But if you send two thousand per ...
1
vote
1answer
184 views

Query multiple rooms with different conditionals (Adults, Children)

I have a simple query where I select available x Rooms with x Adults + x Children per hotel that matches a date range, but I'm having a hard time trying to figure out how to query a list of rooms per ...
0
votes
2answers
39 views

Display Total under name column and sum of amount under sal column [closed]

in a table empno,ename,sal,hiredate is there. but i want output is empno ename sal hiredate 122 ganga 2000 1989-02-06 123 reddy 3000 1986-06-02 Total 5000 here empno, hiredate column ...
0
votes
1answer
637 views

DELETE from Linked Server table using OPENQUERY and dynamic criteria

I'm attempting to run a DELETE statement from my local MSSQL Server to delete appropriate records from a table in my remote linked server using OPENQUERY(). I'm using the following code, but it seems ...
7
votes
2answers
4k views

Database size - MDF too large?

I'm maintaining a SQL Server 2005 database which hosts approximately 2.9Tb of data (2 x 1.45Tb - I have a RAW schema and an ANALYSIS schema so basically two copies of the data ingested). The recovery ...
2
votes
2answers
1k views

How to make postgresql SUM to be more accurate on large amount of floating point data?

I'm trying to SUM 8 million floating point (REAL) values with simple query like this: SELECT SUM(metric) FROM metrics; However, it returns very inaccurate result. It should return 137,586.77, but ...
1
vote
1answer
49 views

Replace Yes with 1 and No with 0

I get a data set and an example of the field is below. The values if these fields is "Yes" or "No". I would like to replace the value of "Yes" with 1 and the value of "No" with 0. This file will be ...
4
votes
2answers
34k views

Selecting two values from the same table but under different conditions

I want to grab a value from a table into two different columns for different values from the same table. Use this query as an example (notice how the select is on the same table aliased as 2 different ...
1
vote
0answers
21 views

Creating a dynamic database

I'm using mysql server connected to Access, and I want to do a form, to fill the table. My question is how to do it. I need to select the data from a listbox, that's okay this is already done. And I ...
0
votes
0answers
16 views

SQL Server 2012 Extended Event-Set Session Event Filter

I am doing an exercise to create a new session using SQL server 2012 extended event feature. --I am not able to see the drop down menu for 'set session event' filter when using 'new session wizard. ...
3
votes
1answer
143 views

How can I prevent having inconsistent data under isolation level serializable in Oracle SQL?

I have the following table: InStores +--------+---------+-----+ | ProdID | StoreID | qty | +--------+---------+-----+ | p1 | s1 | 25 | | p1 | s2 | 70 | +--------+---------+-----+ ...
1
vote
2answers
54 views

Why this query returns rows that I don't want to be there

I'm trying to running this query: SELECT * FROM (`default_users`) LEFT JOIN `default_profiles` ON `default_profiles`.`user_id` = `default_users`.`id` WHERE `default_users`.`group_id` ...
1
vote
2answers
105 views

Execute SQL query after execute TRUNCATE in a table

I have a table called fos_user_user where I have a default record "always". Since I'm testing all the time and sometimes I need to clean that table I'm asking if there is any way to run the following ...
1
vote
4answers
373 views

Database/Storage engine suggestion for a project involving heavy inserts?

I am looking into a project which involves large number of inserts daily.I will have a list of user's (for example a user set of 500k ) , for which I need to monitor daily certain activities ...
0
votes
0answers
11 views

Cannot create 64bit ODBC DSN entry to local SQL2008R2

Environment: SQL Server 2008 R2 server (Express Edition) running under Windows 7 64bit Professional. I want to access the server locally via 32bit VB-application and 64bit Web application. SQL ...
2
votes
0answers
29 views

SQL Server database suspect

All the databases went to suspect mode on a server. Tried bringing on-line by taking emergency but did not workout . Have restarted the server as there is disk I/o error still in suspect mode. Can ...
0
votes
1answer
125 views

How do you use COLUMN_JSON with COLUMN_GET on dynamic columns in MariaDB?

When I do the following query; SELECT name, COLUMN_GET(attr, 'appearance' as char) as 'attributes' from items; I see the 2 columns including the blob data with the weird formatting. Is it ...
-1
votes
1answer
32 views

Insert id into another table and give the item another id that is autocrementing?

Need this answered if anyone can help. I need to create a query that will automatically insert ordID, but give it a new auto incremented OrderNumber in Orders.dbo. Fiddle is below: ...
0
votes
1answer
34 views

SQL Spool FILE SQLPLUS BATCH FILE

*/ SET colsep ';' SET TERMOUT OFF SET NEWPAGE 0 SET SPACE 0 SET LINESIZE 800 SET PAGESIZE 0 SET ECHO OFF SET FEEDBACK OFF SET HEADING OFF SET NUMW 20 column timecol new_value timestamp column ...
4
votes
1answer
35 views

Limiting Domain Admins permissions on a SQL Server

I have a SQL Server 2012 machine and I want to limit the Domain Admins permissions. What can I do to make sure that the Domain Admins have read only permissions for the databases and nothing else for ...
0
votes
0answers
10 views

MySQL Cluster query all nodes at once

Is there a way to query all the available nodes of the cluster in one command ? (Make the same query go individually for each host and show the results.) mysql -uuser -p -hmyhost.com -e "select * ...
2
votes
1answer
4k views

Efficient way to insert/update/delete table records from complex query in Postgres 9.x

I have this function which returns a set a records and I need to persist those records into a table. I have to do it hundred times a day. My initial approach was just clear data from my table and ...
1
vote
0answers
48 views

Get all data from two tables and group it into different category in mysql

These are my tables to build an FAQ: CREATE TABLE `tbl_section` ( `section_id` int(11) NOT NULL AUTO_INCREMENT, `section_title` varchar(100) NOT NULL, PRIMARY KEY (`section_id`), ...
2
votes
1answer
59 views

What are the best practices for restricting write-access on a specific table?

I have a table, which I'd like to be read-only, except for a few specific stored procedures which need permissions to UPDATE rows in the table. After that, I want only a select group of additional ...
2
votes
2answers
39 views

SOS_Scheduler_Yield wait type

I am having a process that has a last_wait_type as SOS_Scheduler_Yield and is inserting 100 records to a table. This process has been with this wait type forever with low CPU utilization %. There are ...
-1
votes
1answer
60 views

A complex query to Joining 2 tables and getting result [closed]

I have a database in MySQL. I have 2 tables, Structures as follows : t1 t2 I want to join both the tables and result should be like this : Employee_id | count(DNIS) | total_calls | count(DNIS) ...
1
vote
0answers
13 views

Metaphone / Soundex Dictionary [closed]

I have a sql table with phrases which I convert using metaphone to allow users to search on strings which aren't spelled 100% correct. Evaluating the benefits of replacing the phonetic strings with a ...
2
votes
2answers
170 views

How to split url in sql server like this

Here is the url www.abc.com/a/b/c/d/e/f/g/h/i I want result like this www.abc.com/a/b/c/d/e/f/g/h/i www.abc.com/a/b/c/d/e/f/g/h www.abc.com/a/b/c/d/e/f/g www.abc.com/a/b/c/d/e/f ...
1
vote
1answer
212 views

Query to get reposts from people the user is following

I have a table posts where all the posts by users are stored, the structure of this table is as follows. | post_id | post_user | post_content | post_date | The users table is as follows. | user_id ...
1
vote
1answer
194 views

Very slow update with subquery (different tables)

I run this query to update "domains" table based on "log" table: UPDATE domains SET domains.mx_collected = 0 WHERE domains.domain IN (SELECT log.mail as domain_name FROM log WHERE `time` >= ...
1
vote
2answers
90 views

How can I select data from two differents servers?

I have two databases: data1 and data2 in two different servers (server1 and server2). I am trying to join two tables of my two databases in MySQL like this: SELECT left(a.code,9) as fourn, ...
4
votes
1answer
27 views

Update columns values using select from external database and table in MariaDB trigger

I have two databases pdone and veevan. pdone is a kind of master and veevan keep on continue sync against an external sources. I need to perform a internal sync between veevan and pdone so I am using ...
3
votes
2answers
61 views

Applying GROUP BY when using multiple CASE WHEN

select e_id ,CASE WHEN w_id in ('1','2') THEN w_id ELSE 0 END as new_w_id ,CASE WHEN w_id = 1 THEN fee END as value1 ,CASE WHEN w_id = 2 THEN fee END as value2 ,CASE WHEN w_id not in ('1','2') THEN ...
1
vote
1answer
37 views

How to update row values with values from SELECT into another DB in a trigger

I have been working in a trigger for INSERT|UPDATE values for a given record based on same PK. What trigger basically should do it is, foreach row when some INSERT happens on veeva_new.account: ...