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
151 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
790 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
911 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
2answers
2k views

Sub-query executes 1 time for a parent query or more?

If i apply a static data in where clause is it faster then applying a sub-query. Example: (The query returns 5 records as a result set) SELECT STARTDATE, ENDDATE FROM TEST WHERE STARTDATE = ...
3
votes
2answers
4k views

Can someone explain why select with nolock will query a potion of updated data?

I was reading the answer from here (from stackoverflow, I think should ask in here) NOLOCK means placing no locks at all. Your query may returns portions of data as of before UPDATE and ...
3
votes
2answers
8k 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
3answers
171 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
13k 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
2k 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
359 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
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
1answer
304 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
11k 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
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
7k views

Error “column does not exist” in a SELECT with JOIN and GROUP BY query

I'm using PostgreSQL 9.1 with a Ruby on Rails application. I'm trying to list the last version of each "charge" (in my history table : hist_version_charges) belonging to the same project id ...
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
641 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
41 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
14k 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
194 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
6k views

SQL Xml returning empty root tag on empty record set

I am working on xml explicit to generate user defined nodes in xml output using Sql Server 2005 Express edition. The below code works fine, just that i couldn't manage to generate a empty node when ...
3
votes
2answers
25 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
141 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
1answer
226 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
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
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
152 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
38 views

Get rows with minimum value

I have tables company: company_id | - - - - - - - - - - - - - - 1 | 2 | employee: employee_id | - - - - - - - - - - - - 1 ...
3
votes
3answers
207 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
401 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
359 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
650 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
76 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
1answer
64 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
20k 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
373 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
56 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: ...
2
votes
4answers
319 views

WHERE NAME LIKE ( A SET OF NAMES )

I would like to drop all the jobs related to a set of specific databases. So I have added all the names into a table variable and I was trying to generate the scripts to drop the jobs. how can I ...
2
votes
3answers
327 views

Using expressions in the SELECT list to thin down a CASE statement

I have a query I'm working through rewriting - it has a huge bunch of case statements in the select list that all basically look like this: select Column1 = case when (Something = 2) and ...
2
votes
2answers
3k views

Insert into … (…) (Select…) on duplicate key update

Can you help me to solve a problem. The task is to insert a record in MySQL database. terms: If the record exists - to update the record. If the record does not exist - to create a new one. ...