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

learn more… | top users | synonyms (2)

0
votes
2answers
189 views

Want to create an SQL function that removes table row duplicates

I'd be following the procedure outlined here (unless of course someone has a better way to do it), and I'm wondering if I could just have some help being pointed in the right direction on how to ...
-1
votes
2answers
450 views

Tutorial in datamining [closed]

Where can I find about tutorial of datamining in SQL server 2012 or 2008 R2? The purpose is to improve my knowledge in datamining.
5
votes
1answer
248 views

Electricity off while shrinking database, how can I recover my database?

While i execute shrink database, the electricity has been gone down, I can't access my database now, it is (In Recovery), how can i recover it?? Note: it is a very huge database: 25 GB. EDIT: My ...
0
votes
1answer
208 views

MySQL query took 98 seconds to run but EXPLAIN looks okay

I have a query that took 98 seconds to execute this morning, but I can't recreate the problem now, and the EXPLAIN output isn't giving me any clues. Am I missing something? Here's the SQL: SELECT ...
2
votes
1answer
88 views

MySQL query took 98 seconds to run but EXPLAIN looks okay

I have a query that took 98 seconds to execute this morning, but I can't recreate the problem now, and the EXPLAIN output isn't giving me any clues. Am I missing something? Here's the SQL: SELECT ...
1
vote
2answers
580 views

Lost connection to MySQL server during query

Here is a part of my query SELECT * INTO OUTFILE 'Target_File_Name' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '' FROM (SELECT 'Start time','Calling no','Called ...
32
votes
3answers
3k views

Unexpected scans during delete operation using WHERE IN

I've got a query like the following: DELETE FROM tblFEStatsBrowsers WHERE BrowserID NOT IN ( SELECT DISTINCT BrowserID FROM tblFEStatsPaperHits WITH (NOLOCK) WHERE BrowserID IS NOT NULL ) ...
4
votes
5answers
6k views

How to get the total days per month between two dates?

Given two dates like 20120302 and 20120605 I need to get a list of Months and the total days in those months that fall between those two dates, like so: March 28 April 30 May 31 June 03
2
votes
1answer
317 views

Flat query to Column structure

I am trying to structure a query for a calendar view. I want each row to return the details for a person and what they are doing for 7 days (split into AM and PM) So the result set might look like ...
4
votes
1answer
3k views

Understanding optimisation of complex SELECT queries

I have a people table with basic contact info in. I have a bunch of criteria that can select Id values (fields called pid) from the people table. I want records that match ((criterion 1 or criterion ...
4
votes
3answers
2k views

SQL Server stored procedure keeps restoring to previous one

There's a Stored Procedure in my database which keeps restoring to the old one, I Alter the stored procedure using Management Studio, however some time later, unexpectedly, the stored procedure ...
5
votes
2answers
153 views

querying a table of events for reporting

The background (masking a real-world problem behind a word problem) Farmer Brown has a peach orchard in California and ships hundreds of thousands of peaches all across the country. When a shipment ...
1
vote
0answers
149 views

Database model problem from MS Access to SQL Server 2005

Good Day, I am a C# programmer but I have a problem with a database model on SQL Server 2005. I have a client who told me to rebuild his database from Access to SQL Server 2005 and actually I did but ...
2
votes
0answers
378 views

What is causing all of the msrepl_tran_version columns to become out-of-sync between locations in transactional replication setup?

I have been having a recurring issue with transactional replication setups at the firm I work for. We use SQL 2008 R2. Seemingly without warning or any replication errors, all of the ...
3
votes
1answer
700 views

Is it possible to make a reference to the result of an aggregate function in a SELECT clause from the same SELECT clause?

I'm not a Database Administrator--just a Software Engineer. I would like to know if it is possible to reduce the following T-SQL query: SELECT SUM(Price * Quantity) as 'Total', SUM(Price * ...
2
votes
1answer
3k views

Named Parameters in SQuirreL SQL?

Is it possible to set named-parameters in queries SQuirreL SQL?
0
votes
1answer
3k views

how do I merge two views

I have basically two views named customerV and productV. the SQL statement in the customerV view is : SELECT CUSTOMER_REGISTRATION.SURNAME, CUSTOMER_REGISTRATION.OTHER_NAME, ...
15
votes
1answer
29k views

Varchar(Max) field cutting off data after 8000 characters SQL Server 2008

I have a field to store some data, the field is declared as varchar(Max). To my understanding this should be storing 2^31 - 1 characters but when I enter some content over 8000 chars it cuts the rest ...
4
votes
2answers
658 views

Using surrogate key after merging the person's data?

This question is about business intelligence with sql server 2008 R2. If you have a person named Jim Brown with his birthdate and social security number (SSN) being located in three different source ...
2
votes
1answer
407 views

Rewriting a query and eliminating subqueries

I have the following query : SELECT ws_id, n50, num_singletons, num_segments_less_100, kmer, num_all_reads, number_bases_in_contigs, num_bases_in_singletons FROM stats_assembly WHERE ws_id IN ( ...
1
vote
1answer
3k views

Decode query truncates a date

I am not sure if I am looking at this problem the right way: I have a query like this on an Oracle database SELECT DECODE( TO_CHAR(MIN(DATE), 'YYYYMMDD') , '' , NULL ...
0
votes
3answers
202 views

Dog slow query, needs optimizing, please help

I found this query in our web app that has been made by hired programmers. It performs like a dog and needs optimization, but I'm no MySQL wizard (yet), unfortunately. Please help, anything is ...
0
votes
1answer
6k views

Returning Row Count in a Stored Procedure in Oracle

I'm mostly SQL Server person, so forgive me if this is too close to SQL Server, but in a nutshell using Oracle 10 I'm trying to build a procedure something like this: PROCEDURE SP_INFO_EX_S( ...
3
votes
1answer
127 views

Complex MySQL Query returning almost all the correct rows

I've created a SQLFiddle with my schema and some test data. The calculated columns aren't working for some reason in SQLFiddle, but they come out fine in my MYSQL Workbench. Assume they work ...
5
votes
2answers
32k views

Using Same CASE WHEN Conditions For Multiple Query Columns

Is there a "better" way to rewrite a SELECT clause where multiple columns use the same CASE WHEN conditions so that the conditions are only checked once? See the example below. SELECT CASE ...
8
votes
5answers
46k views

Update existing rows with squence number/char or any unique data

I added a new column in the Table X This column "cn" has to be unique and mandatory, but old data don't have any value. How to update the existing records with sequecely or random unique data? ...
8
votes
3answers
6k views

How to boost performance of virgin queries in MS SQL Server?

I have ASP.NET web site that does it's own independent caching of data and data does not change for long periods of time, so it does not need to query SQL Server second time with same query. I need to ...
3
votes
3answers
30k views

How to select/count rows where the same value exists in a column but not in other column all at the same table

I have this table structure and data: CREATE TABLE IF NOT EXISTS `default_relations_users` ( `id_user_rq` int(11) NOT NULL, `id_user_ap` int(11) NOT NULL, UNIQUE KEY `rusers_rq_ap_idx` ...
4
votes
1answer
613 views

SQL unpivoting multiple rows/columns, but keeping the rows grouped together, and in the same order they were selected

This is a complicated explanation, so sorry that it is going to be a wall of text. I'm dealing with pre-existing code and am trying to work out a solution, so I can't alter the core way any of this ...
2
votes
2answers
114 views

Performing a “Spread Negitive” over a dataset

I have a situation where a table that represents outstanding balances and credits are both contained in one table. What I need to do is apply all outstanding credits (preferably in order of oldest ...
0
votes
2answers
142 views

full outer join where join key in result always has value [closed]

I wish to do a full outer join between two tables A, B joining on A.ID = B.ID. Now, it is quite easy to include A.ID and B.ID in a result set. this will include rows where: A.ID is null, B.ID is not ...
1
vote
1answer
166 views

How to get non-deleted messages for one user_id if the same user_id appears in two tables at same time?

I'm trying to get non-deleted (deleted = 0) messages for one user_id if the same user_id appears in two tables at same time, but something is wrong because I am not getting the correct or expected ...
-1
votes
2answers
1k views

Parsing a string result from a query

My sql server 2008 query returns a very long strings such as: GET ...
0
votes
1answer
70 views

Select a value for the max

I have a query which selects a max value over all the values of in a datetime column (dtimesent). select dtimesent, max(ResponseTime) AS maxtime FROM allrequests where Request like '%Requestsa%' ...
5
votes
1answer
385 views

Query to find optimal filegroups for objects

I have been assigned a task to create four file groups (one per volume) locate tables so that they are located in a manner that during an ETL process no filegroup is being read and written to at the ...
2
votes
3answers
2k views

Copying distinct rows from one table to another

In SQL Server, I can copy one table into another by doing: Select * into desttable from sourcetable However, there are duplicate rows in sourcetable and I would like to just copy distinct rows. How ...
2
votes
1answer
70 views

Rewriting a query to be more efficient

Currently I'm using: SELECT t.CustomerLastName FROM ( SELECT SUM(OrderLine.Amount * Product.Price) AS OrderLinePrice, Orders.Id, Customer.Lastname AS CustomerLastName FROM OrderLine, Product, ...
2
votes
1answer
2k views

SET QUOTED_IDENTIFIER issue

I have created a Database Project in Visual Studio 2010 for a database which is in 2008. I am having issues in deploying the database as I don't know why, but at the time of deployment in some ...
8
votes
4answers
75k views

Combine column from multiple rows into single row

I've got some customer_comments split out into multiple rows due to database design, and for a report I need to combine the comments from each unique id into one row. I previously tried something ...
1
vote
1answer
86 views

Filter On Indexed, Formatted DATE

How do I write my calling query (without modifying V_SPECIAL_SAMPLES) to use the index on sample.sample_date (filtering by date first)? I believe it's the TO_CHAR and TO_DATE conversions that prevent ...
2
votes
3answers
338 views

How to get all non deleted messages from one user

I have two tables one for messages called default_messages and the other for recipients called default_recipient. This is how SQL for those table looks like: CREATE TABLE IF NOT EXISTS ...
0
votes
1answer
804 views

NULL values on MDX query (Total Column has Values)

I'm a novice with MDX, so sorry if I've missed something. (and sorry about my english too!) The following query: WITH SET [ROWMEMBERS] AS '{([BIN_SCH_INV_GRO].&[1]) , ...
6
votes
2answers
1k views

Is there a better way to create dynamic time averaged series over a dynamic frequency?

I've got a series of tables with lots of high precision data collected from various devices. The intervals that they were collected on varies and even wanders over the time series. My users want the ...
3
votes
2answers
3k views

Why can the SA account no longer access the master database?

I'm somewhat stuck. We have a database server which was set up and configured by a third party. It's for a third party product so unfortunately we're stuck with the way they've configured it. Their ...
3
votes
1answer
817 views

How to order query results by tag weight in IN('tag1', 'tag2', 'tag3')

Consider a simple query as SELECT * FROM posts JOIN tags USING(post_id) JOIN tag_map USING(tag_id) WHERE tags.tag IN('tag1', 'tag2', 'tag3') tag_map is a many-to-many relationship between tables ...
2
votes
1answer
255 views

How to split tables without affecting all stored procedures

We have merge replication setup between corporate database and remote clients. Lately we realized that people working on the corporate server do not need data to be tracked by merge replication as ...
2
votes
0answers
137 views

sql data to a csv-file via cobol

I 'm using the following code : string 'insert into ' delimited by size 'tgamGameTeams ' delimited by size '(Id,Name,RemainingBudget,TeamGamePoints,GameId) ' ...
5
votes
5answers
3k views

Altering table schema takes too much time

I am using PostgreSQL database. I have a table with millions of rows. I have a varchar column with 2000 size and i want to make it to 4000. I ran the alter table command, but it is taking too much ...
1
vote
2answers
317 views

Why are my results returned twice?

I've this query: SELECT DISTINCT `default_wall_posts_comments`.`c_id` as c_id, `default_wall_posts_comments`.`comments` as comments, UNIX_TIMESTAMP() - default_wall_posts_comments.date_created ...
2
votes
1answer
803 views

Disabling Indexes and Auditing for large data import

Hopefully the title describes this sufficiently, but I have a process that runs and performs thousands of individual inserts and updates (nothing I can do to change that). My questions are: 1. Is it ...