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

learn more… | top users | synonyms (1)

2
votes
1answer
26 views

Return top 100 words from strings

Not sure this is even possible but I was wondering if there is any way of using PostgreSQL to return the top 100 most occurring words from a database column containing strings of varying lengths? The ...
0
votes
3answers
48 views

PostgreSQL - Simple column binding

I have many tables of the same length (number of rows) that were previously ordered by some columns as they were being written on disk. My SELECT is of the form: SELECT t1.id, t1.value, t2.value, ...
0
votes
1answer
21 views

PostgreSQL 9.4: Index Only Used For Lower INET Ranges

For some reason queries on the high range of are extremely slow and queries on the low range are extremely fast. My index is an adapted version of the answer in Optimizing queries on a range of ...
0
votes
0answers
7 views

Use array operators inside postgresql function

I'm trying to create a postgresql. But I keep having an error ERROR: operator does not exist: integer[] <@ integer Hint: No operator matches the given name and argument type(s). You might ...
0
votes
1answer
125 views

Entity ID field that can refer to multiple tables?

In designing both audit tables and notification tables, I've run into an issue where I need a row to refer to an entity in another table, but which table that entity is isn't always the same. For ...
4
votes
2answers
70 views

Get 2 Most Recent Records By Product

I am stumped. I need to return a TRUE or FALSE by ProductId if the 2 most recent records created are 'pass' and the LEVEL values are different. PRODUCTID CREATEDATE STATUS LEVEL ABC ...
4
votes
2answers
2k views

Loop through all databases and remove all users

We are doing a conversion from one system to another which is requiring backup/restores. I am trying to create a process that will remove all users after the restore on the new servers as no user ...
5
votes
1answer
63 views

How to improve this union to ensure consistent results?

I've been trying to find an reliable and economic way of counting rows over a 7 day period and so far, this is the best I've been able to come up with: Schema CREATE TABLE IF NOT EXISTS "data" ( ...
-1
votes
1answer
18 views

in postgres how to select items that have a dupe across two columns

My data looks like this: queryable_type queryable_id User 1 User 2 User 2 I'd like to return dupes so that my result would look like this: queryable_type ...
5
votes
2answers
338 views

Reseed identity column in all empty tables

I need to write a query that resets the identity on all tables that have an identity column and are empty.
1
vote
1answer
29 views

How to combine 2 SQL statements into one?

I have these 2 SQL statements. Each year, I need to add the new one for the new DB. How can I write it so it will just work for the new years instead of me manually editing this each year? Is there a ...
0
votes
2answers
114 views

SQL Query Help to get Quiz statistics

I'm coding a quiz web application and i need help. I've got three tables Nicknames : |USER_ID|NICKNAME| that stores USER_ID(auto increment) and NICKNAME registered to my system. Questions : ...
1
vote
1answer
37 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 ...
0
votes
1answer
27 views

Scrapy data analyzed with R, mongoDB or other pipeline? [closed]

I'm scraping data using the Scrapy library in python and i'm planning to make few analysis in the future using R. In the future it could be a very big database with millions of items, what are the ...
0
votes
0answers
12 views

Query Postgres table by Block Range Index (BRIN) identifier directly

I have N client machines. I want to load each of machine with distinct partition of BRIN index. That requires to: create BRIN with predefined number of partitions - equal to number of client ...
4
votes
1answer
3k views

Split comma separated entries to rows

I have a Table like this: | ID | OtherID | Data +--------+-----------+--------------------------- | 5059 | 73831 | 5103,5107 | 5059 | 73941 | 5103,5104,5107 | 5059 | 73974 | ...
2
votes
1answer
62 views

PostgreSQL 9.4 query seems very slow

I'm experiencing a very long response time for a query returning a relatively small number of rows. It takes over three minutes to return ~1.3 million rows. I would assume it was an indexing problem ...
1
vote
5answers
145 views

Should I have a column for every data type or 1 column that stores everything

I have a database table that currently looks like this: PageField -Id (Int, PK) -FieldType (String) Can be Text, Decimal, Integer or Bit -Value (This stores the value regardless of the FieldType, so ...
7
votes
2answers
226 views

Shrinking a SQL Server Transaction Log

I’m currently having to deal with a SQL Server transaction log that has got out of control. Disclaimer: I’m not a dba and this isn’t my area of expertise so please bear with me. Currently I have a ...
6
votes
2answers
87 views

m-to-n total participation in SQL

How should I model the following design in SQL? As it's shown, every A must have at least one B, and vice versa. I know I should have three relations: A(a), B(b) and R(a,b), but what about total ...
-1
votes
2answers
43 views

how to Create A SP with table variable and execute 3 SP statements in the same statement

how to Create A SP with table variable and execute 3 SP statements in the same statement . exec [dbo].[sp_KPI_Churn_MTD_Current_Year] exec [dbo].[sp_KPI_Churn_MTD_Previous_Year] exec ...
0
votes
3answers
22 views

Set Value Based on Number of Records with Certain Values using SQL Server

I am stuck on how to return a bit value based on records with certain values. Query needs to return TRUE or FALSE if there are three different Levels of the same Control: Example data: PRODUCT ...
4
votes
3answers
422 views

Avoid double query on the same table

I have two queries which are doing basically the same thing, but have different grouping, the first query (query 1) is used to populate a chart, and the second to populate a table. Query 1: SELECT ...
4
votes
1answer
65 views

DISTINCT ON vs ROW_NUMBER() for getting a row with a maximum value of a group

I have the following table in PostgreSQL 9.4: CREATE TABLE dpa( id serial NOT NULL, currency_id integer, amount numeric(14,3), date timestamp without time zone, plat_id integer, pl_id ...
1
vote
1answer
135 views

Splitting 1 record on one table to become two records on another table

I am REALLY very new to this so please excuse my ignorance, but I could really use some help! I've got two tables: BillsFiled and Authors On BillsFiled, there are the columns: ID, Symbol, Filedate, ...
4
votes
3answers
192 views

SQL OrderBy When using an In() query

Example query SELECT * FROM Table WHERE ID in (5,3,4) How do I adjust this query so that the order of the returned entries follows the input of the ID's (IE, 5 first, 3 second, 4 third). Edit - To ...
2
votes
0answers
15 views

TLS 1.2 with SQL Server 2008 [duplicate]

I have a VM where I use SQL Server 2008 as a black-box data store for a product I work with. I'm being told by my IT police that my VM is not (or will not be) secure in a short time unless I can ...
5
votes
2answers
3k views

Does SQL Server have a Date_Format function?

All I found is a list of predefined DateFormat that I can choose from, like this As I remember in MySQL (and PostgeSQL too?) you can define your Date Format: DATE_FORMAT(now(),'&m_%Y') --for ...
0
votes
2answers
60 views

Two SQL servers for one website [closed]

I have a website that is deployed on an environment as follows: a load balance. two web server. two database servers. both of the two database server have the same structure and queries. I found ...
-1
votes
1answer
49 views

What is the optimized way to fill table values based on it's previous columns values and from other table column values?

CREATE TABLE TEST2 ( RowID INT IDENTITY(1,1), Ratio INT ) CREATE TABLE TEST ( RowID INT IDENTITY(1,1), Col1 INT, Col2 INT, Col3 INT ) Now, I want to insert values in table ...
1
vote
1answer
822 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 ...
2
votes
2answers
177 views

Limiting read access in postgres to few rows

We have a requirement where we want to limit the maximum no. of rows that a select query can return when its done using a particular login. So basically we would like to append a limit clause to any ...
0
votes
2answers
270 views

Dealing with data stored as arrays in a MySQL DB

So I know storing arrays in a DB field is wrong and would never do it myself, however a 3rd party plugin my company is using stores data in an array and I was wondering if you could help me try to ...
0
votes
1answer
186 views

SSRS Domain Migration

My company is currently going through a data center migration. We are now down to the SQL Servers and this includes SSRS. The bad thing that is going to hit us is the current SSRS server is being ...
0
votes
1answer
41 views

Add ranking to query output

I'm new at programming with PHP/mySQL and I have got a little problem here. This is how my code looks like: SELECT COUNT(*) AS Number , Name FROM table GROUP BY Name ORDER BY Number DESC ...
3
votes
4answers
5k views

How to make SSMS upper case keywords

I recently started using Management Studio 2012. When using MySQL Workbench, a handy feature was that I could stay all in lower case and any reserved word (like SELECT, INSERT) would convert to upper ...
1
vote
1answer
212 views

Select only rows that equal x but not x and y?

I'm having quite the challenge with selecting hierarchical data in MySQL although I feel the schema should be optimized for it since it's based on the WordPress database model. I've got 4 tables: ...
2
votes
2answers
163 views

Normalising Customers and Employers with a people table

At what normalisation stage is it recommend to split say Customers and Employees into a people table and then link to the unique attributes of Customers and Employees. I cant seem to find the ...
0
votes
1answer
307 views

Data Analysis with MongoDB or MySQL

I have a need for processing some data in order to come up with some statistic. This is the scenario: Each user can answer several questions from the question list. They pick their answers from a ...
1
vote
1answer
48 views

Vertica performance on select from view with union

We have a huge number of rows(tens of millions), and for each table we have an associated view that is a select with UNION ALL between that table and several others. Now, we are doing a select from ...
6
votes
4answers
131 views

For SQL Server, how to fix simultaneous parallel table updates?

I have to update all records (add Guids) on two (indexed) empty columns of 150 tables, each table with around 50k records (using a script to create 40k updates at once in c# and post it to the server) ...
4
votes
1answer
121 views

Why do async_network_io wait types occur?

Last week something strange happened on our database. All of a sudden, the application blocked for our users who were not able to save new entities etc. After looking at the Activity Monitor of the ...
1
vote
3answers
229 views

mysql need help optimizing query

I have a table containing servers logs, and another table containing rules to match certain and drop them. The logs tables contains around 1 million rows, and there are about 20-30 rules. The ...
1
vote
1answer
47 views

Prepare MySQL for big UPDATE query on production server

Surprisingly I have not found anything relevant on this subject. What are best practices to prepare production MySQL server for a big update query? The question is broad but I'd like to know details ...
-1
votes
0answers
32 views

Get the sum of Total Churn_MTD(Current+2015+<2015) [duplicate]

how to declare 8 variables and get the total Sum,as you can see from the picture i created the 3 store procedures but dont know how to do the ...
0
votes
2answers
47 views

unable to connect to oracle in linux terminal in oracle VM ,TNS listner not connected

i installed oracle VM , new to oracle vm. Can some help me in connecting to oracle in Linux termial and TNS listner is not responding with IO failure on sql developer when pressing test button ...
4
votes
1answer
86 views

ORDER BY and comparison of mixed strings of letters and numbers

We need to do some reporting on values that are usually mixed strings of numbers and letters that need to be sorted 'naturally'. Things like, e.g. "P7B18" or "P12B3". @The strings will mostly be ...
1
vote
1answer
48 views

Displaying Distinct Max Value autonumber rows from tables in MySql [duplicate]

i am trying to execute the distinct records from mysql table having max value of it sno column. I have tried the following query but having no luck. SELECT DISTINCT p.sno AS sno, ...
0
votes
1answer
283 views

How to merge two databases, both with primary keys

I have two databases, both with the same tables, but the data is out of sync. I need to add data from database A to database B, but not overwrite any data in B that exists in A, instead add it with a ...
1
vote
1answer
12 views

getting records for yesterday day starting till ending PostgreSQL

Currently my application is running over 2 dbs one is snapshotDB one is evercamDB. In snapshotDB we are saving records now it want to retrieve them , currently am using this query select ...