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

learn more… | top users | synonyms (2)

0
votes
1answer
372 views

Last row from a query without Order by

I have something like SELECT var1,var2 FROM table where var3=X AND var4=Y order by var5 desc limit 1; Running this query takes roughly 5 seconds on my database which is far to slow. I was ...
3
votes
2answers
1k views

How to figure out Disk I/O by table

Is it possible to figure out which tables contribute the most to read / writes operations if all of them in same file? I been looking at the following DMV SELECT * FROM sys.dm_io_virtual_file_stats ...
1
vote
3answers
15k views

How to calculate exact month (in days) difference b/w two dates

I am trying to get all the rows where the visit date was exact before five months from to date but somehow DateDiff function is including 26th Sept 2013 record which is not right as five month has ...
0
votes
2answers
207 views

Long running simple query SQL Server 2008 R2 [closed]

I have a very simple query running in a SQL Server 2008 R2 instance: SELECT TOP 10 * FROM messages m INNER JOIN recipients r ON m.id = r.id WHERE m.timeStamp > '2014-07-14T00:00:00.000' AND ...
4
votes
2answers
391 views

How to avoid the SORT operation in SQL query that contains ORDER_BY condition?

I am optimising a PostgreSQL query which involves three tables and a ORDER_BY condition. It seems that PostgreSQL prefer to arrange a SORT operation to perform the ORDER_BY condition, rather than ...
0
votes
1answer
430 views

Database revision needed for sports events database

This is not a code review but more of a database schema review. I have a sports events website that I am doing in my spare time. I need assistance with the database design. I am about 80% completed. ...
2
votes
2answers
9k views

How do I include nulls during comparisons in SQL Server?

So basically in SQL Server a NULL means there is no value and thus can't be compared which returns some unexpected results. For example, the following query doesn't return rows where value IS NULL ...
0
votes
1answer
235 views

SQL Server 2000 : Upgrade from Personal Edition to Enterprise Edition

I've never tried this before but this is what I'm attempting to do : Backup all the databases Copy current master.mdf and master.ldf to another location (for later purpose) Uninstall SQL Server ...
0
votes
2answers
1k views

SQL Server 2008 R2 memory issue

I have read many posts regarding the memory but still its not clear. I have dedicated server SQL Server 2008 R2 with Windows 2008 R2, 32 GB RAM, 2 6 core processor and a database of around 60 GB on ...
0
votes
1answer
35 views

Adding a constant IF not in an other table

So I have this query. SELECT * FROM `Comment` WHERE `Target` = '".$_GET['pp']."' AND `Id` NOT IN (SELECT `Target` FROM `Delete` WHERE `Type` = 'comment') ORDER ...
0
votes
1answer
60 views

I am not sure why I am getting the “Invalid column name”, same code works on my other calculators… [closed]

In the following code: sqlCommandString1 = "Select Correction FROM Wlookup " + "WHERE Citystate = " + ComboBox1.Text Citystate lookup to result correction value. There are no spaces, and I tried ...
1
vote
2answers
265 views

Aggregate function / query in Oracle 11g

I am working in Oracle 11g. I have been racking my brain all day trying to figure out how to write a particular SQL statement (am a Java/.NET Developer, would not consider myself a SQL expert) I ...
0
votes
2answers
3k views

Query Optimization required - Subquery using Inner Join

Could anybody advise how can I optimize this query below please? SELECT IT.[iInternalTransactionID] , IT.[iInternalAccountID] , C.[iContractID] , IT.[dtTransactionDate] AS TransactionDate , ...
78
votes
4answers
19k views

Why use both TRUNCATE and DROP?

In the system I work on there are a lot of stored procedures and SQL scripts that make use of temporary tables. After using these tables it's good practice to drop them. Many of my colleagues (almost ...
3
votes
3answers
511 views

Nested table as condition prevents using index

The following query joining a subquery does a TABLE ACCESS FULL when the master-table uses nested table as condition. When the condition is a simple =, the same query does an INDEX RANGE SCAN as ...
3
votes
2answers
442 views

How to inner join some tables and outer join others?

I'm certain I'm going to be embarrassed by the eventual answer, but I've been googling around and banging my head against the wall and can't figure it out. I have four tables described below. I'm ...
1
vote
1answer
7k views

ORA-00933: SQL command not properly ended

I have created a database link to a remote database, MIGRERDOSSIER.US.ORACLE.COM, then I use it to insert records in a table in a local database. Here is the request I am trying to execute: insert ...
0
votes
1answer
39 views

Building a query programatically

I have three variables $color, $size and $category. Each oh them can be a specific value or may refer to all entries.I want to fetch all products that are $color = "red", $size = "Medium" and any ...
2
votes
1answer
183 views

How do I write a mysql query to combine data from related tables into a single summary?

I'm having trouble defining the data in a query for a chart. I want to summarize data from the first table and combine it with a data summary from the second table. Products - (serial_number, date) ...
1
vote
2answers
58 views

What subquery do I need to calculate this report?

I have two tables: tickets and comments. I need to run a report that shows me which ticket is "neglected" meaning that there has not been a comment in X amount of time. If I was to do this in PHP, I ...
1
vote
2answers
4k views

Sql statement to compare two table

I have two table with the same structure, i want an sql statement to compare them and see any modified line, deleted or added Table Product1 : ________________ |Product | Price| |Product1 | 10 | ...
1
vote
1answer
41 views

pass data from deleted table to another

This is the situation: I have a table, with name TABLE_1 I would like delete TABLE_1 and then create another table with the same name and some of its columns. TABLE_1 has records, but I need to pass ...
7
votes
3answers
2k views

Optimize select on subquery with COALESCE(…)

I have a large view that I use from within an application. I think I've narrowed down my performance problem, but I'm unsure how to fix it. A simplified version of the view looks like this: SELECT ...
1
vote
2answers
432 views

Get only the oldest entry in a join

So I have a table bugs |bugid| title| the table is lacking a date column. But when creating a new entry, most likely always a new entry gets created in bugnotes as well saying "bug created" with the ...
1
vote
3answers
253 views

Preventing SQL Injection

I know that this is very dangerous: EXEC sp_executesql 'SELECT * FROM ' + @Table But, is this safe from SQL injection? IF EXISTS(SELECT * FROM information_schema.tables WHERE TABLE_NAME = @Table) ...
1
vote
1answer
76 views

Merging a Select query into a view

I am using DB2. So, I have a view which is working fine. It is : Select Atr.Ssn_Tx ,Names.First_Na ,Names.Middle_Na ,Names.Last_Na ,Reff.Ref_Cd_Desc_Tx Suffix_Na ,MBR.PRSN_MBRSH_ID From ...
4
votes
2answers
13k views

Restoring MySQL Tables from .ibd, .frm and mysqllogbin files

For some reason, when I try to open up my tables that are stored in .frm and .ibd files (whether on MySQL or phpmyadmin) it gives me a syntax error or it says it does not exist. I've read the other ...
4
votes
2answers
5k views

Way to do MERGE with update source when target not match?

I am trying to write a stored procedure that will take new values from a temp table, merge them into an actual table, giving me the ids of the inserted rows, and the rows that already exist matching ...
4
votes
2answers
1k views

Is it possible for SQL Server to miss rows on a select * where PK = “value” query

We have just migrated an application to an SQL Server 2008 R2 from SQL Server 2005 and moved from a physical to virtual hosting environment. I have a very strange error in our application and I was ...
5
votes
1answer
359 views

Numeric data type with variable significant digits

I need to implement a data type which should preserve the last zeros in the decimal points in SQL Server 2008 R2. Example of the data are 1.002 1.210 0.00100 0.1401 My clients get lab measurements ...
1
vote
3answers
11k views

How to import Sybase .db file into SQL Server database?

I've been given a Sybase .db file by a client and I need to import that into a new SQL Server database so that I may see the data as well as the tables and their relationships, any ideas on how that ...
1
vote
0answers
55 views

Database Structure Definiton

This is a question more so on the table definition vs actual programming. I have 6 sets of potentially 255 unique fields. I have a potential of getting 40 million records per year of these. For the ...
0
votes
1answer
5k views

How to INNER JOIN and OUTER JOIN within the same table?

I am using PostgreSQL and I have the following tables: Product ID | Name | Price Receipts ID | Month | Quantity | Product_ID (FK) Sales ID | Month | Quantity | Product_ID (FK) I am trying to ...
0
votes
2answers
273 views

Difference Between SELECT and select in oracle sql

The algorithm used to find SQL in the library cache is based on the ASCII values of the characters that make up the statement. Means The SELECT instead of select statement will be parsed again ...
0
votes
2answers
130 views

Query Cache Implementation Details

I am new to DBA stack exchange & this is my first question. Kindly help me in finding a low level documentation for Query Cache module in MySQL since I am planning to work with the source code of ...
1
vote
1answer
24 views

Select the last logged change before a specified time from a change list

I want to get the last change for all issues issue, that was made before a specified end time. I have 3 tables. projects +---+------------+ |ID |project_key | +---+------------+ |1 |bug_tracker | ...
11
votes
1answer
5k views

How to preserve the original order of elements in an unnested array?

Given the string: 'I think that PostgreSQL is nifty' I would like to operate on the individual words found within that string. Essentially, I have a separate from which I can get word details ...
0
votes
1answer
117 views

Batch converting indexes to keys [closed]

I have a legacy MS SQL Server database where all the primary and foreign keys were created as indexes. I am developing the new version of our software with Entity Frameworks, which I have learned ...
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
2answers
92 views

I need help optimizing a not that complicated query that returns unexpedcted performance problems

I have a table that stores details about incoming and outgoing phone calls such as when a call was made, or when a call was received, the phone number of the caller, and the phone number of the ...
0
votes
2answers
217 views

Database search with multi joins

I have a MySQL database and I want to perform a little bigger search. I have about 10k records in one of the tables and It's expected to grow, but slowly. The biggest problem is that to perform the ...
1
vote
1answer
43 views

Limit balancing results by a column

I have a web crawler that have a table with tokens to crawl and each token have a fk_location column that is the website where that token must be processed. I also have a function that returns me the ...
0
votes
1answer
91 views

How can I control the order of a query regarding to inner join

I have 3 tables. They have table_a 5m records table_b 20m records table_c 20m records I'm performing the query below. SELECT * FROM table_a a INNER JOIN table_b b ON a.a_id = b.a_id AND ...
1
vote
3answers
10k views

Select query to retrieve values even if are null

I need some help in a query. Need to retrieve data from 1 table, even if one of the field has null value. Here's an example: Select name, SUM(credit) as credit From expenses Where name like ...
1
vote
2answers
298 views

getting one day timestamp in mysql

Please consider the following stored procedure I have: ORIGINAL Stored Procedure: -- -------------------------------------------------------------------------------- -- Routine DDL -- Note: comments ...
0
votes
2answers
227 views

Are these linked server queries susceptible to SQL injection?

I'm using a stored procedure to generate a create table script on the fly based on parameters passed into it. I am validating that the server exists, and then validating if the table exists in the ...
0
votes
1answer
44 views

Problem with foreign keys

I do ALTER TABLE bdt.auth ADD CONSTRAINT `depositor no.` FOREIGN KEY ( `depositor no.` ) REFERENCES bdt.depositors( `depositor no.` ) ON DELETE CASCADE ...
1
vote
1answer
273 views

How to make the SQL query more efficient?

I use mysql under Linux. My table is about 8000000 rows. The table has index(advertiser_id,activity_date),(activity_date,advertiser_id,activity_type). The table is looks as following: activity_date ...
1
vote
2answers
1k views

How to make a table name an attribute in PostgreSQL?

I have received hundreds of tables in PostgreSQL with identical structure and each containing similar data of a different day, so that the table name is the date of the data. I would like to Union ...
0
votes
1answer
23 views

Creation of a simple field failed in MySql [duplicate]

CREATE TABLE bdt.transactions ( `transaction no` int UNSIGNED NOT NULL AUTO_INCREMENT, data date , by int NOT NULL , hours int NOT NULL , ...