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)

5
votes
2answers
92 views

Will I need to create a pivot or can I alter this query

The following Query that I wrote is working to a point. The problem that I found is that in each pump, cable, etc can be listed in each of the part columns. Example: What I would like to get is ...
1
vote
1answer
18 views

mysql - select random rows with max 3 rows per parent id

I currently have a query that returns a maximum of 3 rows per parent_id. I want to enhance the query to return a maximum of 3 random rows per parent_id. This does not work for me at the moment as it ...
2
votes
1answer
125 views

Order by values with most incidence

I have a table as this example: CREATE TABLE customerEvent( customerID int NOT NULL, eventID int NOT NULL ) customerID may appear in many records. I use the following query to identify ...
0
votes
1answer
83 views

Find columns with different values compared with a reference row

My table, MARC, has more than 200 columns with WERKS (plant) as the key. Most of the entries in MARC have values that come from a reference plant. So, usually, all the 200 columns will contain the ...
3
votes
0answers
100 views

Which is more efficient b+ tree or search (binary or linear)?

SELECT A FROM T WHERE M > 1000 AND M < 5000; I am unable to judge which of the below is best suited for the above query: Linear search Binary search on M B+-tree with index on M B+-tree with ...
2
votes
2answers
28 views

Oracle Database 12c: TNS:permission Denied (CentOS)

(This post has a similar title to another post but the underlying cause and solution are totally different.) Problem: I can't login to sqlplus as sysdba but the listener seems to start ...
1
vote
1answer
27 views

Identify records with one identical field, but different timestamp

I am trying to process some data, stored in an Oracle DB, for a machine learning model I am working on. My current query uses this line in the SELECT statement to identify records that have a field ...
3
votes
2answers
41 views

Combine the effects of separate delete and insert rows

How to I combine the two rows below: Date Id For_Id Type New_Value Old_Value ------------------------------------------------------------------ 2015-08-13 6 327 ...
0
votes
1answer
30 views

Automate Backup of SQL Server Database Structure

I have a data warehouse which isn't huge only about 16Gb in a Microsoft SQL Server Standard Edition 2014 database. However, now that it forms part of our production system I don't like developing on ...
1
vote
0answers
28 views

Update row value with a sub-select join

I am trying to update 1 column in all rows in a table to have an appropriate count value. Sadly, the SELECT query I am using won't let me join on a field in update table. The query looks like this ...
2
votes
1answer
29 views

How to fix DB2 Unsupported functionality: GENERATED BY DEFAULT

On IBM DB2 I try the following direct SQL: CREATE TABLE users ( user_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1), user_name VARCHAR(16) NOT NULL, email_address ...
2
votes
2answers
251 views

Numeric vs Integer for a column - size and performance

I have an application which uses a PostgreSQL table. The table is very big (billions of rows) and has a column which is an integer. The integer can be up to 6 digits, i.e. 0-999,999 , no negatives. ...
3
votes
1answer
69 views

Display non-null and null count using case statement

In total there are 10 values in my table, 5 of which are null. I need to display the count using case statement in Oracle, like output below: Sample Data: +------+ | Eid | +------+ | 1 | | 2 ...
2
votes
2answers
31 views

Differentiate between a row modification and add?

Any recommendations into how one can figure out which rows were solely modified from an existing row (edit) and which one were solely inserted? I know minus can extract any rows that were modified ...
2
votes
0answers
55 views

Finding the most recent event older than 60 days [closed]

I would like to filter rows in a table for the most recent event for particular individuals that did not occur within the last 60 days: Select pid, Max(date) as date FROM table WHERE date ...
4
votes
2answers
98 views

Count rows with inner joined tables

I have 3 tables: Players: mysql> SELECT * FROM players; ...
1
vote
0answers
81 views

SQL Server 2012 memory issue [closed]

I am having performance issues on a Windows 2012 Server running SQL 2012. When I run the DBCC MEMORYSTATUS command I receive the following. Process/System Counts Value Available Physical Memory ...
3
votes
1answer
38 views

Scripting Microsoft Data Warehouse Wizard

I am trying to automate the MDW setup process in my environment. Currently I have everything complete except for enabling the data collectors (usually uses a wizard as seen ). Has anyone seen or ...
2
votes
0answers
50 views

How can I organize complex queries?

I am analysing a relatively large (25GB compressed MySQL dump) static data set. Some of the queries I'm writing are several tens of lines long, which makes them difficult to understand, modify, and ...
2
votes
1answer
43 views

How to add DATETIME value for session user

I am maintaining two tables: Employee table consists of username and password(for logging in) log table consists of login and logout time for the session user With the click of a button, I should ...
2
votes
2answers
100 views

INSERT… ON DUPLICATE KEY UPDATE not working as I expect

I have a table called "Example" CREATE TABLE IF NOT EXISTS `example` ( `id` int(11) NOT NULL AUTO_INCREMENT, `a` int(11) NOT NULL, `b` int(11) NOT NULL, `c` int(11) NOT NULL, PRIMARY KEY ...
0
votes
1answer
21 views

json_array_elemnts() in FROM in postgresql 9.3 acting as where clause

I am trying to extract array out of json in the FROM statement But issue occurs when certain json doesn't have that array in it and then query just ignores that row and doesn't get counted in the ...
1
vote
2answers
50 views

Use array expression from subquery in ANY() condition

I have a relatively complicated query with a subquery fetching an array like so: ... ARRAY(SELECT category_id FROM category_schedule_con con WHERE s.id = con.schedule_id ORDER BY category_id) ...
1
vote
1answer
28 views

Query to get yesterdays data only for a specific time interval

Here is my where condition which I use for daily data but I've got to change the date everyday. WHERE X.stage = 'Approval' AND X.T_ExitTime between '2015-08-06 06:00:00:000' and '2015-08-06 ...
1
vote
2answers
27 views

Hows to make a DB2 table read-only?

We need to test how an application will fail when the DB2 database cannot be written to. What is the easiest way to make a DB2 table read-only? Is this considered locking? Thanks in advance, Bert
5
votes
3answers
417 views

How can I force MySQL to IGNORE ALL indexes?

I have read articles about FORCE index, but how can I force MySQL to IGNORE ALL indexes? I tried SELECT * FROM tbl IGNORE INDEX(*), but I was not successful. As for why I (and others) need to do ...
-1
votes
2answers
34 views

error18470 when i try to connect to Database Engine

I am new in SQL Server. Wrongly I disabled my instance when I'd worked with Logins. Now I can not connect to Database Engine. Could you please help me?
0
votes
3answers
62 views

Sql Script takes much longer than estimated

In order to clean up a very large table holding binary Files in IMAGE Cols, I created the following sequence (based on this: Article) to get rid of a part of those image Bloby quickly: Select * into ...
1
vote
1answer
80 views

SQL get row based on condition [closed]

Hi all please see attached data below. I'm trying to get a row based on various conditions. scenario 1 - get highest row if no hours exist against it that has (setup + processtime > 0). scenario 2 ...
5
votes
2answers
70 views

Create smaller row from alias and preserve column names

Using Postgres: SELECT users."name" AS "name" , array_to_json(array_agg(sites)) as sites FROM remodel.users AS users JOIN remodel.user_sites AS user_sites ON users.id=user_sites.user JOIN ...
3
votes
1answer
82 views

Is it possible to validate more than one parameter simultaneously into a Stored Procedure in sql?

On Microsoft SQL Server 2008 I realized a stored procedure which contains more than 20 parameters, in this SP I update or insert data into table which does not accept null values. I need to evaluate ...
1
vote
1answer
28 views

SQL 2012 insert data into a table from another table and custom columns

I'm trying to insert data from one table into another table and I also have 4 columns that are not in the source table but need to be populated in the target table. Here is the Insert statement SET ...
1
vote
2answers
55 views

Retrieve additional columns in recursive CTE

This works as far as getting the amount of children a "thread" has but now I can't seem to get it to pull parent row columns. Parent rows have parent_id is null, trees can be any level deep. I manage ...
1
vote
1answer
23 views

Parsing out date_entered field to capture TIME 'hh:mm:ss' based on time range

Based on the date_entered column, which is a data_type of 'DATE' how would I parse out the data to pull the time. Values in the DATE_ENTERED field (DATA_TYPE is DATE) looks like this 5/15/2015 ...
0
votes
0answers
39 views

SQL Server Profiler performance

I was wondering if I set a filter for just a particular user does it limit the amount of resource consumption used by sql server and does using a filter(depending on the filter) limit the performance ...
2
votes
1answer
106 views

Which DBMS are more standard compliant? [closed]

I find it frustrating that the same queries don't work in different DBMS. Example, some DBMS (such as Microsoft SQL Server and MySQL) seems to support the information_schema table, while others (such ...
0
votes
1answer
51 views

copy tables from one database to another

I have two Databases on mysql. They are used for completely different things and contain different tables. The problem is I need them both on the same server that can only host 1 db (and I can't ...
-1
votes
2answers
55 views

Optimize query or split in two queries and process result separately?

I have this DB schema: As you may notice already there is no relationship between tables and won't be on the future. If I run the following queries I will get a lot of rows per table: SELECT ...
2
votes
2answers
41 views

Extracting two particular values from a multi valued column

I have a table SUBSCRIBER and in it the data is stored in a way that bars is a multi-valued column . Different values of bars are stored in different rows with the ID as following : ID BARS 1 , ...
0
votes
2answers
42 views

Trying to get count of childs rows

I'm trying to get the count of child of a parent row. The sql query I'm using as follow only pulls the immediate count, how can I get total count as childs can have more childs. select c.id, ...
0
votes
1answer
52 views

Subquery returned more than 1 value. please help. i beg for mercy

This is may table. In one column there are daily dates and in the other is a classification. I use this query to count how many registers are in a month and which ones are C or D, but the subquery ...
0
votes
1answer
73 views

Create an “int” column forced between 0 and 3

I would like to create a table with one column that would be of type "integer", and locked between 0 and 3. Is this possible? My goal would be to have this column so that I could just execute a ...
1
vote
1answer
33 views

Nightly Excel Spreadheet import into mssql database

I am currently working with a nightly import that I need to create, but am not sure what the best route would be to update/insert into the current table. This is all done in MS SQL Server 2012 and ...
0
votes
2answers
75 views

How to compare dates in SQL?

In a database table I have made a date attribute, but I have set its type to varchar and not Date. My question is: will I still be able to compare such dates in a SQL Query? Dates in my DB are stored ...
1
vote
2answers
35 views

Group by month in result columns?

I'm using this DBMS: Caché I've never used a pivot table in SQL and I'm not even good at it in Excel, but I assume that's what I need. Given the following sample data: ID Name Result Date ...
0
votes
2answers
34 views

How to match regular expressions in Oracle?

I'm using the following query: select * from table WHERE POSTAL_CODE LIKE '[a-Z][0-9][a-Z] [0-9][a-Z][0-9]'; However, this is not capturing anything. For example, a typical POSTAL_CODE looks like ...
1
vote
1answer
18 views

How can I insert procedure results from server 1 into table in server 2?

The goal is to link our website to the database. The database the website will connect to will be different then our production db. In fact, a separate instance of SQL. How can this be structure to ...
0
votes
1answer
29 views

combining 2 sql queries based on different conditions

I have a table : Person : (integer) (varchar) (Date) (date) id state d1 d2 1 'ALIVE' '2015-08-07' '2015-08-08' 2 'DEAD' '2015-08-08' ...
0
votes
3answers
69 views

Min(date) in sum case statement [closed]

I'm trying to write a statement where the settled date is less than the min(duedate) but I'm getting this error: Msg 130, Level 15, State 1, Line 32 Cannot perform an aggregate function on an ...
0
votes
2answers
57 views

simple mysql query design help

Simple query design help. Below is my query: SELECT tkt.ticket_id, tkt.`number`, val.field_id, FF.name, val.value FROM os_TICKET tkt, os_form_entry_values val, os_form_field FF where ...