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

learn more… | top users | synonyms (2)

4
votes
2answers
7k views

Migrate all databases from one SQL Server 2005 to another

We have a production SQL Server 2005 server on which we are hosting 200 databases. We want to host these databases on our new server. On new server SQL Server 2005 is installed and is ready. I want ...
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 ...
4
votes
2answers
1k views

A query submitted from different applications has differing DOP

I have a query which selects from multiple views and is fairly I/O heavy. If I execute this query using Management Studio it uses parallelism across most of the 16 CPUs and completes in under 10 ...
4
votes
5answers
7k 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
4
votes
2answers
220 views

Checking a wide table for nulls [duplicate]

Possible Duplicate: Test if any fields are NULL I am new here. I have a table with 70-odd columns. Is there a way I can check the number of rows with at least one null value in any column ...
4
votes
1answer
337 views

Grouping() equivalent in PostgreSQL?

I have a SQL query that runs in MS SQL Server and it has grouping(), rank() and rollup() functions / keywords in it. The query is given below. How can this be translated to PostgreSQL? select top ...
3
votes
1answer
106 views

Determining what query was called

I want to reverse engineer what exactly happens to the MySQL database when a product is added in Magento. I will have to reproduce the query, because I would like to manually enter new records using a ...
3
votes
2answers
2k views

Getting the number of Saturdays and Sundays in a month

I am trying to get the number of Saturdays and Sundays for a given month represented by an end_date and a start date. I have been trying to understand the algorithm in the SO question here . I have ...
3
votes
3answers
554 views

SQL JOIN Syntax in MS SQL

I have been taught in my MSSQL classes, this is how to join two tables select * from FirstTable A JOIN SecondTable B on A.ID= B.ID Now in my professional life, I came across JOIN queries like ...
3
votes
3answers
275 views

Join table on some conditions

I've got a table called activities. And a table messages. Activity has many messages, and one message has one activity. This are the message columns : id sender_id receiver_id body activity_id I'm ...
3
votes
1answer
1k views

Does existing msrepl_tran_version column need to be removed when re-creating replication?

What is the process for removing a replication and then re-creating the replication. I know the process to set up replication. However, If i wanted to take a publisher database copy it and restore it ...
3
votes
1answer
333 views

Single year column and multiple weeks columns into single year/week column with PIVOT

I have a table that looks roughly like this. (Pk int, Year int, Week1, Week2, .., Week53) The values in each "Week" column are sales data. What I want to do is create a new table from the first ...
3
votes
2answers
9k views

GROUP BY two columns

I want to count two columns, but need to get the results of first column in one row. SELECT country, COUNT(*) FROM table1 GROUP BY country, type This query gives me country type COUNT(*) ...
3
votes
2answers
3k views

How do I group results in batches of consecutive rows with the same 'module'

I'm looking for a seemingly very simple SELECT. I have a logging table LOGGING of which three columns are of interest to me: MODULE (text), PDATE (date), PTEXT (text). Actually, I care only about ...
3
votes
1answer
4k views

Select the top 5 records and the specific column from each table in the same database

I have 50+ tables in a database. I don't want to use the following SQL on each table in a database. select top 10 * from {table_name} So, is it possible to display the first 10 records PLUS only ...
3
votes
2answers
13k views

ORDER BY clause is allowed over column that is not in SELECT list?

In theory, every statement in SQL Server create virtual table passed to next statement. In other words, when FROM and Where clause are finished, next step is SELECt columns from virtual group created ...
3
votes
1answer
579 views

How to categorize rows by SUM of a column in mysql?

I have a table as id num_items folder 1 4 2 33 3 74 4 41 5 24 6 34 7 46 8 55 9 11 I want to assign folder number to rows but limiting the number of items in each folder (e.g. ...
3
votes
1answer
353 views

Creative workaround for granting edit rights to individual jobs for a group?

We have two developers that say they can not function without full rights to a few jobs on our database server. They both want rights to the same jobs. Given that it seems that MS doesn't seem to be ...
3
votes
1answer
4k views

Very slow simple PostgreSQL query on RDS

I seem to be getting very slow queries on a medium sized RDS box (db.m3.medium, 3.7gb ram). This is across a table of 4,152,928 rows.. select sum(some_field) c from pages where pages.some_id=123 and ...
3
votes
2answers
247 views

Filter and append data to row

My query and my data set (copied from sqlfiddle): CREATE TABLE one ( id int4 primary key, p_id int4, k_id int4 ); CREATE TABLE two( id int4 primary key, p_id int4, k_id int4, ...
3
votes
3answers
3k views

Speed up search across multiple columns

We have a table: user_info (user_id,domain_id,first_name,middle_name,last_name,work_place,.,.,.) Now, we have a user search that search across three columns first_name,last_name and work_place. ...
3
votes
1answer
1k 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 ...
3
votes
4answers
2k views

How to retrieve closest value based on look-up table?

I'm attempting to create a query that will find the closest value from one table and return its ID into the resulting table. Below is an example that should describe the situation better. Sample ...
3
votes
1answer
1k views

Is there any way to get the estimate cost of executing a query in MySQL?

In PostgreSQL, EXPLAIN or EXPLAIN ANALYZE will show the estimate cost of executing a query. But EXPLAIN in MySQL doesn't provide this information. How can I get the estimate cost without installation ...
3
votes
5answers
10k views

Copying Oracle 11g express Database from one server to another

Basically, I am a MS-SQL developer,and relatively new to Oracle. Even after great deal of research, I was unable to find any way of copying entire database from one server instance to another. In ...
3
votes
1answer
446 views

SELECT COUNT(*) to html table

This may be a very simple but it's still not clear to me. I'm writing a SQL Agent job to generate output in html format. I need to get results of a query SELECT COUNT(*) FROM TABLE_NAME. I'm able to ...
3
votes
3answers
143 views

Sum of points and rank according to points

I am creating a game where I am calculating the user points on the basis of user activities. Everything is working fine - I got the sum of points and on the basis of points I calculated the rank. I ...
3
votes
1answer
73 views

How to get 0 as array_length() result when there are no elements

I have a query like this: select id, array_length(users_who_like_ids,1) as ct from queryables order by 2 desc; But empty arrays (with no elements) sort on top. I'd rather have array_length() return ...
3
votes
3answers
490 views

Script to drop all databases except one not working

I'm trying to drop all databases but one on my test server. For this, I'm using the below script but it gives error: EXEC sp_MSforeachdb ' USE [?] IF (DB_ID(''?'') > 4 AND DB_NAME()!=''ABC'') ...
3
votes
2answers
1k views

Testing Query Speed

When I'm running a query for the first time, it takes x miliseconds to be executed. When I run it the second time it takes a lot less time. I assume that it is some caching involved in this mechanism, ...
3
votes
1answer
9k views

Efficient way to insert/update/delete table records from complex query in Postgres 9.x

I have this function which returns a set a records and I need to persist those records into a table. I have to do it hundred times a day. My initial approach was just clear data from my table and re-...
3
votes
1answer
211 views

How to create function(table_name) that returns a set of the table

I have a set of tables that share the same fields (some timestamps and users names used for version control). I need to create a function with dynamic sql that uses a table_name and a timestamp as ...
3
votes
2answers
2k views

How can I create different sets of sequential values based on another column in a table?

I've got a table that holds two types of notes. Collection Notes and Delivery Notes. They are identical data structures, hence using the same table. CREATE TABLE Notes ( Id int IDENTITY(1,1) NOT ...
3
votes
1answer
91 views

How to show rows with 0 without any records in database

I have a lot of record in my database and for data analysis, I need to show count of records for each day including days when I do not have any record, so result is 0. For example I have these ...
3
votes
1answer
6k views

DB2 Defining remote database alias

I'm using DB2 9.7.0.3 for AIX. I want to select data from a remote database, also running DB2 9.7.0.3 for AIX, and insert it into the local database I'm connected to. I believe the query for this ...
3
votes
2answers
719 views

Transaction Log 16GB on 2MB database [duplicate]

Possible Duplicate: Why is My SQL Server Transaction Log Growing or Running out of Space? The transaction log in one of my client's databases is 16GB, when the database itself is only 2MB. I ...
3
votes
3answers
171 views

Choosing a PK: VARCHAR(2) or SMALLINT(2)?

Today we were discussing the following: Brazil has 27 states and each one has its own abbreviation (just like USA). So we've got RJ for Rio de Janeiro, SP for São Paulo, MG for Minas Gerais and so on....
3
votes
1answer
2k views

Restore a Database into a Current Database and merge

I want to know how, or if it is possible, to backup a database then should the user wish, restore that database at a later date and merge it with the one they are currently using. Both Databases will ...
3
votes
2answers
4k views

Can the sorting from an ORDER BY statement be saved explicitly with only one UPDATE statement?

I have this table: SELECT * FROM items id | item | position ---|-----------|---------- 1 | USB cable | 0 2 | SD card | 4 3 | Mouse | 2 4 | Keyboard | 0 5 ...
2
votes
1answer
193 views

Is it ok to let users add new columns and tables to your database?

I'm basically making a database of databases. An administrative User can go in and hit "Create Database" (which is basically a table) and set up the column types and how many columns. Other users can ...
2
votes
1answer
59 views

Highest value of a row in MySQL

I searched and found many results using "Grep highest value of a row in MySQL" but I don't understand the results. I have a row which primary key value is 4 and there are another 8 values. Those are ...
2
votes
2answers
132 views

How to get data from three different tables

This question is continuing of this issue: How to get data from two different tables? Now I need to connect the table Sessions with 2 tables: Input Downloads When I use this query with real ...
2
votes
1answer
836 views

counter table in mysql

i was going through the book: High performance MySql 2nd edition. In chapter 4 it says: An application that keeps counts in a table can run into concurrency problems when updating the counters. ...
2
votes
1answer
40 views

Relating 2 tables with possible wildcards?

I am having trouble getting the desired results when trying to relate the following tables. NULL in the following table essentially means that if it is not an exact match with another row then choose ...
2
votes
2answers
4k views

Optimizing removal of duplicate rows from recursive CTE query

I need to write a recursive SQL query to fetch multiple projects together with their lineage from the following table: CREATE TABLE #Project ( Name VARCHAR(10), ParentName VARCHAR(10) ) INSERT INTO #...
2
votes
1answer
38 views

OFFSET and LIMIT on complex query

I am doing a pretty complex query in Postgres 9.3.4: SELECT p.* FROM unnest('{19082, 19075, 20705, 18328, 19110, 24965, 18329, 27600 , 17804, 20717, 27598, 27599}'::int[]) s(source_id)...
2
votes
1answer
1k views

Find which numbers in [1, 161] are not in the result set?

How to find which two number in [1, 161] is not in the result set? Can I find this two numbers using sql command? mysql> select blog_id from wp_blogs; ... | 149 | | 150 | | 151 | | ...
2
votes
1answer
75 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
566 views

Difference data between adjacent rows

I have a table (called Visits) with ReqTime DATETIME NOT NULL column AND Duration INT UNSIGNED column. Duration should be set to the difference of ReqTime for two adjacent rows. But Duration is not ...
2
votes
0answers
80 views

Modulus calculation for compound primary keys in MySQL Cluster (and other sharded databases)

In MySQL Cluster, and generally in sharded databases, how is a compound primary key's modulus calculated? Say, for example, there are two tables that have primary keys like so: CREATE TABLE Orders (...