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
51 views

SQL database normalisation within C#

Ok so I don't have code for this as I am unsure of how it would even go. Take this scenario I am working on. A purchase system for let's say tires. I have one table named tStock and another one for ...
0
votes
1answer
38 views

Distinct rows by order

I have the following SQL, generated from ActiveRecord in Rails 4 (partly with the Geocoder gem) problem is, that even though DISTINCT is used, hopefully to get only unique items, the items returned ...
1
vote
1answer
147 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 ...
0
votes
1answer
66 views

use parameters in a Cte

I want to use parameters in the below cte but I get the below error messages Msg 102, Level 15, State 1, Procedure X82 7 Days/by Day, Line 5 Incorrect syntax near '('. Msg 137, Level 15, State 2, ...
0
votes
0answers
49 views

How to find pairs of attributes who did the same thing in a same entity?

For example, there are 4 tables below: User(uid, user_name) Friend(uid, friend_id) Like(pid, uid) Post(pid, uid, text) And my question is how to find pairs of user and his friend who have liked ...
0
votes
1answer
69 views

What is the best query to get the latest row version based on time?

I'm wondering what can be the most efficient query to get the latest rows from the following table. CREATE TABLE AGE_DATA ( INSERTED_DAY TIMESTAMP(6), HOSTNAME VARCHAR2(255 CHAR), CONCEPT ...
2
votes
3answers
134 views

Aggregate query with min

Having a product_stock table with following structure: product_id warehouse_id stock price Which has bunch of records (same product can be in different warehouses): 1, 1, 0, 500 1, 2, 5, 505 1, 3, ...
0
votes
1answer
18 views

SQL Help in Aceess

How can I write an SQL in Access that shows the manager's Last and First Name, salary of the manager based on the Department and Budget? EMPLOYEE PK FK: ...
3
votes
1answer
264 views

how can an additional natural join with the same table twice *reduce* the row count

This is real results from my database. I spend some time trying to reproduce this behavior with an SSCCE but failed. How is the last of the below results possible? SELECT COUNT(*) FROM rr.resource ...
0
votes
0answers
50 views

Log shipping Readonly/Standby executes Queries slow for some users

I have a SQL Server database that is doing log shipping to another SQL Server instance, both servers have identical hardware. We have a reporting service that then runs reports against the readonly ...
2
votes
2answers
53 views

Adding Users after Database Restore

I have to restore our production database to a test instance on a daily bases. This test instance gives people access to the data that we do not want changing production data. When I restore the ...
0
votes
1answer
28 views

How to define destination for an append query Microsoft Access

I'm trying to append two tables in MS Access at the moment. This is my SQL View of my Query at the moment: INSERT INTO MainTable SELECT FROM Table1 INNER JOIN Table2 ON Table1.University = ...
0
votes
1answer
19 views

Access query with “last” and display the rest too [closed]

I have an Access DB about measuring tools. I have 2 tables: tblTools, which countains the details of the device, the tblCalibration contains the which device when was calibrated. I wrote a query of ...
-1
votes
4answers
75 views

What permissions does db_owner not have? (In SQL Server 2008) [closed]

Can you list the permissions that db_owner cannot do.
2
votes
1answer
48 views

How do I retrieve the loop record in the connect by clause?

I have a employee-->Manager hierarchy and using the following query: SELECT EMP_ID, FIRST_NAME CONNECT_BY_ISCYCLE "CYCLE" FROM MYTABLE CONNECT by NOCYCLE PRIOR MANAGER_EMP_ID = EMP_ID START WITH ...
0
votes
2answers
25 views

Single select from template record oracle

Due to lack of terminology in my head, let's say I've got 1 record from users table with these attributes : Name | Surname | Height | Weight Person X 6,2 190 How do I find other ...
2
votes
1answer
45 views

Find string of decimal digits in Pi

I am trying to find the first occurrence of "12345" in the decimal expansion of $\pi$ by using MySQL. Possibly by using the Instr function? But I am stuck because it only displays the first 7 ...
0
votes
0answers
16 views

parent / child aggregate column

To start with, here is my fiddle. I have a parent / child relationship setup, I have them ordered by who they belong to, and I want to have a column called lastUpdated in which the parent has the ...
4
votes
1answer
60 views

SQL - Inverse Selection

I have two tables in my schema students(id text, matric text) attendance(id serial, a_date date, matric text references students matric) To get records showing date and student present, I can run ...
0
votes
0answers
29 views

How to move records to another database and free some space

We do monthly a "cleanup" of the database: we set the database in simple recovery mode and then we move records to an historic database so we can delete from the original for free some space with DBCC ...
0
votes
0answers
10 views

reorganize and UPDATE [duplicate]

This is my table called tickets with ID primary key ID-TicketID-TYPE 101-3-0 a02-1-0 01a-2-0 I wanted to UPDATE it and be like this ID-TicketID-TYPE a02-1-0 01a-2-0 101-3-0 but when I used the ...
3
votes
0answers
77 views

Log shipping and SIMPLE recovery mode

We have the need of implement the log shipping in our production environment, and we'll do this on the next weeks. In the other hand, we do monthly a "cleanup" of the database: we set the database in ...
0
votes
0answers
61 views

Why am I unable to use a temporary table more than once on my Stored Procedure?

I am using SQL Server 2008 to build a stored procedure. On it, I create a temporary table (#results) using recursion for getting all the ancestors given a specific record. My specific problem is, ...
0
votes
0answers
99 views

Query to match exact column or partial data of the column: SQL Server / Azure

Bit confused on how to get it work better than what has been done now. I am new, apologies if I'm asking for too much. I have trip details which have from and to locations stored in database from ...
0
votes
1answer
36 views

Errors importing data using COPY comand at postgresql 9.3.5

I am trying import a database table to Postgres 9.3.5 database server using COPY command as follows: COPY comment (generatedid, id, "timestamp", message, bugreport_id, personcontainer_id) FROM ...
0
votes
2answers
44 views

Joining 4 tables and selecting distinct values based on 3 timestamps

I have been trying to remove duplicates from my query results based on the time but I would need to consider 3 different times and i am not sure how to do that. As some of the testing happen multiple ...
0
votes
2answers
54 views

Match multiple components of a subquery CTE in PostgreSQL

I've got an app that grants access to users in varying different ways, and I'm attempting to write a query that determines access in all the different ways, but is readable, by breaking the access out ...
1
vote
0answers
31 views

Poor performance filtering mysql spatial join

First of all, sorry for the crossposting. I ask this question in stackoverflow and they sent me here I have a mysql table with spatial references (GEOGRAPHIC_FILTER), like cities, countries, natural ...
0
votes
1answer
130 views

How calculate the time difference between two consecutive rows

I have a table tickets with ticketid,owner, owndate columns. The table is as.. ticketid owner ownerdate 1001 LEWIS 2004-06-18 14:15:11 1001 WILSON 2004-06-18 14:16:54 ...
-1
votes
2answers
72 views

Getting all descendants of a parent

I have a table with two columns, Parent and Child. Need to get the list of all descendants associated with the parent records, Source Table +----+-----------+ | Parent | Child | +----+-----------+ | ...
0
votes
0answers
133 views

Centos 7, Postgresql Failed to Read Permission Data Directory in mounted Partition

I've a problem with start PostgreSQL 9.4 on Centos 7 Server. Because the partition /var/lib/pgsql/9.4/data/ (which contains PostgreSQL data) is full so I want to change to another partition. ...
2
votes
1answer
91 views

Keep Execution Plan in Cache - SQL Server 2012

How can I keep a query execution plan in SQL Server's cache and use it several times? When I execute a query for the first time it takes 7 seconds. After that it takes only 1 second. This query is ...
0
votes
1answer
39 views

Displaying only the most recent activity of an item

I have made a database that tracks the shipping and receiving of the tools that my company has. I am now wanting to make a query that shows only the most recent activity of all the tools. I have a ...
0
votes
0answers
42 views

What is the difference between normal Select query and a Select query inside Common Table Expression(CTE)? [duplicate]

I'm having a table 'Products' and if Execute this query SELECT TOP 1000 [ProductID] ,[ProductDesc] ,[ManufacturingDate] ,[ExpiryDate] ,[Price] FROM ...
1
vote
1answer
67 views

Grouping() equivalent in PostgreSQL?

I have a SQL query that runs in MS SQL Server and it has grouping(), rank() and rollup() functions / keywords in it. The query is given below. How can this be translated to PostgreSQL? select top ...
0
votes
2answers
43 views

Grouping rows by looking at two columns without considering the order and summing each separately

I have a table looks like this one below: +---------+---------+-------+ | SOURCE | DEST | VALUE | +---------+---------+-------+ | A | B | 1 | | B | A | 2 | | A ...
0
votes
2answers
37 views

wildcard expansion using sql

I've a table named permissions. CREATE TABLE `permissions` ( `id` int(11) NOT NULL AUTO_INCREMENT, `role_code` varchar(45) NOT NULL, `permission` varchar(100) NOT NULL, PRIMARY KEY (`id`) ) ...
1
vote
1answer
54 views

Convert Varchar Duration time to time

I need my code to add the varchar CPUTM field + varchar CPUZIPTIM field which both has time values to see if greater than 2 hours. How do I do this when both fields are varchar. The value in CPUTM ...
2
votes
1answer
68 views

Better way to select from nested tables in PostgreSQL

I have the following schema with millions of rows in the match table and each match have two sides (away and home). I want to create a view which shows the most significant data regarding the matches ...
1
vote
2answers
33 views

Calculating the number of times a motive was cited over a given period

I'm using PostgreSQL 9.4. I have the following surveys table. Each row includes a motive that was cited by the respondent, represented by the motive_value column (think of it as motive_id). ...
0
votes
1answer
79 views

How to achieve using TRIM command on SELECT * From TABLE

I am building DWH where in source data dump is made through SELECT * query into DB. I am taking dump from the source DB, to save time (else had to write 115 select stmt using TRIM where average number ...
0
votes
1answer
61 views

Index rebuild canceled because of .mdf growth

I have the Standard Edition of SQL Server 2012 and used a script to rebuild index. I ran the query and then I saw that the .mdf file for the database started the grow a lot. I chose to cancel/stop my ...
1
vote
2answers
70 views

Combine results of two queries, where the second relies on the first

I have one query which is returning name(text), total, created_at And another query which is returning the same thing name(text), total, created_at The second query usually is returning more ...
5
votes
2answers
133 views

Sporadic execution time causing timeouts [closed]

We have a table with 100 million records holding 24 hour worth of data of our clients' customers actions (actions which are performed on 3rd party sites and replicated from there to our server) The ...
1
vote
2answers
54 views

Combine multiple similar queries into one

I need to calculate a weighted average using the following table. I have to calculate this number for metric_1 up to metric_10. All the metric columns have a value from 1 to 5. ...
0
votes
3answers
50 views

Group by transformed column

I have a database with creations and orders, and a bunch of tables linking them. I want to extract, for each month, the number of ordered creations, and the sum of amount for these orders, but for a ...
0
votes
1answer
28 views

Dropped column still has value when recreated with Postgres table of 150M rows

I need to set the column to NULL. Until now, this has worked, but for some reason, on this table which is much larger than the rest, it doesn't seem to be working: ALTER TABLE ...
0
votes
0answers
48 views

sql taking < 1 sec to run in sql developer, 37 on the application side

As the title says, we have a big discrepency between a sql query we're running from our java application (jdbc thin client) and the same query run in sql developer. I clear the cache of the query ...
1
vote
2answers
65 views

What is the best way to compare values of whole tables between many databases?

We are trying to compare the values of whole tables between many databases. The user can enter in input the table names and the columns names, and the database names he wants to compare. They can ...
0
votes
1answer
101 views

More Best Practices To Updating Large Tables?

I know there are a number of topics on this question, but I'm always seeking more insights. I have a large table with a billion+ records. The amount of records could be reduced and archived, but the ...