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)

3
votes
2answers
326 views

SQL Data aggregation

I have a problem with sql data aggregation problem.here is the sample sql.I am using oracle database. select pm.city cityid, to_char(pm.sampledate, 'yyyy-MM') timepoint, count(distinct me.id) ...
2
votes
6answers
529 views

Need separate columns that calculate percentage

I have the following table: NAME Alex Bob Bob Tim Alex Roger I need to produce the following: Name Count Percentage Alex 2 33% Bob 2 33% Roger 1 16.6% Tim 1 ...
1
vote
2answers
783 views

MySQL query log

I wonder if there is an alternative for MySQL general query log. I would like to log (mostly) DDL statements, so I could trace what user DROP database/table etc. I know that enabling general query log ...
4
votes
3answers
3k views

Change column type from varchar to text in all tables at once

I am using Postgres. I would like to change column type in all tables where column name is "description" from varchar(255) to text. If anyone knows that I would be very glad for your help.
4
votes
1answer
3k views

How to remove known elements from a JSON[] array in PostgreSQL?

I'm facing an issue regarding using the JSON data type in PostgreSQL. I try to achieve storing a Java model denormalized in the DB. The model features lists of complex objects. Thus, I decided to ...
4
votes
1answer
473 views

Order by distance

If I have a query returning nearby cafes: SELECT * FROM cafes c WHERE ( ST_DWithin( ST_GeographyFromText( 'SRID=4326;POINT(' || c.longitude || ' ' || c.latitude || ')' ), ...
4
votes
1answer
266 views

What is the name of this type of query, and what is an efficient example?

The purpose is to find a parent, given it's children. For example, say you have a marketing package (aka a "combo"), and want to match it based on the products in it. Example table/data: create table ...
4
votes
3answers
3k views

NULLs in a composite primary key - SQL Server

I'm trying to work out the best way of creating SQL Server primary keys, foreign keys and constraints to accurately represent my data model in LINQ / Entity Data Objects. Let's assume - for the ...
4
votes
1answer
785 views

How to get text for default language

I have so tables: and so data at Language table: and so data at Text table: I have to return text for requested language if it exists and text for default language if it does not exist. Is it ...
3
votes
4answers
12k views

Recursive self joins

I have a comments table, which can be simplified down to this: comments ======= id user_id text parent_id where parent_id is nullable, but might be a key for its parent comment. Now, how can I ...
3
votes
2answers
1k 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 ...
2
votes
4answers
129 views

Make IN clause behave like AND clause

I've asked the same question on stackoverlow, but got no answer, so that's why I'm asking it here (I've read that simple sql questions should be asked at SO, but I have no choice). Suppose I have 3 ...
2
votes
2answers
3k views

PostgreSQL : select columns inside json_agg

If I have a query like SELECT a.id, a.name, json_agg(b.*) as "item" FROM a JOIN b ON b.item_id = a.id GROUP BY a.id, a.name; How can I select the columns in b so I don't have b.item_id in the ...
2
votes
2answers
420 views

Optimize a query on two big tables

I have a very important query in my system that is taking too long to execute due to huge amount of data on tables. I'm a junior DBA and i need the best optimization I can get for this. Tables have ...
2
votes
2answers
2k views

How can I select rows from a hierarchical query with the lowest level?

I have a hierarchical query in Oracle 11gR2 that returns something like this: Parent (Level 1) Child (Level 2) Grandchild (Level 3) Child (Level 2) Grandchild (Level 3) Grandchild (Level 3) ...
2
votes
1answer
186 views

Search within same ID across several rows

My table has the following structure: +----+---------+------------+-------------+ | id | word_id | meaning_id | sentence_id | +----+---------+------------+-------------+ | 1 | 1 | 15333 | ...
1
vote
2answers
397 views

Return counts for multiple ranges in a single SELECT statement

I have a Postgres database table foo that, among other things, has a column for score that ranges from 0 - 10. I want a query to return the total number of scores, the number of scores between 0 and ...
1
vote
1answer
3k views

Get top most parent by nth child id?

Now there is a question we commonly use this technique to maintain the parent child relation i.e we store all the entities in one tables with a parent_id column and all top most parents have 0 in the ...
20
votes
3answers
8k views

Running total with count?

As the title suggests I need some help getting a running total in T-SQL. The problem is that the sum I need to do is the sum of a count: sum(count (distinct (customers))) Say if I ran the count ...
18
votes
2answers
764 views

SQL INDEX - how it works?

My knowledge of databases and SQL is based in most on university classes. Anyhow, I spent few monts (almost a year) in a company, where I was working with databases. I have read few books and I have ...
24
votes
1answer
6k views

Why is using a table variable more than twice as fast as a #temp table in this specific case?

I was looking at the article here Temporary Tables vs. Table Variables and Their Effect on SQL Server Performance and on SQL Server 2008 was able to reproduce similar results to those shown there for ...
22
votes
2answers
3k views

Why does query error with empty result set in SQL Server 2012?

When running the following queries in MS SQL Server 2012 the second query fails but not the first. Also, when run without the where clauses both queries will fail. I am at a loss why either would fail ...
7
votes
2answers
52k views

How do you show SQL executing on an Oracle database?

How can you show the SQL that is currently executing on an oracle db? Extra information that would be useful would include user, session id etc.
16
votes
1answer
7k views

GO After every T-SQL statement

What is the reasoning behind using the GO statement after every SQL statement? I understand that GO signals the end of batch and/or allows the reputation of statements but what advantage does it have ...
13
votes
3answers
375 views

What is the correct result for this query?

I came across this puzzle in the comments here CREATE TABLE r (b INT); SELECT 1 FROM r HAVING 1=1; SQL Server and PostgreSQL return 1 row. MySQL and Oracle return zero rows. Which is correct? ...
12
votes
1answer
3k views

Why does NOT IN with a set containing NULL always return FALSE/NULL?

I had a query (for Postgres and Informix) with a NOT IN clause containing a subquery that in some cases returned NULL values, causing that clause (and the entire query) to fail to return anything. ...
6
votes
2answers
10k views

Learning to Optimize SQL Queries and Understand Execution Plans - Resources?

I find myself writing more and more SQL queries at work (mostly Oracle 11g, but some SQL Server 2005-2008) and have started creating some pretty complex views for the rest of the analyst team. They ...
6
votes
3answers
3k views

SQL Server Primary key / clustered index design decision

Looking for some advice regarding a table / index design decision I've got to make on some tables that I've got to port into SQL server from an existing 4GL based database. I've got a product history ...
5
votes
2answers
6k views

Prevent copying to temp table (sql)

I have two tables - comments and votes : comments(id, text, user_id, page_id) votes(id, value) There are 2,000,000 rows in the votes table. I created the following query: SELECT ...
5
votes
4answers
4k views

MySQL: Index when joining to tables not being used (Performance optimizing question)

I need to optimize the following query: SELECT /* [things omitted] */ articles.blogpost_id, articles.id AS articleid FROM blogposts JOIN articles ON articles.blogpost_id = blogposts.id WHERE ...
2
votes
2answers
3k views

MySQL Replication without stopping master

I am currently setting up a replication for my database one of the ways i am thinking of synchronizing the master and slave without write locking the master, is by first taking a log position then ...
8
votes
5answers
3k views

Why is the SELECT clause listed first? [closed]

I'm not sure if this question is on-topic - if not, feel free to close. Also, if it'd be a better question for another StackExchange site, I can ask it elsewhere. I was wondering why SQL places the ...
5
votes
1answer
2k views

Allow non-sysadmin, non-owner of a SQL Server Agent job to execute it

I have a job that runs an SSIS package. Currently the owner is a proxy account. I can run the job manually from a sys-admin account. Our web service logs in using a limited account. It needs to ...
5
votes
3answers
10k views

How to model inheritance of two tables mysql

I have some tables where I store data and depending on the type of person (worker,civil) that did a job I want to store it a table 'event', now these guys rescue an animal (thereis a table animal). ...
5
votes
4answers
12k views

Building Dynamic Oracle Where Clause

I'm working on application that uses dynamic query to do a select statement based on user input, after discussing security with DBAs they want me to convert my dynamic select statement into Stored ...
14
votes
3answers
10k views

How does shrinking a SQL Server log file affect performance?

I have a SQL Server 2008 database that has a data file of some 2GB in size, but the log file is over 8GB. With pre-2008 databases I could use the 'Backup log' and the TRUNCATE_ONLY option but this is ...
12
votes
4answers
4k views

Is it possible to increase query performance on a narrow table with millions of rows?

I have a query that is currently taking an average of 2500ms to complete. My table is very narrow, but there are 44 million rows. What options do I have to improve performance, or is this as good as ...
9
votes
4answers
11k views

Can I add a unique constraint that ignores existing violations?

I have a table which currently has duplicate values in a column. I cannot remove these erroneous duplicates but I would like to prevent additional non-unique values from being added. Can I create a ...
8
votes
3answers
3k views

How can I improve my SQL statement with weekly results with week starting on Thursday or any other day of the week?

I am a total newb, and I couldn't find a good way to do this anywhere. I have a database table that contains statistics that are recorded at various time throughout the week. The reporting week ...
7
votes
2answers
4k views

What are the differences between SQL I/O Sim and SQL IO tools?

I want to test my different SQL Server alternatives with a benchmark tool. I found two tools by Microsoft: SQLIO Disk Subsystem Benchmark Tool SQL IO Sim What are the differences between them? is ...
5
votes
2answers
3k views

PostgreSQL query with max and min date plus associated id per row

I have the following table: CREATE TABLE trans ( id SERIAL PRIMARY KEY, trans_date date, trans_time time ); I want to have the following view CREATE OR REPLACE VIEW daily_trans ...
5
votes
3answers
2k views

MySQL Query Optimization : Indexing and Pagination

I'm adding pagination to my 'latest news' php script and running in to an issue. SELECT sid, title, time, bodytext, author, url FROM news WHERE approved=1 ORDER BY time desc LIMIT $start, $limit I ...
4
votes
1answer
408 views

Pull data from multiple tables in a view or function

There are 3 tables from which I need to pull data, and print it in a specific way. http://sqlfiddle.com/#!15/59481/8 Refer to this question to see how the query was produced. The version in the ...
3
votes
2answers
4k views

Database design for an E-commerce website

I am new to database design. I am designing a database for an E-commerce website, there are a lot of products to be updated, but while designing for product specification table I do not understand ...
2
votes
3answers
2k views

SQL Subquery , One to Multiple rows

Following query I have written returns the result as expected, example 121350 rows. SELECT dbo.Articles_in_Consignment.Consignment_id, dbo.ORDER_D.Orders_boxsize, ...
8
votes
6answers
5k views

Find “n” consecutive free numbers from table

I have some table with numbers like this (status is either FREE or ASSIGNED) id_set number status ----------------------- 1 000001 ASSIGNED 1 000002 FREE 1 000003 ...
8
votes
3answers
4k views

Oracle: How do I query a Hierarchical table?

Background This is for the construction of some views we'll be using for reporting. I have an table of locations, the key fields being "location" and "parent". The structure that these two fields ...
8
votes
5answers
3k views

Query Running Differently on SQL 2005 vs SQL 2008R2

At my office, we have a query that is pretty ugly, but runs pretty well in production and in the development environment (20sec, and 4sec respectively). However in our testing environment it takes ...
7
votes
1answer
71 views

Combine two event tables into a single timeline

Given two tables: CREATE TABLE foo (ts timestamp, foo text); CREATE TABLE bar (ts timestamp, bar text); I wish to write a query that returns values for ts, foo, and bar that represents a unified ...
6
votes
4answers
5k views

Passing in the table into a stored procedure

Is it possible to pass in the name of a table into a stored procedure? For example, suppose you have several views of the same table. They all have the exact same structure. You want a store ...