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

Will repeated, identical expressions get processed again?

I am new to SQL server and I was trying to use an alias in a where clause and then I realized the order of processing makes it hard to do that, so I used this: SELECT DISTINCT * FROM (SELECT LEFT(...
2
votes
1answer
37 views

Excluding rows by lots of ids

I need to exclude some rows that the user has already seen. Currently I am doing this by simply comparing the ids that the user has seen to the row's ids by running this query (there are also other ...
1
vote
1answer
55 views

Left join not working with sub-query

I've got table of scores where each entry corresponds to a particular (student, subject) pair. CREATE TABLE score ( id serial PRIMARY KEY, value integer NOT NULL, subject_id integer NOT NULL, ...
1
vote
1answer
51 views

Replace 2 spaces by 1 space in all values of a column

I need to replace 2 spaces by only 1 space for all entries of 1 column in an Oracle database. I found this and tried it: SELECT REGEXP_REPLACE('MODEL', '[[:space:]]{2,}', ' ') new_string FROM ops....
-2
votes
1answer
20 views

Filegroup can't be deleted [closed]

i had a Partitioned table, i somehow deleted the Table and Partition functions and schemes but i am finding it hard to drop the filegroups, any suggestions?
1
vote
1answer
44 views

putting together contents of XML column - from all rows of a table - how to get it done without a “while”?

I have a table called sizeguide with the definition you can see below. I am interested in the SizeGuideXML column, which is type XML. I had a mission the objective was, for each SizeGuideDesc, get ...
4
votes
2answers
233 views

Get the last 5 distinct values for each ID

I'm working with PostgreSQL 9.4. I have a table that contains the following entries: id | postcode | date_created ---+----------+----------------- 14 | al2 2qp | 2015-09-23 14:46:57 14 | al2 ...
1
vote
1answer
599 views

SQL Server stored procedure producing data related to months between two dates [duplicate]

I'm stuck on the following problem. I have a start range date and end range date calculated by a query and I have three columns also produced by another query, the outcome of the last query is: ...
0
votes
1answer
62 views

SQL Server Management Studio 2012, authentic multi-user login [closed]

Issue: I set up SQL Server and SQL Server 2012 Management Studio on the Administration login. When I login on my login I can open SQL Server 2012 Management Studio, however I cannot access the ...
1
vote
2answers
83 views

How to find a word in SQL? [closed]

Lets say you are filtering on rows in a column that contains the word Engineer in a phrase. How can I exclude words like 'Engineering' or 'Engineered' or 'Engineers' when I wouldn't know all the ...
1
vote
0answers
195 views

Two servers with the same exact specs taking way different times to run the same query

I have 2 servers with the same exact specs: Test Server and Production Server The same exact database with the same exact data is on both of these servers. I remote into each server using remote ...
3
votes
1answer
163 views

Always fetch 3 records in a particular order

I have a query which gets 3 books (ABC1,ABC2,ABC3) and their details. I need them in a particular order for pivoting so I have a Row number column for the same. People read none/some/all books. Table:...
0
votes
0answers
178 views

Converting SQL Server query to MS Access

I have the following query in SQL Server: SELECT p.Company Advertiser, COUNT(DISTINCT t.ID) [Sales Call Count], COUNT(DISTINCT j.ID) [Job Count], 100.0 * COUNT(DISTINCT j.ID) / COUNT(DISTINCT ...
4
votes
2answers
217 views

Use column values in ORDER BY and LIMIT clauses

I have this table in a Postgres 9.3 database: I need to use the content of columns order and limit to sort and filter this table. The table will be sorted by column first_name. The final result ...
0
votes
0answers
35 views

Query performance tuning with joins on views with large data

I've this query in oracle. This is basically the query of a BA report which I'm trying to tune. SELECT DIM_PROJECT_OL.PROGRAM_ID, DIM_ANALYTIC_STRUCT_ACCOUNT_OL.STRUCTURE_ID, SUM(NVL(...
0
votes
0answers
168 views

How to intersect two query results?

I want to intersect two query results. But since my database is MySQL, I cannot use INTERSECT. How can I work around it? This is the INTERSECT query I have that wouldn't work in MySQL: ( SELECT ...
0
votes
1answer
121 views

Select values of a column into one row - Sql Server [duplicate]

I want to select in one row the value of a column that appears in multiple rows, I have the table Solution: | StudentID | SolutionDate | SolutionTime | SongID | ---------------------------------------...
15
votes
3answers
432 views

Slow update on large table with subquery

With SourceTable having >15MM records and Bad_Phrase having >3K records, the following query takes almost 10 hours to run on SQL Server 2005 SP4. UPDATE [SourceTable] SET Bad_Count= ...
2
votes
1answer
86 views

How to develop a database (workflow)?

tl;dr: I know a few SQL semantics and I know how databases work (basically) but I never created one that had a considerable size. I lack of knowledge concerning workflow, SQL-code management and very ...
0
votes
2answers
299 views

Updating rows in tables concurrently using Multithreading in Java

I have an excel file with 50000 rows and I'm updating a MySQL table using hibernate by uploading that excel file from a Java Program. It is taking a huge time to update all the rows. Can it be ...
0
votes
3answers
133 views

Dynamic query execution error

I'm trying to execute the following dynamic query code: declare @sql nvarchar(max) set @sql = 'DECLARE @DatePartitionFunction nvarchar(max) = N''CREATE PARTITION FUNCTION DatePartitionFunction (...
-1
votes
3answers
873 views

Updating a table from another database [closed]

I have to update "table2" from another "table1" located in another database. The "table1" located in another database is updated daily with transactions from the last day. I just want to update "...
-7
votes
1answer
77 views

how to add the two values from different column in sql [closed]

I have the data below column 1 | column 2 | total 23 | 25 | column 1 + column 2
0
votes
0answers
760 views

Insert string content into a blob in DB2 database using a simple SQL query

I am having trouble inserting in a BLOB field some text content (in fact it's an XML, I know there are field-types more suited than BLOB for this type of content, but I simply can't change it). I ...
3
votes
1answer
47 views

A difficult query for me

I have the next table: CREATE TABLE `alarms_9_2015` ( `time` datetime NOT NULL, `key` varchar(24) DEFAULT NULL, `point_name` varchar(64) DEFAULT NULL, `message` varchar(60) DEFAULT NULL, `...
0
votes
0answers
90 views

How to audit mysql changes

Have table with 4 000 000 rows. Need audit any data changes in table. I create triggers for this table and audit all changes data,but is there any better way for audit changes in table ? And also one ...
-1
votes
1answer
80 views

Filter rows on file type [closed]

I want to filter results with file type = .jpg / png from database stored value. here is my code. // Create connection $conn = new mysqli($SERVERmysql, $DB_USER, $DB_PASS, $DB); // Check connection ...
2
votes
2answers
285 views

Use random string variable in a insert

This is one of my first tries to code something. I'm trying to develop a code, to add first name, last name, and date, randomly. I have this for now: declare @date date, @val1 int, @...
1
vote
1answer
57 views

CONCAT with multiple INSERT INTO … SELECT … WHERE

I have a number (currently 6) of tables within multiple schemas, and I need to insert a number of (2000) rows into a table in each schema. The idea is that I have the procedure below, and simply ...
0
votes
1answer
46 views

RETURNING value from SELECT table in INSERT .. SELECT

I have a table that looks like this: | column | def | | ------ | ---- | | id | uuid | | status | int | | data | text | I'd like to write a query to duplicate a row (changing only a few ...
2
votes
1answer
244 views

Categorize age ranges and display in one column

I want to see the data as follows Member Age Category Test1 25 Under 30 Test2 55 51-60 Test3 67 Above 60 I have used the below query (from an answer to my previous question) to ...
-4
votes
1answer
276 views

Query to calculate age of a member based on the Date of birth and Current date [duplicate]

I have a requirement to calculate the age of a member based on the Date of birth and Current date, and then classify that age into categories like Under 30, 31-40,41-50,51-60 and Above 60. Can some ...
1
vote
1answer
67 views

Postgres : Using WITH … AS insert into to return non-inserted column

I'm creating an import job which uploads to a temporary table and then splits the data into two separate tables, joined by a key. What I'm doing is more complicated than the following but this simple ...
0
votes
0answers
18 views

msdb.dbo.backupset vs user table

Is there any particular advantage or disadvantage to using msdb.dbo.backupset as opposed to a custom table to log backup details, other than the proven reliability of backupset, and the risk of ...
1
vote
1answer
87 views

Identify sequence of events based on certain rules

I need help on identifying a sequence of events in SQL Server 2008 R2 rows please: This is the sample data with SQLFiddle: ID EventTime TypeID EventNr 1 06:34:51 ...
2
votes
1answer
188 views

Select distinct values between two values in different field

I have a table named products like this: | product_name | start_year | end_year | |--------------|------------|----------| | wine | 2001 | 2012 | | cheese | 1999 | 2010 ...
1
vote
2answers
293 views

what is the quickest way to delete duplicate rows?

I need to delete duplicate rows from a large table. what is the best way to achieve that? currently I use this algorithm: declare @t table ([key] int ) insert into @t select 1 insert into @t ...
2
votes
2answers
72 views

Moving database from one server to another

I have a virtual machine running SQL Server 2012 in Azure and have also generated a script of my database to be run on the SQLSERVER2012 VM on Azure, however I am getting an error "C:\Program ...
-3
votes
2answers
135 views

Get rows which match set of values [closed]

I have a table with the following sample data: If I send qId=1,3,4then the aid of 6 should be returned because this aId (6) is exists for all the input qId values (1, 3, 4).
2
votes
1answer
79 views

CPU Utilization by Long running Query

Is there any way to find how much CPU is getting consumed by a query running for long time in Database?
8
votes
1answer
447 views

Replace value of strongly typed xml element in SQL Server with XQuery

Given a element, defined within an XML Schema Collection as such: <xsd:element name="xid"> <xsd:simpleType> <xsd:restriction base="xsd:string"> <xsd:...
0
votes
1answer
200 views

Column doesn't exist when using CASE expression in WHERE clause

I have this SQL SELECT price_amount, price_currency, CASE WHEN price_currency='USD' THEN price_amount * 3.9024 WHEN price_currency='EUR' THEN price_amount * 4.413 ...
-1
votes
1answer
212 views

Is UNION faster than UNION ALL with DISTINCT?

UNION can eliminate "Distinct" use to optimize the query? Like below, NO distinct is needed if the UNION is used to return distinct set. Sample data declare @T1 Table ( ObjId1 int , Name varchar(100)...
0
votes
0answers
15 views

Problem in Declaring host variable in Oracle [duplicate]

What I found on googling is this Host Variables Have global scope use DEFINE keywords to declare it call using &v_name within pl/sql block act like constraint cannot be overlaped cannot ...
0
votes
1answer
160 views

full outer join not working

I Want to do a full outer join on 2 tables but I get this error, what am I doing wrong? mysql> select * from cajas full outer join almacenes on almacenes.codigo = cajas.almacen; ERROR 1064 (...
2
votes
0answers
75 views

How to get past Windows Server rate limiting repeated SQL Server connections over network? [closed]

Currently I have ETL software installed/ running on a machine separate from the destination data warehouse database (for security purposes apparently). So it must send data/ write records across a ...
2
votes
1answer
30 views

Query a table with linked objects as columns

Title of the question is not clear, I'll try to describe it. In my model I have the following : A Network object includes Lines, and a Line object includes Routes (this is for public transportation)....
6
votes
1answer
106 views

Is there a way to allow a `unique` key to be the same on a max of 3 records?

Is there a way to allow a unique key to be the same on a max of 3 records? I have a table with urls in it. I don't want my program to put the same url in more than three records. It has a reason for ...
9
votes
1answer
2k views

Could a null column be part of a primary key?

I'm developing a SQL Server 2012 database and I have a question about a One-to-Zero-Or-One relationship. I have two tables, Codes and HelperCodes. A code could have zero or one helper code. This is ...
0
votes
1answer
49 views

Insert into int8range from columns of another table

I have two tables in PostgreSQL: Table indexces_grid: +---------------+------------+ | Column Name | Type | +---------------+------------+ | indexid | bigint | | iprange | ...