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

Error code 1064 when trying to add anything manually

So i tried adding via php and i noticed i got a error , so i went into phpmyadmin and tried adding it manually by clicking the SQL in phpmyadmin and write in INSERT INTO `test`(`IDtest`, ...
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
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
34 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 ...
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: ...
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 ...
14
votes
2answers
17k views

MySQL and window functions

It seems that MySQL does not support window functions. E.g. the simple: COUNT(*) OVER() AS cnt does not work. What I am not sure is if this applies to commercial version as well (I assume the ...
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
2answers
4k views

Optimize IN clause in where query with order by - MySQL

I am trying to optimize a query that using IN clause in WHERE to avoid file sorting. To make it easy , I created the following sample which shows the problem. Here is my query: SELECT * FROM `test` ...
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 ...
1
vote
1answer
180 views

Query to get total time logged in per week

I need to find the number of hours each user is logged into an application per week. The table structure is as follows: [ID] [UserID] [Time] (datetime field) [EventType] ("login" or "logout") There ...
0
votes
1answer
40 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 ...
3
votes
3answers
49k views

Oracle password has expired, however I have no way of changing it

When I connect to my local development database, I get an error saying the password has expired. I've been trying to change it with sqlplus to no avail: C:\>sqlplus ...
3
votes
1answer
23 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
119 views

fastest way to generate permutations of a table with n columns and put in a new table added one more question to same how to remove duplicate rows

As i am from mathematics background and require to do some huge permutations to start in my work kind help. If i have a table by name sample1 with n columns what is fastest way to get all the ...
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 ...
4
votes
5answers
134 views

Is there any best number for rows count in a table to start using indexing?

I'm a newbie in DBA. I want to know if there is any specified point in a table's rows count that when we reach that point, then we could use an index. I know there should be some dependency between ...
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 ...
2
votes
1answer
4k views

Extract MySQL information from a compressed tar.gz file

I have a back up-of a MySQL database which is contained in a .tar.gz file - I need to restore it. I've never had to do this before. The only way I can access the database is through phpmyadmin. There ...
1
vote
3answers
75 views

Remove duplicates in SQL Server Group By

I have 2 tables, Product and Type, the tables looks something like this: Product -Id -Name -TypeId Type -Id -Name I want to be able to write a SQL Server query which outputs the products grouped ...
0
votes
2answers
71 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 ...
10
votes
3answers
3k views

Problem with MySQL subquery

Why does this query DELETE FROM test WHERE id = ( SELECT id FROM (SELECT * FROM test) temp ORDER BY RAND() LIMIT 1 ); sometimes delete 1 row, ...
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 ...
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
51 views

mysql 5.7 json filter and features products

sorry for my english! I need use this column(feature) for show features, filters for products and in some language(It depends on the country). I have products table(innodb) and a column(json) ...
0
votes
0answers
27 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 ...
1
vote
1answer
45 views

Grouping similar rows together based on time

I have a table that records all the changes done to a model by a user. The table looks something like this: (SQLFiddle) In the table an action_hash is generated based on the model, model_id and ...
-2
votes
3answers
91 views

Split URL string into columns

Given the string: URL:'ID=1!&user=xxx&depart=21&companyId=43&workOrder=67' I need to get the result: Sno ID user depart companyId workOrder 1 1! XXX 21 43 67 ...
1
vote
1answer
111 views

Is there a setting in MySQL that will allow you to see the columns of a table on a query, even if the table is empty?

When I am writing code to go with a certain MySQL query, I like to do a basic select statement to see the columns, and to perhaps see what data is available as a sample. Sometimes, the table I would ...
0
votes
1answer
107 views

Want to create partitions of a MySQL table and change the charset of new partitions

I have a database table which contains large amount of data. I have written a script to create new partitions every week and drop old partitions (which are of no use). Currently, I have a column in ...
0
votes
1answer
28 views

How to write a SQL query to find the range from column values?

I have some fields in my database which contain values like 2483-5802 and 1245-3325. Now I have a user input and I have to find it from this column. Example if I have to search 2000 which will come ...
1
vote
1answer
12 views

Delete duplicated rows by modified_date - PostgreSQL 9.2

I've got some duplicated rows on a table called ja_jobs: To find those duplicated data, I'm running this Query: select * from ja_jobs WHERE clientid = 33731 AND creatortype = 'legacyrec' AND deleted ...
2
votes
5answers
124 views

MIN(column) with subquery

How do I query 3 tables (STUDENTS, MODULES, MARKS) such that I return the module name and the lowest mark score for student John Doe? SQL> DESC STUDENTS; Name Null? Type ...
0
votes
2answers
3k views

Cannot Add Foreign Key Constraint

Can anyone tell me why there are errors such as: Cannot Add Foreign Key Constraint And am I on the right track? My schema looks like this: DDL: DROP DATABASE ...
2
votes
1answer
423 views

How to include concatenated values in a dynamic pivot column value?

I am extending a dynamic pivot query. The original query works fine. In the second query, instead of displaying the date as a value for the pivot column, I would like to display a concatenated column. ...
4
votes
1answer
263 views

How can I prevent having inconsistent data under isolation level serializable in Oracle SQL?

I have the following table: InStores +--------+---------+-----+ | ProdID | StoreID | qty | +--------+---------+-----+ | p1 | s1 | 25 | | p1 | s2 | 70 | +--------+---------+-----+ ...
1
vote
1answer
24 views

How to Increment duplicate strings in a column

I have a table similar to what is below. I have listed only one column since that's what I'm interested in. | column --------- | foo | foo | foo | bar | bar | bar Below is the output that I ...
11
votes
3answers
676 views

Can IS DISTINCT FROM be combined with ANY or ALL somehow?

Is a postgres way of combining IS DISTINCT FROM with ANY or some other neat way of getting the same result? select count(*) from (select 'A' foo union all select 'Z' union all select null) z where ...
4
votes
1answer
91 views

Update with default values in case clause

I have a single table like CREATE TABLE Mytable (ID int identity, Name nvarchar(10)); GO INSERT INTO MyTable (Name) VALUES ('test1'); INSERT INTO MyTable (Name) VALUES ('test2'); GO ALTER TABLE ...
0
votes
1answer
62 views

Error launching sqlplus

I try to run sqlplus on my server with the command sqlplus /as sysdba and I got this error sqlplus: error while loading shared libraries: ...
1
vote
1answer
976 views

Why isn't SQL Server using my non-clustered index and doing a clustered index scan?

Here are my full tables: CREATE TABLE [dbo].[tblCrawlUrls]( [cl_IdUrl] [int] IDENTITY(1,1) NOT NULL, [cl_CrawlNormalizedUrl] [nvarchar](200) NOT NULL, [cl_RooSiteId] [smallint] NOT ...
0
votes
1answer
281 views

How to use explode mysql function in where condition with cakephp

I am using cakephp 2.6. I have payment_method fields in mysql table which store the values like 54,52,59,57 . Now my question is that how can i check if new posted payment method id is available in ...
1
vote
3answers
171 views

Finding count of null values in 500 columns of a single table

I have 500 columns in a table in SQL Server. I want to find the count of null values in each column. Is there a way to do it using a cursor and dynamic SQL?
0
votes
1answer
262 views

Select all from table using procedure oracle

I am trying to execute the select command in a procedure.Initially i retrived all of the columns of the table in the procedure using cursor. The real problem comes when I am trying to access the ...
2
votes
1answer
5k views

Converting ER diagram to tables and relationships in sql

Department : Branch is 1 : M Branch : Course is 1 : M Branch : Student is 1 : M Branch : Applicant is M : N Applicant : Student is 1 : 1 These are the conditions prescribed in the ER diagram. ...
1
vote
2answers
367 views

Calculating average with AVG for a list of numbers

This is probably a newbie question (bear with me) but how can I calculate the average for a list of numbers using SQL and the AVG function? I mean, I can add together numbers in SQL like: SELECT ...
0
votes
1answer
26 views

How do I find the day of week of a date-time field in postgres?

How do I find the day of week of a date-time field? I'm trying to extract the the day of the week from a column in a sql query in postgres. While SELECT EXTRACT(DOW FROM '2011-07-07') works, the ...
0
votes
3answers
79 views

Select distinct value from row if condition exists

I am new to SQL and I am having trouble constructing a SELECT statement. What I would like to do is select distinct parts that have a location of A1 if it exists. If a part has no locations of A1 I ...
1
vote
1answer
37 views

Strategies for reducing data to summaries within a table over time?

I have a large data set where large numbers of entries differ only by timestamp. In the short term it's beneficial to maintain individual entries so that we can correlate by time for individual data ...