Structured Query Language (SQL). For questions about the language, not Microsoft SQL Server (tag sql-server).

learn more… | top users | synonyms (2)

5
votes
1answer
1k views

Splitting a csv column from select query into multiple columns

I have a stored procedure SELECT [ShipWorks].[dbo].[OrderItem].[Code] AS Code, CASE WHEN (LEN(ISNULL(MAX([ShipWorks].[dbo].[OrderItem].[Location]),'')) = 1) THEN ...
5
votes
2answers
14k views

Restore Backup Fails - File 'Db' cannot be restored to '{…}.mdf'. Use WITH MOVE to identify a valid location for the file

I am getting the following errors trying to do a restore using the restore command on a backup file I recieved from a customer. RESTORE DATABASE SFDB FROM DISK = N'C:\Backup\Backup.bak' Any ...
4
votes
2answers
487 views

Postgres 9.4.4 query takes forever

We're running Postgres 9.4.4 on CentOS 6.5 and have a SELECT query that has worked for years, but stopped working and hangs after we upgraded from 9.2 (it took a while to notice it, so I don't know if ...
4
votes
3answers
644 views

Insert data between 30 minute time intervals

I want to divided my to my time interval depending to the start time and end time. To explain it further, let me have this example. I have this table: User Elapsed Time Start Time ...
4
votes
0answers
151 views

How to Audit for Access with Cross Database Ownership Chaining

I've just joined the data team of a new company and we have a large SQL Server environment (100+ instances) and cross database ownership chaining is on by what I gather is a necessity. I'm looking for ...
4
votes
1answer
988 views

Microsoft SQL Server 2008 - Catching user role changes with SQL Server Profiler?

I'm trying to figure out why one of my SQL server users keeps losing sysadmin role when I uninstall a particular program. Can I use the SQL Server Profiler to track down the action that causes this to ...
3
votes
1answer
555 views

How will the order of operations in this query affect my results?

I've written a query, and I'm kinda sure that it does what it want. I'm thinking, "huh, I wonder if I know how Postgres works?". I'm going to list a specific assumption about this query below, and ...
3
votes
2answers
10k views

Query (duration) different between data value change

I am trying to write a query that would get me the duration of a status in a table. This query needs to work in SQL Server 2008. Say I have the following table: Key Value RecordDate 1 1 ...
3
votes
2answers
5k views

Query: All employee with sum of salary > 500

In my MYSQL Database COMPANY, I have a Table: Worker : mysql> DESC `Worker`; +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | ...
3
votes
3answers
3k views

View foreign key constraints so I can delete a table

I have copied a database I was working on so I could change some key elements of its design for my new version. I want to delete totally some tables from the database however I cannot due to some ...
2
votes
2answers
1k views

Optimize simple query using ORDER BY date and text

I have a query to a table in Postgres with an order based on a date field and a number field, this table has 1000000 records The data types of the table are: fcv_id = serial fcv_fecha_comprobante = ...
2
votes
2answers
4k views

MySQL case sensitivity for table names

Why is it that different platforms (linux vs windows) does not support the same naming conventions? I'm stunned by the fact that the documentation uses file names as an excuse for this. Yes, windows ...
2
votes
2answers
6k views

How do I force a JOIN to use a specific index in MySQL?

I have a query that JOINs 2 tables; lineitem and part, select sum(l_extendedprice* (1 - l_discount)) as revenue from lineitem force index for join (l_pk), part where ( ...
1
vote
1answer
1k 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, ...
1
vote
1answer
897 views

Bulk insert in multiple tables

I have two tables on which I perform some bulk inserts: key: key_id(pk), key_name related_key: key_id(fk), related_key_id First Insert: values_data = "($$key_1$$), ($$key_2$$)" INSERT INTO key ...
1
vote
1answer
7k views

Set permissions on all objects for SQL users

I have a procedure that loops through all objects in the database and assigns them proper permissions to that object. I want to know if there is a better way to do this? I use a model database to ...
0
votes
1answer
503 views

can i shrink log file in full recovery model

i have configured Always on Availability group where iam unable to shrink log file size through simple recovery model. can i shrink the log file size in full recovery model if yes or possible then is ...
0
votes
0answers
213 views

Character set for 11.2 version database in oracle for sql* loader

NLS_NCHAR_CHARACTERSET AL16UTF16 NLS_LANGUAGE AMERICAN NLS_TERRITORY AMERICA NLS_CURRENCY $ NLS_ISO_CURRENCY AMERICA NLS_NUMERIC_CHARACTERS ...
-2
votes
1answer
63 views

Need to list the Similar Sub Groups

Kindly help me to get the list of similar groups together. Please see the above illustration. Here, BaseItem 6586, 6587, 6588, 6589 are sharing similar kind of SubItems (SubSet 1, SubSet2, SubSet3 ...
11
votes
2answers
326 views

Implementation of a many-to-many relationship with total participation constraints in SQL

How should I implement in SQL the scenario depicted in the following Entity-Relationship diagram? As it is shown, every A entity type occurrence must be related to at least one B counterpart ...
8
votes
6answers
8k views

Get last full backup and transaction log backup for each database

How to write script or a query that will display the last full backup and the last log backup for each database on each of the production servers using the system table "backupset" and related backup ...
7
votes
2answers
1k views

MySQL connection doesn't work anymore

I had two MySQL servers running (127.0.0.1:3306 and 127.0.0.1:3307). Now I get this strange error: mysql_connect(): Lost connection to MySQL server at 'reading initial communication packet' I can ...
6
votes
1answer
22k views

QUERY - pivot multiple columns, variable number of rows

I have a table that looks like this: RECIPE VERSION_ID INGREDIENT PERCENTAGE 4000 100 Ing_1 23,0 4000 100 Ing_100 0,1 4000 200 ...
6
votes
1answer
684 views

How does SQL Server order results when joins are used?

How does SQL Server figure out the order of the records in the result set of query execution? I am trying to make heads or tails of it but find myself scratching my head. When I change the fields I ...
5
votes
2answers
504 views

Likes or votes for posts

I am making a small program where users makes posts or write blogs. On those posts, other users can like or dislike the post as in facebook or upvote or downvote the post as in stackoverflow. I would ...
5
votes
2answers
415 views

Reseed identity column in all empty tables

I need to write a query that resets the identity on all tables that have an identity column and are empty.
5
votes
4answers
4k views

How to check for existence of an INDEX on a column independent of used SQL database

Is there are unified way of how to check for the existance of an INDEX for a given column irregardless of the actual SQL database system used? For MySQL one could for instance check for the existance ...
5
votes
2answers
28k views

How can I dynamically alias columns?

I have a table (not designed by me) which has 20 variably named columns. That is, depending on what type of record you are looking at, the applicable name of the column can change. The possible ...
5
votes
1answer
540 views

Disabling SET Statements / Forcing SET Options in SQL Server 2008R2/2012

I am currently working with a MSSQL 2008R2 Database which uses the following SET-Commands frequently: SET ANSI_NULLS { ON | OFF } SET QUOTED_IDENTIFIER { ON | OFF } SET ANSI_PADDING { ON | OFF } ...
5
votes
6answers
3k views

Backup very large table

I have to update certain values of a large table (for the sake of a presumed example, it is called 'Resource' and it is over 5M rows) and thus I have to make a backup before performing the changes. We ...
5
votes
2answers
124 views

Algorithmic order of table indexes for table operations

I have not been able to find a clear indication of the impact table indexes have on the algorithmic order of table operations. I am not interested in the nuts & bolts of specific implementations; ...
5
votes
2answers
872 views

Introducing an 'age' category in a patients database

I want to do the following in my DB related to patients: when patient inserts his birthday it shows his age category as: infants, children, adult, elderly... etc. to help me classify my patients how ...
4
votes
1answer
85 views

DISTINCT ON vs ROW_NUMBER() for getting a row with a maximum value of a group

I have the following table in PostgreSQL 9.4: CREATE TABLE dpa( id serial NOT NULL, currency_id integer, amount numeric(14,3), date timestamp without time zone, plat_id integer, pl_id ...
4
votes
1answer
115 views

identify gaps and islands

I have a database that has the following structure +---------------------+------------+---------------+ | timestamp | EngOilP_sd | CompOilLVL_sd | ...
4
votes
1answer
56 views

the <set clause>'s <multiple column assignment>

I just know a little sql basics and need to write a plain sql syntax highlighter/checker So I'm fighting through the standard... I stumbled over somthing I'm not sure I understand it right, as I never ...
4
votes
1answer
9k views

Join tables in two databases make query slow? is better to partition db?

I have a table1 in db1 and table2 in db2 on SQL Server 2008 R2. If I make a select query joining both tables, it's really slow to get the results. A simple query like SELECT * FROM db1.dbo.table1 ...
4
votes
5answers
7k views

How to get the total days per month between two dates?

Given two dates like 20120302 and 20120605 I need to get a list of Months and the total days in those months that fall between those two dates, like so: March 28 April 30 May 31 June 03
3
votes
1answer
66 views

Correlated subquery to find total larger than state average

I have tables Vendors (VendorName, VendorState,....) and Invoices (InvoiceID, InvoiceTotal,...). I want to get the Invoices (as InvoiceId) that are larger than the Avg state InvoiceTotal. I know I ...
3
votes
2answers
280 views

Do I need a composite primary key in a junction table?

I have a junction table like this: CREATE TABLE `entitytagjunction` ( `EntityId` bigint(20) NOT NULL, `TagId` bigint(20) NOT NULL, PRIMARY KEY (`EntityId`,`TagId`), KEY ...
3
votes
1answer
15k views

SQL Server error log entry : Error: 17806, Severity: 20, State: 14

I have error in my log for a few weeks, I searched a lot but I couldn't found useful answer. I did closed SQL Server port for public IP, but I have problem yet. Error: 17806, Severity: 20, State: ...
3
votes
2answers
519 views

Slow fulltext search due to wildly innacurate row estimates

Fulltext queries against this database (storing RT tickets) seem to be taking a very long time to execute. The attachments table (containing the fulltext data) is about 15GB. The database schema is ...
3
votes
1answer
10k views

Writing a query to find all employee salaries that are higher than their managers

I have two tables with some dummy data. I need to find all employees that make more than their managers. I was led to believe the point of the exercise is to learn how to do subqueries. I can isolate ...
3
votes
1answer
5k views

Concatenate one-to-many field in single result?

Say I have the following query: SELECT * FROM AppDetails, AppTags WHERE AppDetails.AppID = '1' AND AppDetails.AppID = AppTags.AppID Which gives the following results: AppID AppName ...
3
votes
3answers
14k views

Bulk update of all columns

I am wondering if PostgreSQL has an update query somewhat like their insert values syntax. I have an updated set of data in this form currently: INSERT INTO ...
3
votes
2answers
4k views

Postgres 9.2 select multiple specific rows in one query

I have a table with three columns, Id Serial. Value real. timein Timestamp. I want to select the values based on a range of days ie. from two days ago until now. The table may contain one or two ...
3
votes
1answer
7k views

Select first row (grouping) + add aggregate function

First have a look at this question on StackOverflow. I'm looking to accomplish the same task, except I also need to add an aggregate function (PostGIS's ST_Union) to my query. How can I combine the ...
3
votes
2answers
4k views

Get all partition names for a table

I want to list all the partitions created by dynamic triggers in PostgreSQL 9.1. I was able to generate a count of partitions using this related answer by Frank Heikens. I have a table foo with an ...
3
votes
4answers
2k views

What is the best permissions to set for only creating and managing own databases?

I want to create a login for a new user who could only create and manage their own databases. Other databases on the server should be read-only to that user. What would be a good set of ...
3
votes
2answers
2k views

Running a CTE query in a loop using PL/pgSQL

I'm trying to execute query that is repeatedly called in a loop using plpgsql -the loop iterates over another table (named coordinates) that contains top left and bottom right latitude/longitude ...
3
votes
1answer
4k views

Import CSV File

Currently, we have a DTS package on SQL Server 2000 that imports a file called suicoweb.csv. The DTS csv file properties pages look like this. Here is a sample line from the CSV file ...