DO NOT USE this for questions targeting Microsoft SQL Server, use the tag sql-server instead. "SQL" is an acronym for "Structured Query Language", it is used in relational database management systems (RDBMS) to query, update, delete, and insert data as well as modify the structure of the database. ...

learn more… | top users | synonyms (1)

0
votes
1answer
185 views

Optimizing a query with ORDER BY in a derived table

The query below takes too long to execute (58 seconds). If I run two more queries like this with different nid values, the second and third query take much more time to execute. How can I optimize it? ...
1
vote
0answers
146 views

How to index for a group by with columns from different tables?

Is it possible to create an index from columns of 2 different tables? If not, how do we optimize a query that does a group by on columns of 2 different tables? For example: the following ...
1
vote
0answers
106 views

MySQL Replication fails - commands received in wrong order?

The other day, I had this issue with one of our MySQL 5.1.60 master and 5.1.61 slave setups: Last_Error: Error 'Can't create table 'foobar.#sql-4b87_2' (errno: 150)' on query. Default database: ...
1
vote
4answers
783 views

Pick one single row each time from either table?

I will run a query and get a list of aID and based on that I will try to match below tables.For each aID I just want one record either from tblGAlert or tblEAlert based on gDateTime and eDateTime ...
3
votes
1answer
302 views

Combine - Select Into, with Insert Into Select

I've read many explainations like this one here that say "Select Into ..." is to create new tables and "Insert Into ..." is to append to existing tables. I'm automating a coworker's sql scripts. ...
2
votes
1answer
3k views

SQL Server select with regex

Can we use regular expression to select the item from database? The table item is like below Table column|name| 10.01.02 | a | 100.2.03 | b | 1021.10.04 | c | Now my problem is that i ...
1
vote
2answers
195 views

Using max for each sub group does not seem to work.

I have a table that looks like this in Oracle 11g: +----------------------------------------------------------+ | ACCT_NBR | MAIL_TY | ORGA | PERS | RUN_DATE | ...
3
votes
2answers
149 views

How do I deal with items that I need to repeat monthly?

I have a table where users will enter transactions on a weekly basis, these transactions will either be single items or will be recurring i.e. rent would be recurring and should be the same for every ...
1
vote
1answer
300 views

Data modeling membership and profiles

Anyone have an idea on how to solve this issue. Im modeling my membership and user profile DB and run into one issue I have to solve. I have this layout of tables. Membership (where all basic info ...
0
votes
1answer
453 views

Why would running a SQL query overnight break my database for the following day?

I apologise in advance if this is a duplicate question (which I bet it will be). I had a good search through and found similar questions but nothing that seemed an exact match. I wrote a report that ...
0
votes
1answer
288 views

PostgreSQL indeterminate ordering of results [closed]

When I have this query: SELECT DISTINCT "spree_products".id FROM "spree_products" INNER JOIN "spree_variants" ON "spree_variants"."product_id" = "spree_products"."id" AND ...
2
votes
1answer
2k views

Postgres nested hstore select query

I have a carts table with items hstore column. An example entry in this column is: carts.items row#1 = { "1614" => {:quantity=>"100", :price_cents=>1655}, ...
2
votes
2answers
463 views

SQL Server 2008 R2 : A Possible indexing strategy for the given schema?

We have these four tables: Store ( row bigint, id uniqueidentifier, name varchar ) Products ( row bigint, id uniqueidentifier, storeID uniqueidentifier, productname ...
0
votes
1answer
43 views

SELECT to only see Offsetting Transactions in table?

I have a temp table like this I have been playing with it but I don't know how to get same PortCode,Cusip combo to give me only offsetting transactions (Buy,Sells) not (Purchase,Purchase) combos. ...
3
votes
2answers
549 views

Efficiently query MAX over multiple ranges

When performing a MIN() or MAX() over a single range covered by an appropriately sorted index, SQL Server does a TOP() and so returns the value after fetching just one row. When the search criteria ...
3
votes
1answer
769 views

Dynamic file name for file import

I'm trying to figure out a way that I can pass a filename into a stored procedure that can import a file. However, after setting up the SQL dynamically, the object can't be found. DECLARE @fileName ...
2
votes
1answer
85 views

Correct database solution?

So I work for a smaller company and currently we need to do our data processing with a desktop solution. We're running into memory limitations and the overall solution in most people's eyes ...
2
votes
1answer
70 views

How to go about modeling the main object of relationship?

I'm using MySQL and MyISAM tables. I have a bunch of objects that I want to group together 1 object belongs to 1 and only 1 group 1 group can have many objects 1 group has 1 required main object ...
2
votes
1answer
1k views

Need Help Executing a Remote Task from SQL Server Agent

There is a scheduled task running on a batch server via the Windows Task Scheduler that sometimes overlaps with other scheduled jobs in the SQL Server Agent causing CPU issues. We can solve this if ...
1
vote
1answer
260 views

Stored procedure to validate xml content against dtd

I have a stored procedure which creates xml content and then is stored to a xml file. Is it possible to validate the generated xml content against a dtd file? Thank you in advance!
2
votes
1answer
1k views

What is the difference between COUNT(*) and COUNT(*) OVER()

Take the following code example: SELECT MaritalStatus, COUNT(*) AS CountResult COUNT(*) OVER() AS CountOverResult FROM (schema).(table) GROUP BY Marital Status COUNT(*) ...
3
votes
1answer
6k views

Get result of joining multiple tables as one row

I have these 2 tables: table1: id | name --------- 1 | john 2 | jack table2: id | profile_id | institution ----------------------------- 1 | 1 | SFU 2 | 1 | UBC 3 | 2 ...
2
votes
2answers
659 views

Optimize simple query using ORDER BY date and text

I have a query to a table in Postgres with an order based on a date field and a number field, this table has 1000000 records The data types of the table are: fcv_id = serial fcv_fecha_comprobante = ...
2
votes
1answer
2k views

What is the difference between TABLESAMPLE(10) and TABLESAMPLE(10 PERCENT) in SQL SERVER 2012

My understanding of the use of TABLESAMPLE is that it returns the top n percent of rows in a result set where TABLESAMPLE(n) So I wrote the following queries to just check (as I'm learning T-SQL) ...
2
votes
3answers
162 views

Which of these two methods is standard when creating a 1 to many database relationship?

If I have a customer that can have many addresses, I can create an Address table with columns Street, Town etc. and CustomerId. Then I can insert multiple records to have multiple addresses per ...
1
vote
1answer
66 views

Redundant transpose. Case is not enough to solve my problem

I have db problem in transposing rows to columns. I am half way through the result but getting redundant data. My table: EMP_ID EMP_NAME SAL_PAID 01 ABC JAN 01 ABC FEB 01 ...
2
votes
4answers
14k views

Query to list all tables in a schema

I am using Rational Application Developer to run querys on a database. We are unable to locate anyone who has a list of the tables on the schema and so far the queries we have found to get a list of ...
1
vote
0answers
310 views

SET IDENTITY_INSERT equivalent in Access

In sql server i can explicitly insert value to a identity column of a table by using SET IDENTITY_INSERT ON . For an Access database i am using Autonumber column datatype. For this column i am not ...
0
votes
2answers
73 views

Difference between the given queries

I am required to retrieve the list of pno ( product_id ) for the products which were purchased by two customers. For this, I have been given two relations which are ( along with the attributes ) as ...
2
votes
2answers
2k views

SQL Server 2012 Availability Group issue

I need some assistance and some bandages for shooting myself in the foot. This is a DEV environment but I need to bring it up. I am using SQL Server 2012 Enterprise on Windows 2008 R2. Somehow our ...
1
vote
1answer
5k views

Passing column names dynamically to UNPIVOT

I have a table with following data First Second Third Fourth Fifth Sixth 2013-08-20 2013-08-21 2013-08-22 2013-08-23 2013-08-24 2013-08-25 And ...
2
votes
1answer
702 views

Distributed - across multiple databases or servers - Transactions

I have some doubts related to transactions in SQL. I perform below steps. Connection 1 open for DB1 database Begin transaction for Connection 1 Run the commands on Connection 1 Connection 2 open for ...
-1
votes
1answer
101 views

Problems working with decode function when there is more then one expression

I am using Oracle 11g. I have these two tables: Table acct: acctnbr || Name || Mailing address 000001 || Slater || 000002 || Baker || Alt 000003 || Parke || Table addruse: ...
0
votes
3answers
625 views

Dividing in SQL

can anyone explain to me why this query returns 0.000? SELECT CAST(20/1024 AS NUMERIC(10,3)) I am just trying to do a simple conversion of MB to GB Thanks
1
vote
1answer
62 views

SQL SERVER 2012 error [closed]

am trying to restore a backup, the backup is from a database in the current instance, want to restore the backup with a different name for test purposes, this was possible in 2008 R2 but in 2012 its ...
0
votes
0answers
99 views

MySQL Access Denied, tried a few things, pulling hair

I'm trying to get to know Django (my first attempts at a framework, or any backend work for that matter), and I'm seriously stumped by MySQL, and SQL in general. I'm trying to create a new database ...
0
votes
3answers
1k views

Setting up SQL Server service broker from one database to multiple databases

I would like to set a SQL broker service whereby DatabaseA sends the same data to DatabaseB and DatabaseC. In this case, all databases reside on the same instance. I have looked at the following ...
2
votes
1answer
486 views

Grouping over multiple data dimensions (pivot)

I am logging an Events table, and want an output (as seen at the bottom of this answer), the issue is that I am getting the data in rows, but want them to be displayed column-wise. The two dimensions ...
0
votes
2answers
794 views

copy package from schema B to schema C

I am in the next situation: I am using oracle 11g. I am connected to an user, lets say schema1, where are a lot of permissions. I want to create a script which will copy the Package1 (and its body) ...
0
votes
2answers
102 views

Understanding multiple indexes with MySQL (and performance)

I have a "phones" table with around 120 000 records, it contains phone_id, phone_number, phone_user_name, phone_last_contacted, etc. The phone_id is index and looks like: 1,2,3,4 ... 120 000. But I ...
0
votes
1answer
50 views

Query Optimization for added value

I have a query that's behaving a bit oddly. In my database I have a table called "records". It tells me a bunch of information about what applications a user ran on my company's machines. I'm ...
1
vote
1answer
154 views

Suppress repeated performance condition alerts

I have set SQL Server performance condition alert with details as follows: Object : SQL Server:Databases Counter : Percent Log Used Instance : My database name Alert If ...
0
votes
3answers
3k views

Can I give the same name to a table and a stored procedure?

Is it possible to name a stored procedure the same as a table? I have an existing table named 'buyers', and I'm trying to create a stored procedure named 'buyers', but I'm getting this error: ...
-1
votes
2answers
123 views

How do I avoid duplicate rows using FREETEXT?

I have the following query: SELECT cat.CategoryId,cat.CategoryName,cat.CategoryImageURL,cat.CategoryImageData, ap.AppName,ap.AppImageData,ap.AppDetails,ap.AppId FROM Category cat,App ap WHERE ...
2
votes
2answers
609 views

How to create Combination of records (Order does not matter, no repetition allowed) in mySQL tables

I've got a table that has hundreds of rows, each row is a recipe with nutritional information, for example: recipe_table: id | calories | protein| carbs | fat recipe1, 100, 20g, 10g, 2g ...
-1
votes
1answer
2k views

how to export postgresql database table into mysql

I am trying to get data from a Postgres database, but I am totally new to using Postgres. I am here to ask my question to get some ideas from the best answer on how can I can do this. If possible, I'd ...
0
votes
2answers
731 views

How to index for a query with independent range conditions?

What is the best way to index a table that I query using independent range conditions? E.g. WHERE X < ? and Y > ?
6
votes
2answers
370 views

Should I add a column or 1-1 table if property is usualy null

There's a new unique property we need to add, which is relevant only to a specific subset (less than 1%). We thought it's better to add a new table which will be 1-1, than to add a new column which ...
0
votes
1answer
59 views

strange “using where” in simple query

I have a table with this structure: mysql> describe user_groups; +----------+----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | ...
4
votes
2answers
590 views

Open source and commercial tools to build dynamic queries of fact tables in star schema [closed]

What user interfaces or tools (commercial and/or open source) can query a star schema model (Ralph Kimball dimensional model)? So for example, if we have X fact tables, and Y dimension tables, and Z ...