Questions tagged [query]

Writing queries to request or change information stored in a database or other information management system. Questions should include table and index definitions, sample data, expected output, and a tag for the specific system (e.g. MySQL, PostgreSQL, Oracle, SQL Server, MongoDB).

Filter by
Sorted by
Tagged with
22 votes
1 answer
15k 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. ...
  • 1,015
40 votes
3 answers
4k 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 ) ...
20 votes
7 answers
11k views

Efficiently select beginning and end of multiple contiguous ranges in Postgresql query

I've got about a billion rows of data in a table with a name and an integer in the range 1-288. For a given name, every int is unique, and not every possible integer in the range is present--so there ...
  • 423
5 votes
2 answers
3k views

Concurrently update with limit mysql

I want to know if concurrently UPDATE .... LIMIT N will not overlap with more than one client querying the db. After UPDATE ... LIMIT N the client will make a SELECT with some client_id assigned. I ...
  • 153
4 votes
3 answers
2k views

MySQL very slow query when changing one WHERE field despite no index/key

This is quite a confusing issue for me. I have a database full of baseball statistics. Running this query: SELECT * FROM hits JOIN stadiums ON stadiums.gameName = hits.gameName JOIN players ON (...
  • 143
41 votes
5 answers
280k views

How to select distinct for one column and any in another column?

I need to query an SQL database to find all distinct values of one column and I need an arbitrary value from another column. For example, consider the following table with two columns, key and value: ...
  • 521
14 votes
4 answers
170k 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 ...
  • 2,043
15 votes
3 answers
23k views

Notification on long-running query or deadlock in SQL Server 2008 R2?

I'd like to know if there is a way to send a notification on deadlock? If so what queries would be required. I understand that SQL Server takes care of deadlocks, I simply would like information on ...
  • 253
6 votes
4 answers
7k 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 ...
6 votes
1 answer
1k 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 ...
  • 6,572
2 votes
2 answers
1k views

How to add a day/night indicator to a timestamp column?

There is a timestamp column I use to indicate whether a row was created during day or night time. My code is the following, but for some reason I only get 'DAY' as outcome. Am I not formatting the ...
  • 33
4 votes
2 answers
3k views

How to fetch the columns of the synonyms for linked servers in SQL server

I am using two servers local (SQL Server running on my system) and remote (SQL Server running on another system). I have created synonyms in my local server for a table which is located on the remote ...
2 votes
1 answer
15k views

Create Trigger that updates the same table before (or after ??) the Insert

I have just started using triggers and one thing that I would need to do is to create a trigger that updates the same table that's inserting the row. TABLE: CREATE TABLE `city` ( `city_id` ...
  • 23
0 votes
2 answers
57 views

Limit and offset (pagination) for rows with related data (with joins)

I have tables Alpha and Beta. Beta belongs to Alpha. create table Alpha ( id int auto_increment primary key ); create table Beta ( id int auto_increment primary key, alphaId ...
0 votes
1 answer
1k views

How to query Last inserted row for each group in oracle 10

I have two tables about users where I want to join two tables and show the latest remarks of each user bellow is table I want to query the latest remarks for each user (1 remark per user) bellow ...
0 votes
2 answers
1k views

How do I find out whenever a value changes in a certain column?

Need help if anyone can offer! I currently have my select statement returning the TOP 1000 * results from a table. They are CC numbers that are currently active. I'd like to see how I can go about ...
13 votes
1 answer
38k views

Query for all the Postgres configuration parameters‘ current values?

You can set various configuration parameters for Postgres by either editing the postgresql.conf file manually or by calling ALTER SYSTEM commands. These are two avenues for writing the settings, but ...
15 votes
3 answers
39k views

Grouping records based on intervals of time

I have a table with the following schema, and I need to define a query that can group data based on intervals of time (Ex. records per minute) and then provide the sum of the changes to the ...
  • 1,257
3 votes
3 answers
5k views

Is it normal to use many Triggers?

I have many so-called summary columns throughout my database to count number of rows in other tables. For example, counting the number of comments by a user (a column in the user table). Currently, I ...
  • 4,281
12 votes
3 answers
2k views

Query challenge: Creating even sized buckets, based on a measure not row count

I'll describe the problem in terms of loading a fixed number of trucks with orders, as evenly as possible. Inputs: @TruckCount - the number of empty trucks to fill A set: OrderId, OrderDetailId, ...
6 votes
3 answers
4k views

Select all overlapping ranges from starting range

My question is similar to this one with (I think) significant enough difference. I have a base range and I want to find all other ranges in a table that conflict with it and each other. or rather the ...
41 votes
3 answers
47k views

Is it a good idea/approach to index a VARCHAR column?

We're using PostgreSQL v8.2.3. There are tables involved: EMPLOYEE and EMAILLIST. Table 1: EMPLOYEE (column1, column2, email1, email2, column5, column6) Table 2: EMAILLIST (email) 2 tables are ...
  • 957
7 votes
2 answers
5k views

Find the largest databases on my server

I have several hundred databases running on the same MySQL server. How can I get a list sorted by the size of the databases?
  • 161
6 votes
5 answers
8k views

SQL Pair Query?

I have only one table which contains 3 columns for travel company. It shows buses that went from City A to City B etc. I would like to find how many times this route has been used. I can easily find ...
3 votes
3 answers
5k views

Calculating duration between two events in a MySQL database (timeseries)

I have a (timeseries like) table in MySql which stores some on/off events. These events are reported at random intervals and represent the current state of an IoT switch: I need help building the SQL ...
  • 201
2 votes
0 answers
542 views

AWS Aurora queries much slower in "sending data" phase than RDS instance

I've been using an RDS instance during the development phase of a project. This was a db.t2.micro instance, so not very powerful at all. Today I have created a new Aurora (5.6) [serverless] database ...
11 votes
4 answers
20k views

Checking if 2 tables ( on different servers ) have the same exact data

So it seems that the company who is hosting our SQL Servers has been having some trouble with the replication from the production server to the backup server... I believe some of the tables have been ...
  • 3,185
7 votes
1 answer
627 views

Dealing with temp tables when I have not control over db variables

I have no control over things like tmp_table_size and max_heap_table_size, and so as our tables grow the time taken by queries requiring temp tables is growing geometrically. I am wondering if there ...
  • 859
5 votes
2 answers
261 views

Query Plan question

I've created a varbinary hash to check changes between 2 tables. Here is the execution plan, I'm a bit stumped on the indexing, or indeed if there is a better way of writing this. https://www....
4 votes
3 answers
10k views

Stored procedure executes too long

We use SQL Server 2008. When I am watching in SQL Server Profiler I found some queries that take for example 20 seconds. The same query (in a stored procedure) when executed in SQL Server Management ...
's user avatar
3 votes
1 answer
616 views

SQL Server 08: Union over while

I've done some reading on this, and I keep seeing people say it's a bad idea, but hear me out! I have a stored procedure which needs to find a series of timeslots over a number of days. Due to the ...
  • 203
3 votes
1 answer
10k views

How to use ON DUPLICATE KEY for UPDATE

Consider a table of CREATE TABLE test ( id int(11) unsigned NOT NULL AUTO_INCREMENT, external_id int(11), number smallint(5), value varchar(255), UNIQUE INDEX (external_id, number), PRIMARY KEY(id) )...
  • 4,281
2 votes
1 answer
5k views

How to get last and first occurrence of items into separated columns?

I do have the fallowing table: id name value 1 Daniel 3 2 Daniel 7 3 Daniel 2 4 Carol 9 5 Carol 4 6 Carol 9 7 Ray 5 8 Charles 1 I would like to get the first and the ...
1 vote
2 answers
370 views

The difference between filtering a specific column in 'On Clause ' of a join statement and 'Where clause'

I have two tables with these structures: Table1(Date ,Code ,Name) Table2(Date ,Code) I need to filter a specific date for both tables. For instance I need data only for '10/28/2019' from each table....
  • 1,356
1 vote
1 answer
956 views

Merge 2 tables and fetch data one by one

I have two tables: product & offers. products has (prod_id, category_id, prod_name, brand, price, status) offers has (offer_id, prod_id, offer_type, discount_amt, validity) I want to display ...
  • 13
0 votes
2 answers
7k views

Performing query based on another query's result

First I have to run the query (1) using the pagedetailid number to get the adblockid. Then I have to put that adblockid into the other query (2) to get the result. What I would prefer is a single ...
8 votes
6 answers
13k views

How do you submit a List of items to a Stored Procedure to limit results? [duplicate]

Possible Duplicate: Passing array parameters to a stored procedure Lets say you want to filter results by a List of uniqueidentifiers such that your SQL statement would read: WHERE CustomerID in ...
  • 211
7 votes
2 answers
2k views

Using totals on aggregates to improve performance

I have two tables: details and totals of these details. Details (Slow solution): select OrderId = r.OrderId , TotalQty = SUM(r.Quantity) , ...
  • 6,572
6 votes
3 answers
531 views

How can I calculate all grouping permutations of an input string in SQL?

Given an input like "ABC" generate a query that calculates all potential splits of 0 or more of the given string, Desired output, A B C A BC AB C ABC Given an input like "ABCD&...
  • 57.3k
5 votes
1 answer
902 views

Query Execution Time

I am querying a SQL server 2008 database table having more than 500 million records. The query I fired to move around 10 million records to temp table took 9 hours. Similar query I fired which has ...
's user avatar
4 votes
2 answers
575 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 other ...
4 votes
2 answers
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 ...
  • 609
4 votes
2 answers
6k views

How to retrieve the maximum value and its corresponding date in a table

I'm trying to figure how to retrieve minimum/maximum values and minimum/maximum dates from a data set, but also the date value that corresponds to each minimum/maximum value. Example Data CREATE TABLE ...
4 votes
3 answers
5k 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. If ...
  • 365
3 votes
1 answer
501 views

Mysql get records more then 3 in interval of 1 minute, return group_concat ID

Currently i have this dataset, i need to return grouped ids that are within the range of 60 seconds and have more than 3. CREATE TABLE test ( `id` bigint NOT NULL AUTO_INCREMENT, created_date ...
  • 239
3 votes
2 answers
3k 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, ...
  • 391
3 votes
2 answers
8k views

How to print results in SQLcmd window

I'tm trying to show in SQLcmd window the results of these queries: SELECT SYSDATETIME() AS "date"; PRINT 'EXTRAÍDO POR:' SELECT SUSER_NAME() AS "Owner" PRINT 'PASSWORD SETTINGS'; exec ...
  • 33
3 votes
2 answers
260 views

Validation of availability for a new order

I have a management application of sales, stock and payment on a warehouse whole saler from a web interface. In particular, when a order is effectuated it must create a line corresponding to each ...
3 votes
5 answers
3k views

Single query to return counts over different IDs in a single record

select count(title_id)as algodata from titles where pub_id =1389 select count(title_id)as binnet from titles where pub_id =0877 select count(title_id)as newmoon from titles where pub_id =0736 ...
  • 2,035
3 votes
1 answer
2k 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 | | ...
  • 159