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)

1
vote
4answers
2k views

Check the data latency between two Always On Availability Group servers in ASYNC mode

I have implemented Always on Availability Group in asynchronous mode between two servers primary and secondary. When i tried to check the data latency between the servers in performance monitor ...
0
votes
2answers
23 views

How to get a column of the select to be “cumulative” (like in excel)?

This simple query (written in MySQL) is counting the number of WP posts published every day. But I need another information. I want to know the cumulative number of post for each date returned by the ...
0
votes
2answers
888 views

Recursive update for tree structure in PostgreSQL

I have the following structure of table. id chNum parentid --- ------ --------- 1 1 NULL 2 1.1 1 3 1.1.1 2 4 2 ...
2
votes
0answers
26 views

SQL Agent Jobs Automatically Disabled After Modifying Maintenance Plan

Hoping someone can shed some light. I modified a maintenance plan yesterday, to find out this morning (by using the last modified date from sysjobs) that all jobs related to the maintenance plan are ...
0
votes
0answers
11 views

DB design for video-annotations, where annotations have no unique id [migrated]

Right now i have a program in java with 2 classes: public class Video { private String id; private ...
3
votes
1answer
2k views

Azure SQL Administrator Can't Access Master Database

I created an SQL server and database. I can log in to the database manage page with my administrator account, and manage the database I created, but when I try to look at the master database, it says: ...
3
votes
1answer
230 views

MySQL fulltext selection performance

I have 1.5M rows in a table. Following is the table create code: CREATE TABLE `jobs` ( `id` INT(8) NOT NULL AUTO_INCREMENT, `job_id` VARCHAR(50) NOT NULL DEFAULT '', `title` VARCHAR(255) ...
192
votes
15answers
74k views

Can MySQL reasonably perform queries on billions of rows?

I am planning on storing scans from a mass spectrometer in a MySQL database and would like to know whether storing and analyzing this amount of data is remotely feasible. I know performance varies ...
1
vote
1answer
26 views

wm_concat in a specific order

I have a query that makes something like this: +---------+---------+------+ | Patient | Pattern | Rank | +---------+---------+------+ | a | 0 | 1 | | a | 1 | 2 | | a ...
0
votes
0answers
31 views

I want to pivot a table and aggregate a column into strings [duplicate]

I have a select statement producing the following: +---------+---------+------+ | Patient | Pattern | Rank | +---------+---------+------+ | a | 0 | 1 | | a | 1 | 2 | | a ...
2
votes
1answer
27 views

I want to make a new column based on another column

I don't want to make a new table; rather, I want my query to produce the desired output. I have a query that produces a bunch of columns, and then a PROGRAM_ID column which only has 666 or 667. I ...
1
vote
2answers
123 views

Calculating percentage from two tables

I have two tables: First one contains unique mentees under one mentor, so mentors can have more than one mentee in that table. Second one contains interactions of mentors with mentees on different ...
0
votes
2answers
110 views

write to SQL server from another server

I have 2 Windows 2012 virtual machines set up in Virtualbox - one called "AppServer" where I'll be installing applications, and another called "DatabaseServer" - where the application databases will ...
0
votes
0answers
29 views

Challenged with modeling a big DB

I'm tasked to develop a gaming web application system with relatively large database - it should hold the data about 300 million players, the orders/payment of each player - ~3B records - , and other ...
-4
votes
0answers
23 views

Query to update datetime field with yesterday's date [duplicate]

Can you tell me the sql query to convert the datetime field from which current date to be updated to previous date and time part as 21:00:00:000 adjusted to GMT
1
vote
1answer
141 views

Access Value between ranges

I'd like the following population logic built in an Access database: I have two tables. The first, Table1 contains a set of buckets, with a starting and ending value: Start End Bucket ...
0
votes
0answers
10 views

inserting multiple rows with two input fields with same names [migrated]

HTML <tr> <td><input type="text" name="description"/></td> <td><input type="text" name="quantity" /></td> <td><input type="text" ...
0
votes
1answer
33 views

OR Operation in JOINS SQL(MYSQL)

I am using MYSQL SELECT m.proposalId, m.title, n.stageNumber, n.committeeId, n.modifiedDate, o.msNumber , o.description,o.ics,o.edition FROM mystands_Proposal m INNER join ...
0
votes
1answer
348 views

Many mysql queries stuck at sending data

I am using opencart and I can se many queries blocking the mysql server. All queries are the same except the p.manufacturer_id. Attached there is the query and the explain of the query. The query is ...
0
votes
1answer
38 views

Logic Value of Assignment Expression

I'm doing the leetcode SQL problems and kind of confused with the code below: select distinct Num from ( select Num, case when @prevNum = Num then @count := @count + 1 ...
0
votes
1answer
31 views

Updating across row and column in SQL

Bit of a strange question. Entries in this table have three relevant columns; drug, date_1, date_2, seq_id I am trying to make it such that every drug with the lowest seq_id has a date_2 equal to the ...
0
votes
1answer
61 views

How to exclude weekends and holidays in a query

I have been tasked with creating a flat file. One of the fields that I need is a date field. I need the disco date to be LAST_EVENT_PROC_DT + 12 unless that date falls on a weekend or holiday,if so ...
0
votes
0answers
15 views

Convert SQL OPENXML to XML Nodes Value [migrated]

Can anyone help with converting the following from OPENXML to Node protected virtual void ApplySqlXmlSetupSql() { if (!this.query.HasFilterBySectionList && this.query.IgnorePermissions ...
1
vote
2answers
137 views

How to join tables when one of them doesn't have any relationship with the others

First of all, I know what I am about to show you is a completely DB mess but I just got it like this. Tables : Authors manufacturers_id manufacturers_name 1 Name1 2 ...
2
votes
1answer
48 views

Create a grouping based on chains of pairs

Say I have two tables: CREATE TABLE datum ( datum_id SERIAL PRIMARY KEY, datum_text TEXT NOT NULL ); CREATE TABLE datum_pair ( datum_id1 INTEGER NOT NULL REFERENCES datum (datum_id), ...
0
votes
1answer
35 views

How to Store text of 15 to 20 KB file data in blob or as text? [duplicate]

How to Store text of 15 to 20 KB file data in blob or as varchar in sql Server?
0
votes
2answers
121 views

where in (sub query) vs join

just want to ask about how to optimize my query. because i feel efficiency using join instead of "where in" sub query can someone enplane the deference SELECT C.Customer_id, C.tran_id, ...
0
votes
1answer
28 views

Filling not applicable fields with N/A rather than leaving them empty or Null [duplicate]

I have a database of few million records, many of the fields (call them Ec1 to EcN) have either Null or empty string in them. When I looked at them more closely, I found that for many records, several ...
0
votes
2answers
124 views

Calculating values from three related tables, without using join or union

I have a simple basic three tables on SQL Server database: What is needed is to get the quantity available, without using joins or unions which is calculated: Quantity Available = Items Opening ...
1
vote
1answer
121 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'): ...
0
votes
0answers
24 views

Log Shipping Fails to create backup after Server reboot

We made a change last week to point our log shipping backup jobs to a new Windows File Share. For three days everything was running successfully. On Saturday night we performed our monthly ...
0
votes
0answers
20 views

Alternative when IN clause is inputed A LOT of values (postgreSQL) [migrated]

I'm using the IN clause to retrieve places that contains certain tags. For that I simply use select .. FROM table WHERE tags IN (...) For now the number of tags I provide in the IN clause is around ...
11
votes
2answers
7k views

How to keep history of SQL Server stored procedure revisions

Note: I am not asking about full version control. Is there any way automatically to keep a history of stored procedures on SQL Server. Similar to how Google Docs automatically keeps a history of ...
0
votes
0answers
41 views

Moving Database SQL Server 2012 to 2008/R2 [duplicate]

I'm planning to move a Large Database from SQL Server 2012 to 2008/R2. I tried the scripting thing, it works on a small-medium type of database but not on Large database w/c is about a hundred MB. Any ...
1
vote
2answers
104 views

How to know how long my server is started

I am working with postgresql-9.4 and for support and monitoring tasks I need to know how long my server is started? Is there any function or way to know this? Is there an sql way to know it? I ...
1
vote
1answer
54 views

How to insert multiple returns of the select statement?

I have 3 tables. ClassList, Student & Faculty. I'm trying to assign the faculty to a specific class, e.g. I'm assigning "John Doe" to the section "Section1" where this "Section1" exists in the ...
0
votes
1answer
268 views

Replication Snapshot folder not cleaning up

The job that I thought was supposed to clean up old snapshots isn't removing the old files. The retention is left at default yet there are snapshots months old which remain. The job runs as the ...
0
votes
0answers
30 views

Pgsql Function to create multiple child tables inherited from parent tables

I need help with pgsql function to create multiple child tables concatenated with date. Inherited from parent tables. I am using the following queries -- Setting date range SELECT CAST( ...
2
votes
1answer
43 views

Join more than 3 tables in one single query, with many comparisons and no IDs

I use an overlycomplex and poorly designed software at my work, and now I need to produce a report of invoices issued between a period of time. Invoices are held at table ENTR2012, which uses the ...
2
votes
2answers
78 views

String that contains letters representing a negative number

I would like for every string that has a letter on the end make it a negative value. <field> = '00000000014545p';--Varchar(15) <field> = '00000000012645x';--Varchar(15) <field> = ...
0
votes
1answer
85 views

Get list of students having actions in Oracle

I have a table of student actions (id, student_id, type, and quarter). I need to get a list of all the students, along w/ a flag of whether they had a particular action in the current quarter, and the ...
1
vote
1answer
153 views

SQL Server performance is slow when migrated from SQL Server 2012 to SQL Server 2014

We are trying to migrate from SQL Server 2012 to SQL Server 2014 on Windows 2012 R2 OS. After the upgrade, we are doing performance testing and observed huge difference in running a simple query from ...
1
vote
1answer
30 views

Postgres knowing which where column returned positive result

Assuming we have a query like: select * from table where a=1 or b=2 or c=3 and the where conditions met are a=1 and c=3 Would it be possible to know in the query (in the output) that a and c are ...
1
vote
1answer
66 views

Group by maximum consecutive row

I have data like the following: id | src_ip | dst_ip | port | ----|----------------|--------------|------| 256 | 192.168.61.200 | 10.75.64.222 | 80 | 257 | 192.168.61.200 | ...
0
votes
1answer
169 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 ...
-1
votes
0answers
32 views

Split Column Sentence Into Word [duplicate]

Id Name 1 xyz abc xxx www 2 aaa 1 bbb c 3 ggg 4 ccc j I want to split the words in the name column and insert into another table. Expected result: name ---- xyz abc xxx www aaa 1 etc ...
0
votes
1answer
153 views

SQL Query - Possibly the most poorly written query in the history of mankind

Having a major headache trying to tune this query and looking for help, a programmer who had no idea what they were doing messed with this query and it is currently being used in production and it ...
1
vote
1answer
56 views

Why is this select statement not returning what I've provided?

I have 3 tables in my SQL Server I am wondering why is my select statement not working or giving the result with my condition in the where clause. I learned back there to use this kind of method.. ...
3
votes
2answers
3k views

“No row was updated error” in SQL 2012 - worked fine in SQL 2005 and 2008

I have a database that currently lives in SQL Server 2005. We are currently looking at upgrading to SQL 2012. I installed 2012 in a test environment (Windows Server 2012 virtual machine). Backed ...
2
votes
1answer
74 views

Slow PostgreSQL query

I'm building a search interface for a web site that currently searches through approximately 2 million pieces of real estate with approximately 60 million attributes (address, city, state, ZIP, beds, ...