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

learn more… | top users | synonyms (2)

-1
votes
1answer
82 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
301 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
48 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
287 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
290 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
70 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
95 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
192 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
350 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
139 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
83 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
516 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
239 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
221 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
183 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
85 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
111 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
51 views

Insert into int8range from columns of another table

I have two tables in PostgreSQL: Table indexces_grid: +---------------+------------+ | Column Name | Type | +---------------+------------+ | indexid | bigint | | iprange | ...
2
votes
3answers
124 views

Performance of rare SELECT vs. frequent INSERT in timeseries data

I have a simple timeseries table movement_history ( data_id serial, item_id character varying (8), event_time timestamp without timezone, location_id character varying (7), ...
-1
votes
1answer
59 views

How can I return the different prices of my quantities? [duplicate]

I have two tables, one containing all the quantities of products by country and another table that contains the prices by country. When I add a condition for example country = "ch", I have the price ...
2
votes
1answer
168 views

Messaging:- Need to find last child of its parent with in the same table

I am making a messaging module. I need to find the all parents with their last child message. Like: +--------------------+----------------------+---------+ | pkUserDiscussionID | ...
1
vote
2answers
23 views

Determine a set of id's that exist in a set of tables: options needed to speed up or parallelize the algorithm

I am trying to determine a set of id's that that exist in a set of tables, but the size of the tables are > 10 million records, the database is denormalized, normalization is not an option and the ...
1
vote
1answer
270 views

MySQL - Number format - Separated with Comma

I have a query of MySQL like this- SELECT avg('total_cost') FROM 'employee' And then I am getting output like this- 1254589687.26587952 But I like to have a output like this- 1,25,45,89,687.26 ...
2
votes
2answers
95 views

OUTER JOIN with join condition causes unexpected results

SQL Server 2008 R2, but likely behaviour is found on all other versions of SQL Server as well. This may seem obvious, but to me it seems like a bug. The following query gives unexpected results, ...
3
votes
2answers
555 views

View active MySQL connections per user

I need a query that will give me the active or open number of connections to a certain database for a certain user. I have been searching for hours and have come up with nothing so far. I am close, so ...
11
votes
2answers
611 views

Unable to mirror a database SQL Server 2012

When trying to mirror a database using the following command ALTER AVAILABILITY GROUP SQLAlwaysonGroup ADD DATABASE test0916aj8CJ I get the following error Msg 1475, Level 16, State 105, Line 1 ...
0
votes
2answers
71 views

Need help with SQL query in Teradata

I have a table with the following columns member_number step_count social_activity -- (which contains both Challenges and Posts) high_fives Basically all the columns 2-4 are counts for members. I ...
5
votes
1answer
152 views

Why does this query not use the index that exists on SURNAME?

There is an index on SURNAME in the PATIENTS table, we run the following query but the index doesn't get used. DECLARE @SURNAME VARCHAR(30) = 'test' SELECT SURNAME FROM PATIENTS AS PAT WHERE (PAT....
4
votes
1answer
268 views

Star join query optimization - Change partitioning, use columnstore?

I am trying to know the best possible way to improve performance of a query given to me by my client. It contains a few tables which are joined and one of table is called dwh.fac_sale_detail which ...
2
votes
1answer
54 views

select from database with special characters

I am using php select * from `outstanding` where `lcocode`='PN007\00128' select * from `outstanding` where `lcocode` LIKE 'PN007\00128' kindly suggest code for fetching data with backslash I used ...
0
votes
1answer
22 views

How can I select a field value in PostgreSQL based to the date computation within the table?

I have a PostgreSQL table that have these values: dollar_rate; end_date ------ 1. 46.77; 2015-09-16 11:03:03.4082 2. 46.70; 2015-09-17 11:27:00.2446 3. 46.67; 2015-09-17 15:09:15.0354 Now I have ...
1
vote
0answers
35 views

How to parse and display row data as Column

I'm trying to display the row data from tbl_cte as a column. The result of column1 is alphanumeric e.g : "Data1.2.3". I need to parse it and display the "Data" as the name and "1.2.3" as the version ...
0
votes
1answer
58 views

Database & Application design to support Reporting, Analytics and Stats

I am trying to determine the best way to structure my database (MS SQL) and web application (JAVA) so that we can provide lots of reports and aggregated results in close to real time. Think "Google ...
0
votes
2answers
33 views

Is there a more satisfactory query to get the rows of 'A' ordered by the number of rows of 'B' associated with that row of A in SQL

Suppose I have two tables A and B. Table A has a primary key, A.ID for instance, and each row in B contains a reference to some A.ID (there is a foreign key column in B). As a specific example: A: ...
0
votes
2answers
230 views

How do you remove a mirrored database SQL Server 2012

I have mirrored databases (synchronized), how do I delete such a database correctly? I tried use master alter database test0224aEd62 set single_user with rollback immediate drop database ...
2
votes
1answer
163 views

Best Practice for Passing and Working With Parameters Security/Performance

Our senior programmer has been having me write stored procedures in the following format to protect against injection attacks. He says that the best practice is to take the parameter in, then declare ...
1
vote
0answers
91 views

Sql query , please check whether my solution is correct?

Problem is: Consider the following relational schema: COURSES (cno, cname) STUDENTS (rollno, sname, age, year) REGISTERED FOR (cno, rollno) The bold attributed indicate the primary keys for ...
1
vote
0answers
18 views

How to copy the query results from the grids when having more text in the single column

when having more text in the single column the result is trimmed out to diffrent rows when copying to excel.This is working fine in SQL 2005 mgmt. studio but in later versions (2008,2012) the result ...
-1
votes
1answer
1k views

How to get dates between two dates from MySQL table?

I want to get dates between two dates from MySQL table EXAMPLE fromdate(2015-01-01 and so on....), todate(2015-01-10...and so on) are the columns i have to get dates between these two columns. ...
2
votes
1answer
52 views

Tuning a query with: index, window function?

Description Environment: Centos 6-x64, Postgres Plus Advanced Server 9.3. I have a table with 4 columns with data as below: id code_id effective_date name 24 12 ...
3
votes
1answer
113 views

Expand table rows based on column value

I have a table with two columns; EVENT_DATE date and RANG number the first column holds a date for an event while the second column is for the period of that event. here is a sample of data | ...
1
vote
3answers
273 views

Make another column in a table equal to the primary key column

In my notes table (MySQL database) I have id as the primary key. I want the column originalid to be the value of the primary key. Here is the query I run after creating a record which I thought would ...
0
votes
1answer
94 views

Retrieving only the last row of a table, in the absence of a primary key

I have a table that has 3 columns: Column 1: contains XML data (that contains a timestamp value) Column 2: contains the file name(with the path to it) corresponding to XML data record (as to which ...
2
votes
0answers
115 views

Log file Zeroing

During the creation of the database, I am getting below mentioned information about log file zeroing and I have highlighted my understanding in bold fonts. 2015-08-23 12:42:36.850 spid51 ...