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

learn more… | top users | synonyms (2)

1
vote
1answer
34 views

Most efficient way to retrieve data by timestamps

I'm using PostgreSQL 9.2.8. I have table like: CREATE TABLE foo ( foo_date timestamp without time zone NOT NULL, -- other columns, constraints ) This table contains about 4.000.000 rows. ...
1
vote
0answers
41 views

Having 2 identities in a table

I need to have some how 2 identity columns in one table. One that is normal identity and another one that will reset yearly. I have done the reset part with the help of DBCC CHECKIDENT ('TestTable', ...
0
votes
2answers
50 views

how to retrieve records in 3 tables

I have 3 tables. I need to check records whether it is present in those tables using a specific WHERE condition. Need all the datas in 3 tables. I have tried LEFT JOIN, RIGHT JOIN and FULL OUTER ...
0
votes
0answers
24 views

Fixing errors in sql statement [closed]

I have 3 tables of which i queried the first table (users) to get this result: $String_users= '19,20,21,22,25,26,27,28,29,30,31,32,33,34'; and I want to filter through two other tables friends and ...
1
vote
1answer
58 views

Insert all dates from a month into a table

I'm confused. How to get all dates from a month and insert them into a table in a database? I want to do this with a trigger so when I make a period, ex April 01-04-2016 / 30-04-2016, then in table ...
1
vote
0answers
13 views

Transactional replication using articles filters and delete operations

The problem with transactional replication using articles filters and delete operations. I have a problem with my transactional replication when I use articles filters and delete operation. When I ...
0
votes
2answers
53 views

How can I return top 10 rows from each state [duplicate]

How can I return the top 10 rows for each category? Say I have a table named xyz. It has a column called state, in the political unit sense of the word. I want the top 10 rows from each state. Do I ...
3
votes
2answers
98 views

How to rewrite a query with MAX in WHERE clause

I have a query with a max in the where clause, which is slow. select count(*) from TableName tbl1 where tbl1.id = ( select max(tbl2.id) from TableName tbl2 ...
1
vote
1answer
43 views

Limit the number of records depends on the part of primary key

I created database for football matches. I have 2 tables with a many-to-many relationship, so I also created a junction table: Referee CREATE TABLE referee ( id_referee INT IDENTITY PRIMARY KEY, ...
0
votes
0answers
12 views

Beginner explanation on MySQL cluster? [duplicate]

I am a beginner on MySQL and so far I have a web application with only one MySQL server running. I want to add more MySQL servers and started to read about MySQL clustering, but have a problem with ...
1
vote
1answer
74 views

Why using where in indexed field is slow?

we have a big table. the table structure is : CREATE TABLE `visit_logs` ( `id` int(10) NOT NULL AUTO_INCREMENT, `user_id` int(10) NOT NULL, `ref_id` int(10) NOT NULL DEFAULT '0', `link_id` ...
0
votes
1answer
37 views

Copying data from two tables into one using INSERT in SQL 2012

I've been trying to copy data from two tables into one using the INSERT statement. The tables are from the same database and have the same numeric types and I only need to copy one column from each ...
0
votes
0answers
16 views

Grab value from the group SSRS

How can I grab a value for a particular month in SSRS if it's in a group? I have all 12 months as a group, in a column outside of the group I need to get the value for March that is in the group?
0
votes
0answers
14 views

Diagnosing why postgresql query is going in a seemingly endless processing loop

I maintain a social forum built with Django and postgresql 9.3.10. There's a maintenance query I run via psql, to clean up my postgresql table. It goes like this: begin; DELETE FROM links_seen WHERE ...
-4
votes
1answer
24 views

Query to sort through history table

I have the above tables and want a query that will give the results as above. Basically I have a table of history where I want to display single row by adding two column that show prior Group from ...
1
vote
1answer
59 views

how to uniformly select rows in sql

I am new to here and please let me know if I didn't make the question clearly. If I have a m rows in a table and I want to select n rows uniformly. In other words, the interval between first row and ...
1
vote
1answer
48 views

OFFSET FETCH in SQL Server 2008

I have this query to paginate the results and it was working fine on SQL Server 2012. However i had to move my database to SQL Server 2008 and now my store procedure is not working. I did some ...
0
votes
1answer
36 views

MySQL - Finding Gaps of Date Ranges - Overlapping Dates

I have been struggling for a long time with generating gaps in date ranges, according to a set. CREATE TABLE `terms` ( `id` int NOT NULL PRIMARY KEY, `term_begin` date NOT NULL, `term_end` date ...
0
votes
2answers
40 views

Grant select on tables with a specific naming pattern

How to grant select on all tables starting with VVC_? grant select on VVC_* to user1
17
votes
7answers
404 views

Group daily schedule into [Start date; End date] intervals with the list of week days

I need to convert data between two systems. First system stores schedules as a plain list of dates. Each date that is included in the schedule is one row. There can be various gaps in the sequence ...
0
votes
1answer
58 views

Why did a query change its execution plan and how to anticipate the change?

We have a job using a dynamic query in production for years now and its execution took 5-6 seconds up until our last code deploy. Now the same query takes days to finish. It's a relatively simple ...
0
votes
2answers
21 views

Thinking about Normalization should I create a column for specific behavior?

I would like to expose a problem I often have when I work with database and I never knows what to do. I need to load data from a file into a table (table1) line by line with the help of a software. ...
8
votes
3answers
254 views

Unexpected CASE evaluation logic

I always understood that the CASE statement worked on a 'short-circuit' principle in that evaluation of subsequent steps does not take place if a prior step is evaluated to true. (This answer Does SQL ...
0
votes
1answer
55 views

Subquery returns more than 1 row

I'm executing a query to return the products of a specific sale in Magento database. It's working fine for a sale that contains only one product, however, I'm having Subquery returns more than 1 row ...
0
votes
1answer
30 views

Query the last records by date, where condition is true

A made this query, which is works perfectly now: SELECT t.DocID, t.RevID, t.EffDate FROM tblRev AS t INNER JOIN ( SELECT DocID, max(EffDate) AS MAXEffDate FROM tblRev GROUP BY DocID ) AS tm ...
-1
votes
0answers
29 views

Using where clause to create a left join

TL:DR I have two tables Table1 and Table2. Table1 contains 1 row per request. Table2 can contain 0 to 20 rows per request. Current query simply gets data from Table1. I want to add Table2 to the ...
2
votes
1answer
52 views

Predicate re-write in where clause

How can I rewrite where col % 5 = 4 in SQL Server. The reason to rewrite this is because I am selecting some columns from the table based on this condition. Because of % used in where it is causing ...
1
vote
2answers
31 views

Ignore empty rows while loading data into a table

I created a table (tbldemo) with 22 columns using a CREATE TABLE statement in MySQL. When trying to insert the data from a .csv file using a LOAD DATA INFILE statement, I get this error: Error ...
0
votes
3answers
21 views

Create Table Syntax [closed]

I am trying to create a table in MySQL but I keep getting an error of incorrect syntax. Below is my create table syntax, what is incorrect? CREATE TABLE [Test].[dbo].[Ite]( [ID] [INT] NOT NULL ...
2
votes
3answers
23 views

SQL Login curretly in use or not

Recently we did a change on a client's production server. The change was to rename an sa login. Once we renamed the sa login we found out the client was having a problem because sa login was used in ...
1
vote
1answer
19 views

Top N Results Ordered by Joined Table Column

I'm trying to optimize a fairly simple query that just gets a list of the top N users ordered by the number of followers they have. I am running into performance issues when I run the query a certain ...
0
votes
1answer
34 views

frequently dropping table in sql server

Kindly help me understand my below concern. In our current business requirement. We have to pull the complete data everytime. We have to truncate out table and load it with 20million of data. I want ...
0
votes
1answer
14 views

How to support custom fields for an entity and list them as columns?

I am currently working on a system which supports "custom fields" for users. I will illustrate this with a simplified example of this database schema: users -------- user_id name age custom_fields ...
1
vote
1answer
26 views

Alias Column Name [closed]

I am trying to run a basic select Query in PostgreSQL, but I am having trouble using the column alias. I tried to create the alias as randomfield As [Space In Name] but it gave an error of Error: ...
2
votes
1answer
23 views

Count of range on datetime in MySql

I want to group table entries for each userId only if they are consecutive entries in the table. I have a table like this. id userId transactionTime 1 1 2016-04-13 22:15:44 2 1 ...
0
votes
0answers
20 views

Copying values from one table to another table which the column intended to be copied delared as default

Can i know, the below column late_allowed when creating attendance table i declared as default and with a numbers (such as 3). The problem is that i am not sure how to put and where can i put default ...
0
votes
0answers
10 views

don't understand atquery command script [migrated]

I'm using a big-data database. in one of it's tutorial it has recommended me to use below bash scripts if order to running queries: #!/bin/sh # this will launch the real atquery program with the ...
0
votes
1answer
41 views

Scalar UDF causing performance issues in SQL Server

I have a scalar UDF doing some string manipulations. When I use it in select clause it causes performance issues. I read that if we do not query tables or views in scalar functions it won't affect ...
0
votes
1answer
35 views

Updating column from another table - PostgreSQL 9.2 [closed]

There are two tables: ja_jobs junk.test_test1 I need to update the time_job column from junk.test_test1 to ja_jobs. Here is what I'm doing now: UPDATE ja_jobs t2 SET time_job = t1.time_job FROM ...
3
votes
1answer
24 views

Store info in a column as JSON

I'm learning SQL Server 2016, I learned how to extract JSON from tables. Now I need to store JSON in a column. My JSON was: {"endereco":"R. Nome da Rua, 000", "cep":"00000-000"} I`m using nvarchar ...
0
votes
1answer
12 views

select duplicated rows by modified_date - PGSQL 9.2

Query: SELECT j.id INTO junk.ja_jobs FROM ( SELECT time_job, recurrenceid, clientid, creatortype, deleted, MIN(id) AS id FROM ja_jobs WHERE clientid = 33731 AND creatortype = 'legacyrec' AND ...
3
votes
1answer
30 views

Extract Multidimensional JSON

I have some tables: teste // id, descricao teste2 // id, descricao relacao // id, idTeste, idTeste2 With this structure, I got all products owned by a client. Now I'm trying to use FOR JSON PATH to ...
0
votes
0answers
32 views

SQL Server Trigger: Move deleted rows from 4 different tables to an archive table

I have a garage database with 20 tables. 4 of these tables - tCar, tCustomer, tAddress and tTelephone hold data about when we service a car. After a few months I want to be able to delete the data ...
0
votes
1answer
36 views

How to delete rows from the mid of the table without any where condition?

I have a table testing with two columns id: auto_increment name: varchar(20) I fired a query select * from testing limit 1, 2 which gave me 2nd and 3rd record. Now, I want to delete these records ...
1
vote
1answer
57 views

Storing Friendships: Should I use a multivalued column to retain Friend Identifiers?

Say I have a table UserFriendlist that has the columns: Id (set as Primary Key) UserId FriendListIds And let us suppose that it holds the following values: ...
0
votes
1answer
33 views

Application Using ODBC Data Source To Connect To A Database Not Working?

Ok so I have a program that needs to access a database in SQL Express 2008 using an ODBC Data Source. The program is used for the inventory of objects in the SQL database using a gui. It basically ...
0
votes
2answers
75 views

Is it OK to use PRIMARY KEYs from one table as NAMEs of other tables?

My case: I have many organisms (for example human, mouse and etc.) and a few models for each one. All models are independent from each other and I will never query them together. Normally I will do ...
1
vote
1answer
15 views

Informix help - SQL DBA

I am a SQL Server DBA with minimal Informix 11 experience. I'm tasked with extracting an entire Informix database (circa 8GB according to systables in Informix) into a SQL Server database. I plan to ...
4
votes
2answers
366 views

Help undoing an accidental restore on SQL Server 2014

Yesterday I made a serious mistake¹: Restoring the wrong database. Background (you may skip this section): I right-clicked the Test database and proceeded to the "Restore Database" section as ...
0
votes
3answers
62 views

Get all records from inner joined tables if second table does not have data

I follow below syntax by which I achieve the result. but it takes time IF NOT EXISTS(select * from #tblxyz) BEGIN INSERT INTO #tblxyz select NULL END Select a.* FROM tblabc a ...