An acronym for "Structured Query Language" (it's not the name of a specific DBMS product), SQL can be used in relational database management systems (RDBMS) to query, update, delete, and insert data as well as modify the structure of the database. It can also be used to manage schemas and data ...

learn more… | top users | synonyms (1)

0
votes
0answers
30 views

SQL: Dynamic Pivot Column, In the column's row value How to get Top 1 value

In my following query I need to get the top 1st record's value in pivot row value. So basically I get all parts and if there is a service entry with that part and a revision number I used that ...
0
votes
1answer
16 views

Postgres Grouping of Like Results

Our database is running on Postgres 9.3. We have a table called notifications. Notifications contains id, type, triggering id, source id, and some other information. What I am trying to do is select ...
1
vote
1answer
106 views

SSRS pivot dynamic columns

I've tried to port this from a similar post, SQL query to return a column per date in date range? however I haven't been able to quite get it to do what I need... Please find some example code below : ...
1
vote
1answer
56 views

Delete from table where two of the three keys in another select

I have tableA which has 4 columns: IDA (bigint), IDB (int), IDC (varchar(255)) and comment (varchar(255)). The primary key is (IDA, IDB, IDC). I'm required to delete the records where the count of ...
0
votes
1answer
31 views

Transform it into 3NF [closed]

Consider the relation LEASE, where a person can live in only one building, and a building can charge only one rental rate: RELATION = LEASE (PersonID, BuildingID, Rent), Primary Key = {PersonID} I ...
0
votes
2answers
107 views

SQL - Cannot Add Foreign Key Constraint

Can anyone tell me why there are errors such as : Cannot Add Foreign Key Constraint And am i on the right track ? DROP DATABASE technology_university_hospital; CREATE DATABASE ...
0
votes
1answer
33 views

Most used tables in MySQL database server

Can somebody please help me with a query that lists the most used tables in a MySQL database? Or how can i find out the most used tables in the database? Thanks
1
vote
0answers
70 views

MDF/TempDB Growth, SQL Server 2005 Enterprise

Here's my scenario: SQL Server 2005 Enterprise Records from Database A being added to Database B Size of Database A MDF file: 3.15 GB I have separate drives for Data, Logs, and System. I ran a ...
2
votes
4answers
207 views

Re-writing a sub-select as a condition on a column generated with 'over (partition by …)'

I am wondering if it would be possible to use over (partition by...) clause in order to avoid using a sub-select in the example below: declare @t table ( id int ,code char(2) ...
0
votes
3answers
29 views

Case when not return more than one value

I try this, but the SwitchCase can not return more than one value. WHERE CP.NumIntAut IN ( CASE WHEN @NumIntAut IS NOT NULL THEN (@NumIntAut) ELSE (SELECT NumIntAut FROM ...
1
vote
3answers
40 views

Get seconds of day in Postgres

In Java we use Joda-Time to get the seconds of a day as an int value (despite the date): date: 10-10-2014 00:00:30 -> second 30 of day date: 11-10-2014 00:01:30 -> second 90 of day date: ...
0
votes
1answer
25 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 ...
1
vote
1answer
50 views

Set bit in Table A if values are present in Table B

I have two tables, Containing up to ~10 million entries. TableA which has 3 columns: IDA (bigint), IDB (int) and CHK (bit). (IDA, IDB) is the primary key. TableB which has 2 columns: IDA (bigint), ...
0
votes
1answer
36 views

Machine account causing a Token-based server access validation failed with an infrastructure error

As the error suggests the SQL log is filling with the error: Login failed for user 'xxx\xxx$'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous ...
1
vote
1answer
43 views

Slow Query with LIMIT

We have this query: SELECT "messages".* FROM "messages" WHERE "messages"."account_id" = 1 AND "messages"."status" = 'unread' AND (created_at >= '2014-04-24 01:00:00.000000') ORDER BY id DESC It ...
1
vote
1answer
24 views

How to group by on two fields and count

CREATE TABLE IF NOT EXISTS `usuarios` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user1` varchar(255) DEFAULT NULL, `user2` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT ...
1
vote
1answer
83 views

Query performance issues

I need help in improving the performance of the following query. It has the purpose to select one record out of a group of records with an identical ID (doublettes). Rules are that Date2 must be the ...
0
votes
0answers
12 views

first query on my table takes apx: 0 sec but taking too much time on or after 3 to 4 query

I have the following table in mysql database: CREATE TABLE `chronologicallists` ( `sno` int(250) NOT NULL AUTO_INCREMENT, `empSno` int(11) NOT NULL COMMENT 'Employee Serial Number', ...
0
votes
1answer
25 views

update column without auto timestamp update

Hello everyone I have got a product stock table with a time-stamp column so the so any updated to that row will update the time stamp ,I'm happy with this setting but sometimes I want to update the ...
0
votes
1answer
43 views

Index file - size changed

I realised today that my index file(which is about 250GB) is smaller than yesterday for about 7GB. Everything, I done in the meantime, was index rebuild. Is it possible that index rebuild is the ...
0
votes
1answer
26 views

numbering distinct column values in an ordering but not just displaying the total

I have a column with non-unique entries, we'll call this column "State" (representing the state where an order was shipped to) and a second column called "Times" (representing the times that an order ...
0
votes
1answer
24 views

How to get all the months from the last paid date in MySql?

I need to get the all the months from the last_payment_Date for the user from that table for processing purposing. As of now i am using this query.. SELECT `Name`, `Acc_No` FROM `user_table_details` ...
0
votes
1answer
26 views

How to model efficient permission checks for a social network?

The goal is to build a facebook like permission system. So that users can grant other users access on objects based on a direct assignment (allow user X to see posts) or allow users to access them ...
1
vote
1answer
39 views

How to Choose an SQL Database for Simple Data Preparation & Analytics

In the interests of not making this a shopping list: please advise on "how" to choose a the right tool, as opposed to which is the best. E.g: Which questions should I be asking myself / client? What ...
0
votes
1answer
34 views

Query to select distinct name based on several factors

I have a table with the following data (simplified here): ID | PolicyName | Setting | State | Value 1 Default DiskUsage Enabled 1 2 Default Memory Enabled 1 3 Default CPU Enabled 0 4 Personal ...
0
votes
1answer
103 views

Delete and merge duplicate records from joined tables?

I am trying to delete duplicate records from the output of the query below. If they also meet certain conditions i.e., 'different address type', then I would merge the records. From the following ...
0
votes
1answer
52 views

Doing a insert across two databases with same collations and getting an error

SQL query : insert DB1.dbo.tbl1 select distinct Number, Name, 'Unknown', 'Unknown', 'Unknown', NULL, 'No', 'GL', 'N/A' from DB2.dbo.tbl1 T2 where T1.Number not in (select distinct AccountNumber ...
2
votes
2answers
150 views

Stored procedure takes 18 seconds on first load, 0 seconds on subsequent loads, waiting several minutes makes it slow again

I have a stored procedure that returns a lot of data for a business dashboard. On first load it takes 18 seconds, after that it takes 0 seconds, even if I change the input parameters at random it ...
0
votes
2answers
54 views

SQL Server database performance is very poor after a month of use [closed]

I am quite new to SQL server and have a question regarding database speed. The database (about 50 MB big) was very fast in the beginning. Now, a month later, the speed is a lot worse. I run a script ...
0
votes
5answers
92 views

Database with only text fields

I started working on an existing SQL Server Database System in which most of the fields are stored as text. Except some IDs all fields are varchar (phone numbers, zip codes, Dates, addresses, monetary ...
0
votes
3answers
44 views

how to order a field based on two parts of the row value

I have a field with contents of the form 22/4, 30/8, 30/5, 30/3 I want it ordered in the form 22/4, 30/3, 30/5, 30/8 with the part after / ordered if the part before / is same! Please help!!
0
votes
2answers
69 views

Is there anyway to search PL/SQL reserved words with contains function?

Is there anyway to search PL/SQL reserved words with the contains function in the Oracle? We would like to search keywords such as "test for" or "test do" with the contains or catsearch functions ...
5
votes
2answers
216 views

Strange query results for DELETE with ROW_NUMBER

In the following DELETE statement I try to delete all rows except the first one ordered by some criteria. (The actual query makes more sense, this is just a repro. All the sys.objects stuff is just ...
-2
votes
1answer
41 views

Subqueries in SQL Server 2014 [closed]

I want to get results that is in more than one row using a subquery. here is what: Note. This is in microsoft SQL Server 2014. Relations: FeeStudent(Feeid, Studentid, MODE,LEVEL, SEM, Paid, Status) ...
2
votes
1answer
34 views

Best way of finding rows referencing a given id on PostgreSQL

I'm trying to find if there would be anything violating the foreign key constraint when deleting a row, to check if I can delete it or not (can't use ON DELETE CASCADE) For that, I have a piece of ...
1
vote
2answers
73 views

Fourth inner join to the same table increases query duration significantly

As soon as I add a fourth inner join to a specific table, the query duration increases significantly: sub 1sec to 45sec. The table in question is deployments d4. The SQL Query: select a.id as ...
1
vote
3answers
56 views

Performance first low, then high after adding index, then keep high after drop index

I am testing performance a complicated select query. It is very slow. using explain, I have found a ALL in type COLUMN. Then I ADD INDEX. result comes out fast. However, when I DROP the INDEX I ...
2
votes
2answers
63 views

Oracle: Helping hand writing this query. Do I need a sub query here?

I have the below query that returns the number of orders created on a particular customer number per month for the past 2 years. SQL SELECT TO_CHAR(o.entry_date, 'MM'), SUM(CASE ...
1
vote
1answer
29 views

group records according to column

hi how can make this result query: to this: I have this code and i know it's still farfetched: select max(case subcod when 'DISABILITY' then A end) as disability, max(case ...
2
votes
2answers
226 views

Join 3 tables and return only distinct values for max datetime

I've been trying to do this the whole 2 weeks but seems like to no avail. 3 tables involved here. but am expecting the result would be distinct case no. for the same date & user. otherwise, it ...
0
votes
1answer
58 views

SQL Server 2008 and constant expressions

If I have the following scenario, would Replace be evaluated a single time? DECLARE @text nvarchar(32) = 'This is a test field' SELECT textField FROM Table WHERE Id = @idICareAbout AND textField = ...
0
votes
0answers
26 views

Receiving duplicate results in one condition of JOINED query

I always end up here when Googling for solutions, so I thought I'd cut out the middleman since I'm unable to fix this via searching/forums. It's my first time posting, so please forgive any faux pas ...
2
votes
0answers
74 views

SSRS URL Changes while navigating

I am trying to track down the root cause of this issue I keep seeing. We have SSRS running with a custom URL like https://reporting.companyname.com/reports. When I connect to that address I can ...
3
votes
1answer
108 views

Need help with a Running Total Issue

Using SQL-Server 2008 R2 and Report Builder 3.0 Here is my query without the running total column: SELECT CostManagement_CommitmentDetails.Id ,CostManagement_CommitmentDetails.LineNumber AS ...
1
vote
1answer
61 views

2 Date Ranges - How many days from one range excluding weekends overlap

First - Thanks for your help. I'm looking to create a query in SQL that provides the me the # of days from one date range that overlap another date range and excludes weekends. Example Data: ...
0
votes
1answer
39 views

MySQL: Rank by Department in an employees table

I need Mysql query to rank the hours used by each department in an employees table, but it just ranks all without taking into consideration the departments.
1
vote
1answer
38 views

How to query a list of all the different errors?

I have a DB which is very big and stores a lot of eventlogs, around 15 million rows. When I try this query, it is totally impossible to get some results. Any idea how I could do this? Maybe indexed ...
1
vote
3answers
32 views

Study material for Advanced Databases module inc SQL, Relational Model, ERM & TM [closed]

For my next module in my masters, I am planning on studying Advanced Databases. (Long story short, the masters is designed for people with NON-CS degrees, so I have never studied databases, therefore ...
-2
votes
1answer
35 views

Oracle Concurrent Insertion

We have simple case like Create table t1 (c1 number primary); session1: insert into t1 values (1); //session1 do not commit transaction session2: insert into t1 values (2); Will Session2 ...
2
votes
2answers
43 views

SQL Insert Statement Persists Two Records When I Need Just One

I have altered it to include hardcoded variables when I pulled this chunk of code from the stored procedure. I ran it independent of the application with the same result. I need one insert statement ...