A core SQL statement, SELECT retrieves data from one or more tables or other sources of row set data such as views or table-valued functions.

learn more… | top users | synonyms

4
votes
1answer
1k views

Tricky selection of grouped rows, selecting based on values of two distinct but related rows

Say I have a table with the following information (sorted here for display purposes) taskid | reference | centreid | date_out --------------------------------------------------- 10001 | ...
4
votes
1answer
140 views

Fast alternative for “NOT IN”

I have a table A that got a field called id that is the primary key of this table. I also have a table called B which also have a field called id as primary key. Now I want to get all rows from ...
4
votes
1answer
167 views

Mysql: Problem using GREATEST with IFNULL and join

I have a query selecting the greatest value of 2 timestamps. To achieve it I'm calling: SELECT GREATEST( IFNULL( tableb.create_time, 0 ) , IFNULL( tablea.create_time, 0 ) ) I'm using IFNULL ...
4
votes
0answers
846 views

SQL Server Deadlock - Two SELECT Exclusive Locks on Exact Same Index and Key [closed]

How is the deadlock graph below possible? How did two SELECT statements simultaneously acquire an exclusive lock on the exact same resource? The exclusive locks were on the primary key index for ...
3
votes
5answers
969 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 ...
3
votes
2answers
3k views

Does the SELECT statement count into DML?

Looking at the name Data Manipulation Language (DML) I would assume, that all contained statements are actually for manipulating data. As far as I know the SELECT statement can only be used to query ...
3
votes
3answers
267 views

Listing rows with a WHERE condition

I am struggling with a SQL query that lists hosts that are offline. Problem The problem occurs when I need to list hosts that don't have any ports open (status = 0), so looking at the table at the ...
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

MySQL: Is it possible to order a query by a specific letter using ORDER BY?

What I'd like to do is order my query so that the results show every row found, but are ordered by showing results that begin with the letter 'D' first. Is this something that is possible to do with ...
3
votes
3answers
172 views

Tuning reads (or perhaps the truncate!) for unusual database structure requiring regular truncates

We have an unusual database structure (I leave the reasoning for another question), that in it's current form requires a regular truncation and recreation of allot of row (about 1 million). We're ...
3
votes
1answer
14k views

Multiple “GROUP_CONCAT”s with WHERE clause

In a table as id name type info 1 BMW car yes 2 Reno car no 3 IBM electronics no 4 Sony electronics yes 5 Mazda ...
3
votes
2answers
3k views

Select query according to date

We have a table to store the Events. Table has got a schema like EventID INT Primary Key EventName NVARCHAR(100) StartDate Datetime EndDate Datetime So in the front end we want to show the events ...
3
votes
3answers
3k views

Does the MySQL primary key affect the efficiency of a SELECT query?

Does the primary key serve any purposes apart from uniquely identifying a specific row? For example, a table with an autoincremented primary INT key could greatly aid in search due to the option of ...
3
votes
1answer
5k views

Getting unique names when the IDs are different (DISTINCT ?)

I've got this table: id | name 1 | John 2 | John 3 | Peter 4 | Mike 5 | Mike And I want to get the unique names, getting the latest one in the list. I could go for: SELECT DISTINCT name ...
3
votes
5answers
442 views

Find all values not present for other values of other column

Let's suppose I have a table with integer columns a and b (each pair (a,b) is unique) and following data: a | b ------- 1 | 2 1 | 3 1 | 4 1 | 5 2 | 1 2 | 3 2 | 4 3 | 1 3 | 3 Now I need to ...
3
votes
1answer
312 views

SELECT multiple sensor values in one query

Background I have a couple of devices, each with a couple of sensors. I log these every now and then and stores them in a table described below. When someone requests a web page, I fetch a couple of ...
3
votes
3answers
12k views

Return int value from boolean field in SQL select

Is there a way to return an int value for a boolean field in a sql select statement?
3
votes
2answers
68 views

Inserting rows with WHERE condition

Structure 1 table named: scan 2 columns named: id, and date Problem I have two working queries, a SELECT and then an INSERT which I would like to merge together creating a INSERT where the ...
3
votes
1answer
1k views

Index on foreign key makes query extremely slow

We are recently experiencing a tremendous query slowdown with spilled over temp tablespace. A specific query causes this problem. The queried table (table3) has an indexed PK, three FK with indexes ...
3
votes
1answer
3k views

Any disadvantages of “SQL_BUFFER_RESULT” for MySQL SELECT?

I've been researching the usage of SQL_BUFFER_RESULT. Mostly it is referred to as an aid to reduce table lock issues. It seems to be a good option to use. However I cannot seem to find any ...
3
votes
1answer
702 views

Single AND operator and multiple OR operators

I wanted to select a record_id with a user_id of 1 and category_id can be 1, 2, or 3. Every SELECT statement should return 1 record. View my sample table here: http://i49.tinypic.com/2ezjtk8.png How ...
3
votes
1answer
42 views

Extra “count” row with total of 0

Consider the following: db=> SELECT ga, count(ga) FROM gb GROUP BY ga ORDER BY ga ; which gives the following output: ga | count ---+------ ? | 200 A | 100 B | 50 | 0 (4 rows) ...
3
votes
2answers
16k views

Index for date column for range query in Oracle?

We have a lot: select count(*) from TBL where XDATE between ? and ? and FLD = ?; We expect to boost performance by tuning index on XDATE column (which have DATE type). What type of indexes more ...
3
votes
3answers
1k views

Is there a way, in a single SQL statement to ensure that all items in a list are present in some column?

What I'm looking for is a way of verifying that all of a list of items (let's say 'FOO', 'BAR' and 'BAZ') are all in a given table.column. This would be something like SELECT something FROM sometable ...
3
votes
3answers
2k views

Count consecutive rows in mysql

I would like to get the count of consecutive rows in MySQL. It is a large database I try to avoiding the joins. Here gadget_id means vehicle and every 20/30 second the vehicle sends the current ...
3
votes
2answers
195 views

Is it always bad to use SELECT * FROM?

It is widely recommended NOT to use SELECT *. I understand this, as it is not a good idea to bring the entire row into memory, when only a few cells are needed. Is it still bad to use SELECT * when ...
3
votes
2answers
1k views

Select total number of records from common table expression

We are using common table expressions to temporarily save the search results. WITH Results ( ------ ) AS ( select --- ) SELECT * FROM ...
3
votes
2answers
28 views

Howto (async?) preview intermediate resultset

Is it possible to retrieve parts of the resultset from SQL Server queries in advance? For instance if I run a query that will return 20,000 rows, is it possible to throw the results in packages of ...
3
votes
1answer
208 views

Update columns values using select from external database and table in MariaDB trigger

I have two databases pdone and veevan. pdone is a kind of master and veevan keep on continue sync against an external sources. I need to perform a internal sync between veevan and pdone so I am using ...
3
votes
2answers
256 views

Query parent table and get child tables columns

I have two tables, one inherits the other: CREATE TABLE parent ( col1 INTEGER ); CREATE TABLE child ( col2 INTEGER ) INHERITS( parent ); // Insert some data INSERT INTO parent( col1 ) ...
3
votes
1answer
248 views

postgres filtering select based on combination of joins

I am having trouble filtering a request that depends on a combination of child elements (one-to-many relationship) Please consider the following example. CREATE TABLE sample_container ( id serial ...
3
votes
1answer
29k views

How do I select items from a table where a single column must contain two (or more) values?

I have a MySQL database table that references different words and their locations in documents. I want to return the IDs of documents that contain all of the words. Here is an example table. docid ...
3
votes
1answer
3k views

MySQL Insert into two tables using new IDs

I'm trying to select data already in the database and insert it into two tables using the generated ID from table one. table1 contains configuration options, table2 contains pricing data. Table2 uses ...
3
votes
2answers
71 views

MySQL - Order by closest value on multiple columns

I have a table for a game lobby. In this lobby a user gets put in their connection and their level. In my SQL statement I am getting all the users in the lobby which are between +5/-5 of the user's ...
3
votes
1answer
2k views

How can I make a select statement get blocked?

Hello I am trying to intentionally make a SQL select statement get blocked by another simple SQL delete or update statement, for the purpose of learning. I prefer only InnoDB tables. To prepare the ...
3
votes
1answer
172 views

PostgresSQL: Get single attribute of UDT in SELECT statement

I created a user-defined type in a PostgreSQL 9.2 database and am trying in the SELECT statement to get only an attribute of the UDT. However, I don't seem to get my code to work. Creation of type ...
3
votes
1answer
3k views

Simple Oracle query hangs depending on WHERE clause

Not used to using Oracle, but we have a large database where a non-unique query like SELECT * FROM employees where department = 'HR' is working, results list up no problem. But when I do ...
3
votes
2answers
43 views

Get rows with minimum value

I have tables company: company_id | - - - - - - - - - - - - - - 1 | 2 | employee: employee_id | - - - - - - - - - - - - 1 ...
3
votes
3answers
238 views

How does like query work with indexed table

I am working in Spring-java and mysql. I have to query the table of size 100k records. Table has say 10 columns. And In my sql select query I have to make like queries with %text% search on say 4 ...
3
votes
1answer
493 views

Better way to select from nested tables in PostgreSQL

I have the following schema with millions of rows in the match table and each match have two sides (away and home). I want to create a view which shows the most significant data regarding the matches ...
3
votes
1answer
382 views

Combine - Select Into, with Insert Into Select

I've read many explainations like this one here that say "Select Into ..." is to create new tables and "Insert Into ..." is to append to existing tables. I'm automating a coworker's sql scripts. ...
3
votes
1answer
2k views

Selecting minimum value using a subquery

I need to write a query to grab the lowest price from each merchant and output the price link (p_link) and some other information such as merchant name + rating. We have a table for prices ...
3
votes
1answer
9k views

how to fetch different data on compare rows of two tables

I have two tables in my database, one is user_app table in which user info are stored, table are as: +--------------+--------------+------+-----+-------------------+-----------------------------+ | ...
3
votes
2answers
674 views

Should I use select count(*) for counting records or having a field like comments_no for tables like post, comments?

In my recent project which is about social networking for an Asian country, I'm in doubt whether I use the below SQL statement for counting records: SELECT COUNT(id) shareLikeNo FROM ...
3
votes
3answers
84 views

SELECT + INSERT or INSERT alone

I have an application where several devices send data every second to my server. On the database I have a table with the devices and another table with the data sent every second. The first time a ...
3
votes
2answers
93 views

SQLite Like clause match whole word only

I have a SQLite database with a table named: minecraft +----+----------------------+ | id | name | +----+----------------------+ | 1 | Pocket Mine MP | | 2 | Open Computers ...
2
votes
5answers
21k views

Retrieving row count without using Count function

I wrote a query below which shoots me a syntax error why would it do so, SELECT MAX('Row') FROM (SELECT ROW_NUMBER() OVER(ORDER BY ID DESC) 'Row' FROM USERS) Error Desc: Incorrect syntax near ...
2
votes
5answers
394 views

How define select, if bridge pair may have 3 players?

Normaly a pair has 2 players, but if there is an odd number of players at the club, one 'pair' has 3 players. Table, members: pair, player_id 1 1 1 2 2 3 2 4 3 5 3 6 4 7 4 8 5 9 5 10 6 11 6 12 6 ...
2
votes
2answers
66 views

CTE To Use Alternate Select When Select Returns No Rows

I have created a involved CTE, no recursion, with multiple selects being done as it processes the business logic of the requirement. But I have been advised that the logic needed for the scenario ...
2
votes
3answers
1k views

How to query efficiently from Postgres to select special words?

Let's say that I have a table called words with very many records. Columns are id and name. In the words table I have for example: 'systematic', 'سلام','gear','synthesis','mysterious', etc. NB: ...