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
27 views

Versioning entities in db with draft option, select query

I have postgresql db where i keep data for jee application. User can edit those data without creating new version of entity(draft). During edit, entity is copied as draft(version set to -1) after ...
0
votes
0answers
25 views

Upgrade SQL Server 2008 R2 [closed]

I have SQL Server 2008 R2 with SSMS version 10.50.1600.1. Can I upgrade this version to SQL Sql server 2008 R2 with SSMS version 10.50.4000.0? While restoring the database there comes an error. So ...
5
votes
2answers
117 views

Need help with null field values

I am having a problem with syntax. I have the following statement which updates a field in a table with a value from a field in a different table. I need the statement to update the field with the ...
0
votes
1answer
53 views

Duplicates when checking for two different column values based on a key that is not unique in table

I want to check to see if two orders were placed for one person within an hour of one another. Example data PERSONID | proc_code | TIME 123 | 1 | 4/25/2016 00:01:00 123 | 2 | 4/25/2016 00:01:00 123 |...
0
votes
0answers
14 views

SQL Server - DENY new server role to view any logins, server roles, or system databases

Wondering if the following is possible, to satisfy a requirement for login to be able to connect to an instance via SSMS but only be able to select from a view in a particular user database, without ...
1
vote
1answer
27 views

Get rows that have ALL join criteria

I have three tables growth_spaces, equipment and growth_space_equipment which links the two tables together. I'm trying to write a query to get all growth spaces that have all of the equipment that I ...
1
vote
2answers
82 views

How can I remove 'Using temporary ' and speed up my query

Hi I was working for hours to speed up this query using indexes, but its useless.. im tyring to optimize by eliminating 'Using temporary' and using only indexes. I wonder if there is another ...
0
votes
1answer
47 views

Innovative solution required - Remotely create stored procedure

I have a local stored procedure that I want to distribute out to many other SQL Servers without having to log onto each one manually. All servers can access the same network share if I want to go ...
0
votes
0answers
36 views

best practice to remove leading zeros when writing data to a destination

I have a query like in my other question DBA Question with Source Code of Select statement And i want to remove the leading zeros of the customer Number (KUNDE) with the answer of this question (...
0
votes
2answers
28 views

Execute query using daily timestamp (mysql event)

I was trying to find a way for give users a daily "point" per online day. So my plan was setup a MySQL event to run this query: UPDATE users SET points=points+1 WHERE timestamp > "X" (Where X ...
0
votes
0answers
63 views

Error ORA-12547: TNS:lost contact

I installed Oracle12c Database in my server i run SQLPLUS with the command 'sqlplus /nolog' and when i type 'conn sys /as sysdba' i got this error ORA-12547: TNS:lost contact Can you help me ...
1
vote
1answer
44 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. ...
2
votes
1answer
402 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
53 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 ...
1
vote
1answer
72 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 ...
0
votes
2answers
63 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 ...
4
votes
2answers
109 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
48 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
83 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
63 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
21 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
18 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
25 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
58 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
57 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
45 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
18
votes
7answers
458 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
81 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
23 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
263 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
61 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
31 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 ...
2
votes
1answer
54 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
108 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
24 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 ...
1
vote
1answer
23 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
21 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
29 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
22 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
1answer
56 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
38 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
35 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
14 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
42 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
37 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
39 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 ...