Database Administrators Stack Exchange
Database Administrators Stack Exchange

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).

1256 questions
40
votes
6 answers

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: key value === ===== one test one …
WilliamKF
  • 511
  • 2
  • 6
  • 6
40
votes
3 answers

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 ) tblFEStatsBrowsers has got 553 rows. tblFEStatsPaperHits…
Mark S. Rasmussen
  • 1,465
  • 1
  • 14
  • 20
38
votes
3 answers

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 joined in such a way that if either EMPLOYEE.EMAIL1 or…
Gnanam
  • 927
  • 2
  • 8
  • 16
23
votes
5 answers

Should Query Tuning be Proactive or Reactive?

As a software developer and an aspiring DBA, I try to encorporate best practices when I design my SQL Server databases (99% of the time my software sits on top of SQL Server). I make the best possible design prior to and during development. But,…
Thomas Stringer
  • 41,128
  • 9
  • 109
  • 152
21
votes
2 answers

Difference between GROUP BY B,A and GROUP BY COALESCE(B,A)

I have been getting myself very confused. Could somebody kindly explain under what circumstances I would want to use a GROUP BY COALESCE? My guess is that I would use it if I wanted to conditionally group a set of data by column B (if B was not…
Mark McLaren
  • 323
  • 4
  • 13
21
votes
1 answer

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. What's the best way to understand this? I thought…
newenglander
  • 1,005
  • 5
  • 12
  • 23
20
votes
7 answers

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 are gaps. This query generates an example…
Stew
  • 413
  • 1
  • 4
  • 7
19
votes
3 answers

Alternatives to concatenating strings or going procedural to prevent SQL query code repetition?

Disclaimer: Please bear with me as someone who only uses databases a tiny fraction of his work time. (Most of the time I do C++ programming in my job, but every odd month I need to search/fix/add something in an Oracle database.) I have repeatedly…
Martin
  • 2,381
  • 4
  • 22
  • 34
19
votes
2 answers

How do I show the binlog_format on a MySQL server?

How do I show the binlog_format on a MySQL server? And if I dont like it how do I set it to XX permanently? Where XX is STATEMENT, ROW or MIXED.
Nifle
  • 1,432
  • 7
  • 17
  • 30
18
votes
7 answers

Is polling the only way for updating app's data from a database?

An application needs to have data as more freshly updated from a database as possible. In such a case, is there any other way for getting the data, besides of a timer based requesting (polling) the database? I work with a MS SQL Server 2008 (and…
rem
  • 1,505
  • 3
  • 20
  • 22
15
votes
3 answers

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 the queries involved. I found the following to…
Hasanain
  • 253
  • 1
  • 2
  • 6
15
votes
3 answers

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 SnapShotValue since the previous group. At present, the…
JoeGeeky
  • 1,227
  • 4
  • 14
  • 26
14
votes
3 answers

How to make multiple counts in one query?

I count records with queries like SELECT COUNT(col1) FROM table1 WHERE col1 LIKE '%something%' SELECT COUNT(col1) FROM table1 WHERE col1 LIKE '%another%' SELECT COUNT(col1) FROM table1 WHERE col1 LIKE '%word%' For each count, mysql needs to walk…
Googlebot
  • 4,119
  • 19
  • 57
  • 80
14
votes
4 answers

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 working with this delimited list from SELECT clause…
Ben Brocka
  • 2,013
  • 6
  • 28
  • 38
13
votes
1 answer

Сardinality estimation of partially covering range predicates

At the moment I'm trying to figure out how SQL Server evaluates the cardinality of range predicates that partially cover the histogram step. On the Internet, at cardinality-estimation-for-and-for-intra-step-statistics-value I came across a similar…
Павел Ковалёв
  • 184
  • 8
1
2 3
…
83 84