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

learn more… | top users | synonyms (1)

0
votes
0answers
8 views

Collect data with central management servers and keep them in local table [duplicate]

I have 50 servers. I manage them from a central management server. I could open a query and get result for each one at the same time. But I want to do it in an automated way, like a job, to collect ...
4
votes
3answers
67 views

Collect data with central management servers and keep them in local table

I have 50 servers. I manage them from a central management server on SQL Server 2014 Enterprise Edition. I don't use repository. I could open a query and get result for each one at the same time. But ...
1
vote
1answer
44 views

SQLite query rows nearest to hour

My question is similar to the one asked here. I'm new to SQL and I'm creating a weather app that stores part of the data locally in a db, so I'm writing the query using the sqlitedatabase java class ...
-2
votes
1answer
32 views

Merging two rows when there is a duplicate value in one of the columns [closed]

Consider a table T1, having columns C1, C2, C3... C1 C2 C3 CAD PF1 100 CAD PF2 200 USD PF3 300 Result should be C1 C2 ...
7
votes
1answer
86 views

Why does adding a smallint column to a postgres query increase execution time 60X?

Here I have what seems to me to be a routine table and a table creation script that I have put in /tmp/try.sql. I am running postgres under OSX 10.10.2. Here is the table: cow_dev=# \d carc ...
2
votes
1answer
55 views

I need help with a SQL-query that results in an error

I get the following error when I try to delete a record in the user table that has an entry in the feedback table: e.g. when a record in the feedback table has a reference to the user table ...
1
vote
2answers
35 views

Update audit database name in triggers and views of main database?

I have a a system with two databases, main database and audit database. A lot of the triggers and table views in the main database and audit database are referencing from one database to the other. No ...
1
vote
3answers
66 views

How to retrieve the latest guid value after you have inserted a new row in realtime

Goal: Retrieve the latest guid value in real time after you have inserted the the value in the table Problem: Don't know how to do it Info: *You can only add a new vale that is address and zip code ...
0
votes
0answers
17 views

How to dynamically query values into their own columns [duplicate]

First of all, I'm aware that the wording may not be perfect, I'll glad to fix it with better suggestions. I have a table that relates some costumers to some products, like so: costumer_id ...
1
vote
1answer
69 views

SQL current timestamp

I have made a featureclass for a utility company where they can register measures set up to mitigate problems with rats, called "mitigation measure". From this feature class it is possible to state if ...
2
votes
0answers
39 views

Database Corruption - object in sys.parameters does not have a matching row in sys.objects

I'm using SQL Server 2008 R2 and I have a strange db corruption. I ran dbcc check db and the output is: Attribute (object_id=1096186) of row (object_id=1096186,parameter_id=1) in sys.parameters ...
4
votes
1answer
392 views

Why did adding a secondary index not increase select performance?

I have a table in MySQL called messages, which looks like this : id (primary key) | description | created_at(timestamp) This table is supposed to hold chat messages between users of my ...
0
votes
2answers
51 views

SQL select default parameter [closed]

I have a table with columns like name, age, email etc. For some columns I also have parameters to control the behaviour of queries. I want to show the column's value if the corresponding parameter has ...
0
votes
1answer
41 views

Three queries returning 1 row each

So.. I've got: 3 DB Queries returning 1 row each .... SELECT value FROM ja_settings WHERE varname='clickatel_api_id' SELECT value FROM ja_settings WHERE varname='clickatel_user' SELECT value FROM ...
7
votes
3answers
155 views

How to convert default instance to named instance in SQL Server 2012?

How to convert default instance to named instance in SQL Server 2012? By mistake I have chosen default instance option and installed. Now I have to change to named instance. How to change?
3
votes
2answers
47 views

How do you join tables at different points based on data in the first table?

Complicated question: I have 3 tables that I am joining to pull data. The first is a 'shift' table; the second a 'schedule' table, and the last is a 'shift_info' table. shift_info ...
0
votes
2answers
54 views

SQL temporary variable query

Not sure what am I doing wrong in this query, there seem to be a syntax error in the subquery AND typeId = (SELECT @id = typeId FROM EmpType, near @id =. I'm looking at getting the typeid in a ...
2
votes
1answer
53 views

What Host Name is my application connecting to SQL Server on

We have this 1 node broken cluster that I have been given the joy of fixing. Wont go into the reasons why its only got one node but I am planning on migrating this off onto a newly built cluster. So ...
3
votes
1answer
38 views

How to convert following mysql query to oracle?

I have a mysql query like following, Column time contains date in long format. SELECT FROM_UNIXTIME(CEILING( Time/('20160'*1000)) *'20160', '%Y:%m:%d %h:%i') AS Time, SUM(CreatedInstanceCount) AS ...
1
vote
1answer
44 views

Does ALTER on PostgreSQL, Oracle table (respectively) create intermediate copy of the table?

I've heard MySQL admin claim that ALTER on MySQL table creates an intermediate copy of the table during the operation (don't know if the type of table involved were supposed to be MyISAM or InnoDB), ...
3
votes
2answers
60 views

SQL Server “fix” query for Hash Match instead of Nested Loops

At the moment I have a view that is running very slow. It takes about 3 minutes to execute. This suddenly happened. To debug what was going on I used an old dataset and compared both queries. On the ...
1
vote
1answer
76 views

Simple trigger to update two columns in one table

I want to create a trigger MySQL that updates two fields at the same time. Whenever a record is added or inserted, I want the contents to be copied to one field or the other, depending on which one ...
3
votes
3answers
146 views

Choosing a PK: VARCHAR(2) or SMALLINT(2)?

Today we were discussing the following: Brazil has 27 states and each one has its own abbreviation (just like USA). So we've got RJ for Rio de Janeiro, SP for São Paulo, MG for Minas Gerais and so ...
8
votes
6answers
520 views

Switching values in a column with one update statement

You find that an error in a system has been incorrectly naming men (M) as women (W) and vice versa in the database. The columns only allows for one character. Without using any temp tables, write one ...
3
votes
2answers
100 views

Solving MySQL Query

I am trying to solve the following query with these data: Some assumptions will need to be made. Please list any such assumptions Users: +---------+---------+--------+-----+--------+ | USER_ID | ...
-5
votes
1answer
47 views

#1064 - You have an error in your SQL syntax; [closed]

I am trying to create index on a specific column, My syntax CREATE INDEX indexname ON test1; But i got a error #1064 - You have an error in your SQL syntax; check the manual that corresponds to ...
3
votes
3answers
204 views

Selecting only one duplicate

I have a database of purchase orders and stockroom checkouts, and it seems that the stockroom checkouts tends to get duplicates inserted (we source this data from an older system nightly). What would ...
0
votes
1answer
35 views

MySQL error 1066 - Error Code: 1066. Not unique table/alias: 'TAXES'

I have two MySQL statemenents: SELECT PRODUCTS.REFERENCE,PRODUCTS.NAME,PRODUCTS.PRICEBUY,PRODUCTS.PRICESELL, SUM(TICKETLINES.UNITS) AS UNITS, SUM(TICKETLINES.PRICE * TICKETLINES.UNITS) ...
0
votes
1answer
23 views

How do I get a list of “large data users” from my table?

This situation is a little specific so bear with me. I have a (very) large table with many users (from a sync server). The users are stored by a hash identifier "username". Each user has many rows in ...
1
vote
2answers
78 views

Finding the names and the year of the movies that have at least 5 actors

Given database of the website "eMovies" that manage data about cinema movies. Write in SQL query that finds the names and the year of the movies that have at least 5 actors. My attempt: The ...
1
vote
1answer
41 views

Understanding the flaw in the query

Suppose we have a relation Employee={empId,name,department, salary} and we are interested in finding the employees who get more salary than anyone in department 5. Would the following query work? ...
3
votes
2answers
197 views

Find the days difference between two dates per month

I have a table 'data' in which there are two columns i.e. Startdate and Enddate. Startdate is the date when an employee joined working in a company and Enddate is the date when he left. I need to ...
7
votes
1answer
145 views

Does UPDLOCK guarantee concurrency

I have a table called tblOrderNumber which has 1 row and 1 column. This table stores what the next order number will be for my ecommerce website. It is ABSOLUTELY VITAL that the same order number is ...
0
votes
0answers
17 views

relationships in access

I am making the database design of my website in Microsoft Access 2013 because it has a nice user interface and I believe it will help me understand everything a whole lot better with the UI. I have ...
0
votes
1answer
52 views

mysql query taking differently long

I need some enlightenment on a MySQL query issue: I recently found out that a select query is taking differently long depending on how large ibdata1 is (at least that's how it seems to me). To be ...
0
votes
1answer
30 views

Understanding the given correlated subquery

I am unable to understand how this correlated subquery works on the relation Student={Sid,Sname,marks} Select Sname from Student s1 where (select count (*) from Student s2 where ...
0
votes
2answers
52 views

Insert Data in seconds between 30 minutes time interval [duplicate]

I want to divide my time interval from the start time to end time. The elapsed time should be divided across 30 minute time intervals so that for this input 08:05:13 12:09:49 I should get ...
0
votes
1answer
24 views

select rows older then column DATETIME - INT value of other column in single query

I have 2 columns in my table: (DATETIME)lastsendattempt (INT)sendgap I know I can select all rows that have a "lastsendattempt" DATETIME which is older then the current time. But, am I able to use ...
0
votes
0answers
27 views

MySQL Optimization (my.cnf)

My site has been extremely slow lately at peak times (3 hour window each week). My website is an auction site, Ajax-heavy and uses Pusher.com to update bid status and bid amounts in real-time. I tried ...
0
votes
0answers
26 views

Unique Constraint that ignores soft deleted fields [duplicate]

I want to add a unique contraint to the PartyUser table so that the same email can't be added more than once for a Party (Soft deleted records to be ignored). I am using SQLServer Party (Id, Name, ...
1
vote
0answers
52 views

How does logical and physical operators of query plans works in low level? [closed]

As a practice a few weeks ago I decided to build a small database system. I wrote relational tables as object arrays. Also I wrote basic relational algebra operations that operate on my data ...
5
votes
1answer
95 views

Index not being used in SELECT query

I have a table of about 3.25M rows with the follow format in Postgres 9.4.1 CREATE TABLE stats ( id serial NOT NULL, type character varying(255) NOT NULL, "references" jsonb NOT NULL, ...
1
vote
1answer
44 views

Combining GROUP BY and JOIN

This seems like a really simple request, but I can't find the answer. I'll admit that I only have a very limited understanding of JOINs, so it could just be that I just don't fully understand the ...
7
votes
2answers
271 views

8k row overflow error when updating row of size 5k

I'm trying to update a target table which has one row of size 5k to a row of size 5k also. Since it's one row it's easy to know the row actual size: select * from ...
4
votes
1answer
54 views

Exchange rate table and conversion on the fly in query

I'm working on a small project and I'm have arrived to the point where I let a user chose in which currency to display items. I'm working with Java and my first thought was that I would use some kind ...
1
vote
2answers
54 views

Table design: how to store denormalized Excel data

I have following data stored in Excel which I would like to transfer to SQL DB. There will be a new value every year for every indicator. Indicator | 2001 | 2002 | 2003 ...
1
vote
1answer
30 views

Three table query to find the most recent item based on a distinct value [duplicate]

I am attempting (without success thus far) to write a query that will join 3 tables and return the most recent record based on a distinct value. Table A itemnum | itemname -------- | -------- 123 ...
3
votes
1answer
37 views

How to use the below expression in my SQL Query

I have this expression for sorting. I need to use this expression in my SQL code for the sorting purpose. Can anyone help me with this?? I tried case when stmt but it fails. Expression: if ([All ...
4
votes
1answer
41 views

Group and count array elements using intarray

I am working on a Postgres 9.4 project with the intarray extension enabled. We have a table that looks like this: items ------------------------------------- id name tag_ids ...
1
vote
2answers
56 views

Getting Only What Changed from SQL Server 2005

We have a 17 store system which are on SQL Server 2005 Standard Edition and on Windows 2003 server. We currently have the following process: At each store we have an SQL job which runs a procedure ...