Tagged Questions

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)

2
votes
1answer
82 views

Why am I getting cardinality issues when querying a single table

I have the following query: SELECT [iri].[RateItemKey], [iri].[RateItemType] FROM [prism72].[dbo].[ispRateItem] AS [iri] WHERE [iri].[Status] = 1 AND ( ...
4
votes
2answers
111 views

CTE works as expected, but not when wrapped into a function

--testing table CREATE TABLE public.test_patient_table ( entity_id INTEGER NOT NULL, site_held_at INTEGER NOT NULL, CONSTRAINT entityid_pk PRIMARY KEY ...
3
votes
3answers
476 views

How to achieve many to many relationship in this database design?

I am currently working on small travel application in which users can add other users' trips in their wishlist. I am facing difficulty in designing database for wishlist. What I have tried so far is: ...
-1
votes
1answer
47 views

Calculate number of items with distinction using SQL [closed]

I have a product Table like this; Product | Operation | Component A | 10 | C1 A | 10 | C2 A | 11 | C3 B | 12 | C3 B | 11 | C1 B ...
0
votes
0answers
12 views

How to import to UNIX the results from a select query?

After a select query i get around 14k records.Rather than exporting them as insert statements into a file and then executing from UNIX, i want to import them to UNIX directly and insert so as to save ...
0
votes
1answer
42 views

Work with table named Group [closed]

I have a table named Group in my database , when I try to work on it it gave error : SELECT GroupID,GroupName FROM Group it said : Msg 102, Level 15, State 1, Line 1 Incorrect syntax ...
1
vote
1answer
24 views

Correlated query Issue [closed]

In the below query i have 2 tables product and productstatus. In product table I have productId and productName and in productstatus table there are locationid,productid,currentstock and ...
2
votes
2answers
54 views

Is a Postgres long-running query aborted if the connection is lost/broken?

If I open a connection to Postgres and issue a long-running query, and then break the connection (e.g., kill the client process that opened the connection), will the long-running query continue to ...
0
votes
1answer
69 views

Why is Excel File Opened Exclusively By Another?

I'm creating an SSIS package to loop through a series of Excel files (.xls). I create my while loop container and then create my data flow, which includes an Excel source. When I edit my Excel ...
-3
votes
1answer
45 views

Char in select why [closed]

In sqlplus query eg: select a.phonumber from customers a where User = 'mark' Why using the character a -- what is benefit? Thanks
1
vote
2answers
117 views

Currently all connections getting “Timeout expired”

I have an application which connects to SQL Server. Currently I am getting, Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because ...
2
votes
3answers
181 views

SELECT all substrings (n-grams) of length n?

How could I select all n-grams, ie. substrings of length n from a string using SQL? For example, the 3-grams of string example are exa, xam, amp, mpl, ple. I'm using PostgreSQL to be more precise.
1
vote
1answer
61 views

WITH RECOMPILE option in SQL Server [closed]

When you have multiple connections concurrently trying to execute the query by using different parameters in the filter condition with recompile option, Is there are any chance that it may produce ...
2
votes
2answers
64 views

Postgresql and UTF8 to Latin1 conversion?

We have a Postgresql database where the data is in Latin1. Apparently someone loaded some UTF8 encoded data into it since when I select data from some of the columns, I get stuff like: SELECT ...
0
votes
1answer
43 views

SQL Query - How do I Select Into new columns

I'm trying to write a query that will put values in three different columns. I have the query pulling the information I need, I just don't know how to split the values out into different columns. ...
1
vote
2answers
49 views

Insert New Foreign Key Row for new Strings

Lets say I have a table new_data like this: ndx | someval | somemachine serial | integer | integer ---------------------------- And let's say I have a reference table new_data_someval_ref: ...
1
vote
3answers
75 views

Database design: 4 types of users but have different functionality , separate or one table?

I have 4 types of users: Admins , normal user , company , service provider Admins and normal user share some attributes (id .first name ,last name ,phone ,mail) company and service provider share ...
0
votes
1answer
51 views

Select Statement to generate result, for Group by per second and link with Table

i tried to search in existing queries but didnt find any related thread. i would like to run a query in user table (for example), and like to get output result on per second bases. and give me zero ...
0
votes
1answer
44 views

SQL Server stored procedure that would enforce atomicity?

Let's say I have following insert statements INSERT INTO tEmpl (lastName) VALUES('Smith') INSERT INTO tEmpl (lastName) VALUES('Jones') INSERT INTO tEmpl (lastName) VALUES('Smitty') INSERT INTO ...
-2
votes
1answer
58 views

Why is this SQL query illegal? [closed]

Students is a table with two fields name and grade for a number of students. I wonder why the following query in SQL is said to be illegal: SELECT Students.name, MAX(Students.grade) FROM Students; ...
0
votes
0answers
26 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
98 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
96 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
32 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
69 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
206 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
27 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
39 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
24 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
30 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
42 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
21 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
97 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
50 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
147 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 ...