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).
Questions tagged [query]
1578 questions
43
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
- 987
- 2
- 9
- 16
42
votes
5 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
- 531
- 2
- 6
- 6
39
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,455
- 1
- 14
- 20
30
votes
1 answer
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 how about reading?
➥ Is there a way to query for…
Basil Bourque
- 11,188
- 20
- 63
- 96
26
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,075
- 5
- 13
- 23
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
- 42,434
- 9
- 120
- 155
23
votes
6 answers
Is there a generic term for tables and views?
I am looking for a generic term, e.g. for a database abstraction, that includes all tabular data structures like database tables, views, tabular query results aso.
As far as I understand, 'entity' is not the proper term since this would correspond…
Pinke Helga
- 341
- 2
- 5
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
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,472
- 8
- 17
- 31
21
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
- 493
- 2
- 5
- 9
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,420
- 4
- 26
- 35
19
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,555
- 3
- 20
- 22
16
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,297
- 4
- 16
- 26
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
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,551
- 26
- 70
- 96