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

learn more… | top users | synonyms (2)

2
votes
1answer
73 views

How to go about modeling the main object of relationship?

I'm using MySQL and MyISAM tables. I have a bunch of objects that I want to group together 1 object belongs to 1 and only 1 group 1 group can have many objects 1 group has 1 required main object ...
2
votes
1answer
4k views

Query taking non-consistent time to execute [closed]

I have a query which takes large time to execute sometimes.When i checked the speed it was taking 15 seconds.But actually it should run faster than that.When i again checked the query it again ...
2
votes
2answers
307 views

How to join multiple tables (SQL) to find the difference between an order and shipped quantities?

I have a collection of orders that were submitted to an internal ERP, which were processed and partially shipped. The table hierarchy is as follows: Order table contains OrderReference(PO), OrderID ...
2
votes
2answers
13k views

Dynamic Oracle Pivot_In_Clause

I'm kinda stuck. I want to do a user-role-relationship pivot table and my query so far looks like this: WITH PIVOT_DATA AS ( SELECT * FROM ( SELECT USERNAME, GRANTED_ROLE ...
2
votes
1answer
183 views

Is it ok to let users add new columns and tables to your database?

I'm basically making a database of databases. An administrative User can go in and hit "Create Database" (which is basically a table) and set up the column types and how many columns. Other users can ...
2
votes
1answer
1k views

Reorganize full text catalog is offline or online?

I need to schedule a Full Text Search Maintenance and I was reading this link: http://msdn.microsoft.com/en-us/library/ms176095.aspx. Reading this documentation I am supposing that I need to ...
2
votes
4answers
2k views

search for something in the first 1000 records in the table

If I do the following MySQL query select * from table where name Like '%john%' limit 5; that mean the query will search all the mysql database table and return only the top 5 results that match the ...
2
votes
1answer
967 views

SQL INSERT Query takes 2 seconds and sometimes much longer to execute

I know the question's title is bad, But I have an hard time figure it out my self... sometimes this query runs in 2-3 seconds and sometimes it's takes even 30 seconds I have a table named ...
2
votes
2answers
1k views

How to GROUP_CONCAT between given rows?

I have a table as id diary 1 breakfast 2 walk 3 start 4 office works 5 office projects 6 end 7 taxi 8 start 9 preparing for meeting 10 doing the meeting 11 end 12 night I want ...
2
votes
1answer
5k views

How do I completely remove MySQL from a 64-bit windows 7 machine?

I'm new to MySQL and struggling to find a version and workbench which is stable on my 64 bit Windows 7 machine. I've decided to attempt to completely remove MySQL from my machine and to restart the ...
2
votes
3answers
535 views

MySQL Query - How to get most recent demographics?

I have a table that has duplicated cardnbr's. I need to pull out demographics that are the most recently used by the customer. I have a date field (lastvisit) that shows their most recently used card. ...
2
votes
0answers
215 views

Get all comment.message, status.message, users.* and profiles.* where users.user_id exists in friend.friend_id or friend.user_id

I need some help building this query because it's a bit complicated at least for me. I need to get all comment.message, status.message, users.* and profiles.* where users.user_id exists in ...
2
votes
1answer
826 views

Change in datatype?

I have a table on which rows are inserted on hourly basis. The row contains id, installation, product_id and hit_allowed. all of them were mediumint in the beginning. but few days back id reached it ...
2
votes
1answer
317 views

Flat query to Column structure

I am trying to structure a query for a calendar view. I want each row to return the details for a person and what they are doing for 7 days (split into AM and PM) So the result set might look like ...
2
votes
3answers
338 views

How to get all non deleted messages from one user

I have two tables one for messages called default_messages and the other for recipients called default_recipient. This is how SQL for those table looks like: CREATE TABLE IF NOT EXISTS ...
2
votes
1answer
195 views

SELECT for values in a table

I have this table structure: id1 id2 1 2 1 3 2 1 2 4 id1 and id2 are not unique as you can see in the values. I need to retrieve those values that exists in the two columns ...
2
votes
3answers
2k views

performance of multi field primary key or 'contrived' 'semi artificial' key

This is not a question regarding the benefits or otherwise of using an artificial auto increment key in any given table against using a multi-field 'primary key'. That discussion (or argument) can ...
2
votes
5answers
21k views

Retrieving row count without using Count function

I wrote a query below which shoots me a syntax error why would it do so, SELECT MAX('Row') FROM (SELECT ROW_NUMBER() OVER(ORDER BY ID DESC) 'Row' FROM USERS) Error Desc: Incorrect syntax near ...
2
votes
1answer
834 views

Cartesian Product SQL / Inserts with multiple Values Clauses

I want to create cartesian product sql query.But,I'm getting the error.Where I wrong? alter table #Personel ( Ad varchar(100) ) alter table #Masa ( MasaAdi varchar(100) ) insert into #Personel ...
2
votes
2answers
1k views

How can can I get object definitions stripped of all comments?

When I want to know if a stored procedure or views references a given name (table, view or column), I need a version of the object definition stripped of all comments. Is this possible by a SQL ...
1
vote
1answer
60 views

Select multiple columns from subquery - Connect by

I have a table which manage folder structure hence has parent child relationship and we trigger a query to sum all document in the complete heirarchy SELECT folder_name name, ...
1
vote
1answer
19 views

index creation - Slow Query - PostgreSQL 9.2

I've got the following query: SELECT * FROM ( SELECT split_part(full_path, '/', 4)::INT AS account_id, split_part(full_path, '/', 6)::INT AS note_id, split_part(full_path, '/', 9)::TEXT ...
1
vote
2answers
104 views

OrderBy int slow for large table join

I have roughly 1.5 million records. This query works fine for smaller data, but it gets a second slower for every few hundred thousand records. For 1.5 million, the query is noticeably struggling. I ...
1
vote
2answers
103 views

Converting rows to columns

I have data like following: created_at | status ---------------------+------------- 2016-04-05 1:27:15 | info 2016-04-05 3:27:15 | info 2016-04-05 5:27:15 | warn 2016-04-05 ...
1
vote
1answer
64 views

Combining 2 SQL queries

I have these 2 queries below that I use to calculate net profits from Jan to Dec. Query 1 /* Profit for the Period 13 */ SELECT * FROM ( SELECT MONTH(T0.RefDate) AS Month, '311999' AS 'SAP Code', ...
1
vote
1answer
45 views

SQL DB2 query with where clause

Can you please help me out with the below query? I need to extract from column VAL_DT only items with date older than today's date but this rule should not apply to items that contain the following ...
1
vote
1answer
49 views

SQL Query; Admin Log System; Foreign Keys; Retriving multiple rows form different tables at once

Table A (users) id | name | ip 52433 Yashas 1.1.1.1 512 Admin 5.6.7.8 Table B (admin_record) id | admin | time | date | reason | action 52433 512 2312352 ...
1
vote
1answer
64 views

Filter created_at and count distinct minutes

I have this query to get records from a database: SELECT camera_id, count(*) AS snapshot_count FROM snapshots WHERE created_at >= timestamp 'yesterday' AND created_at < timestamp 'today' ...
1
vote
1answer
72 views

Data structure design for supporting arbitrary number of columns in table or database [closed]

I am currently working on a sort-of a meta-modeler to build a free web service so that people can input data and run several models on that data. The task I am currently struggling is: user needs to ...
1
vote
1answer
39 views

Return Bit Value based on previous records by Category

How would I return TRUE or FALSE if four of the last six records failed for a given MAKEID? So there has to be 4 failures (or FALSE records) in the last six MAKEID FAILURES CREATEDATE 101 ...
1
vote
2answers
77 views

Select column to be inserted based on input column value

Assume the following two tables (and ignore the quality of the database design): Table 1 has columns id, sequence_number, value: it contains many rows. Table 2 has columns id, seq1, seq2, seq3, ...
1
vote
1answer
470 views

Msg 39011 SQL Server was unable to communicate with the LaunchPad service

I was playing with the new "R" scripting in SQL Server 2016 CTP 30. Finally got it working, thanks to this site. I've had a problem that I'm sure others will run into as well. All of a sudden the ...
1
vote
3answers
202 views

Best DB structure for large CSVs?

I have previously posted on stackoverflow, however, I have been directed here. I'm getting to the stage where I need to analyse lots of flat CSVs: Read-in around 500GB of CSVs (e.g. daily data ...
1
vote
1answer
46 views

Constrain/limit certain records in sql table definitions

Let's say I have posts on which users can leave a comment. And I want to limit the amount of comments to a certain post to 10, is it possible to add such constraint/restriction to the table? My guess ...
1
vote
0answers
106 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 ...
1
vote
2answers
228 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
66 views

Full text search on two tsvector columns

I'm trying to do a full text search across two columns of two separate tables in PostgreSQL 9.4.2. The tables and columns are: article.article_title keyword.keyword Where there is a many-to-many ...
1
vote
1answer
61 views

Update records from one table to another in different databases where condition is accomplish

I have two tables stored in two differents DBs and I need to perform some sync between both tables based on lastmodifieddate and lastSyncAt. Let's call them as db1 and db2. This is the DDL schema for ...
1
vote
1answer
241 views

Returning case-sensitive input of case-insensitive array match

Taking a JSON table with some simple data: CREATE TABLE jsonthings(d JSONB NOT NULL); INSERT INTO jsonthings(d) VALUES('{"id":1,"email":"Test1@test.com"}'); INSERT INTO jsonthings(d) ...
1
vote
2answers
312 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 ...
1
vote
1answer
175 views

Postgres Nested WHEN Aggregate Function

In PostgreSQL (version 9.4) I am trying to construct a query to determine in various tranches how many times various numbers appear in the data set between ranges. When I query group-ing by ...
1
vote
1answer
230 views

SSIS Transaction Problem

I am trying to use SQL transaction with my SSIS package so if any error comes changes are rollback. I am using an "Sequence Container" in Control Flow for this and setting its "Transaction Option" to ...
1
vote
1answer
356 views

Querying sums of grouped consecutive rows in PostgreSQL 9

I have data about people traveling in different countries like this: country | begintimestamp | distance Germany | 2015-01-01 00:00:00 | 100 Germany | 2015-01-01 01:12:13 | 30 France | ...
1
vote
1answer
50 views

How to shard a database like that of SO?

I know a bit about sharding but I cannot wrap my head around that fact that say eventually SO cannot scale vertically then how would they shard their SQL DB? Even if we assume SO will not reach that ...
1
vote
2answers
149 views

New to databases - does my ERD reflect good database design?

I'm making a database to hold data for a media player that I'm working on. Originally, I thought to use one table with the fields: {filePath,artist,title,album,track_number,length,date,artworkUrl} ...
1
vote
1answer
170 views

SQL Server Chain Permission

We have the 2 databases. Reporting HR There are some users who want to extract data from HR database. But we don't want to give direct access to that HR database. So, we created the special ...
1
vote
1answer
4k views

Mirroring SQL error log - Error: 8474, State: 11

I'm getting these errors in SQL error logs: An error occurred in a Service Broker/Database Mirroring transport connection endpoint, Error: 8474, State: 11. (Near endpoint role: Target, far ...
1
vote
2answers
399 views

Mysql fulltext index search returning weird result

I have ingredient table. I want all those recipes which have certain ingredients. Below is my table structure. Table(ingredient) - Applied fulltext index on ingredient column. ...
1
vote
3answers
144 views

Clustered Index improvemens for particular queries

I found a table that looks like this: CREATE TABLE [dbo].Table1 ( id INT primary key IDENTITY (1, 1), [idUser] INT NOT NULL , [Amount] INT NOT NULL , [Attempts] INT NOT NULL , ...
1
vote
1answer
128 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 = ...