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

12
votes
7answers
17k views

Is it possible to mysqldump a subset of a database required to reproduce a query?

Background I would like to provide the subset of my database required to reproduce a select query. My goal is to make my computational workflow reproducible (as in reproducible research). Question ...
9
votes
3answers
6k views

What is the default order of records for a SELECT statement in MySQL?

Suppose you have the following table and data: create table t(k int, v int,index k(k)) engine=memory; insert into t values(10, 1), (10, 2), (10, 3); When issuing select * from t where k = 10 with ...
2
votes
1answer
316 views

select for update gives error on indexed column

here is my code: mysql_query("start transaction"); $q="select max(id) from test for update"; $r=mysql_query($q); $row=mysql_fetch_array($r); for ($k=0;$k<100000;$k++) { $q="insert into test values ...
1
vote
4answers
6k views

Recursive self joins

I have a comments table, which can be simplified down to this: comments ======= id user_id text parent_id where parent_id is nullable, but might be a key for its parent comment. Now, how can I ...
14
votes
2answers
65k views

oracle - list users with access to certain tables

I'm sure this has been asked before but I can't seem to find the relevant details for the following. Is there some sort of pre-built table that can do the following (I have used dba_tab_privs but it ...
22
votes
1answer
3k views

USING construct in JOIN clause can introduce optimization barriers in certain cases?

It was brought to my attention that the USING construct (instead of ON) in the FROM clause of SELECT queries might introduce optimization barriers in certain cases. I mean this key word: SELECT * ...
3
votes
1answer
2k 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 ...
0
votes
0answers
29 views

SQL JOIN excluding duplicate

I'm using PostgreSQL under ubuntu 14.04. I have two tables as below: create table material_base_name ( id bigserial, base_name varchar(50), constraint pk_materials_base_name primary key (id) ); ...
0
votes
1answer
993 views

Select Multiple Values from Same Column; one sql statment

I am looking to return a list of users from a database. There are hundreds of users in the db. Each user has it's own unique user_id. All of the values that I am trying to return reside in the same ...
9
votes
2answers
4k views

How to select specific rows if a column exists or all rows if a column doesn't

I'm writing a script that gets a count of rows for a few tables, however for some tables I want to only get a count of rows where a flag is set (in this case active=1). Is there a way I can do this ...
2
votes
5answers
301 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 ...
0
votes
3answers
489 views

Can a RDBMS connect to a remote server, execute a select query and copy them into a local table just with SQL commands?

I'm sorry it may sound stupid but I've been wondering about how multiple databases are automated before the virtualization era. Can a DB Adminsitrator make a stored procedure to get data from a remote ...
7
votes
4answers
2k views

Multiple operations using WITH

Is there a way to execute multiple operations using the WITH statement? Something like WITH T AS ( SELECT * FROM Tbl ) BEGIN OPEN P_OUTCURSOR FOR SELECT * FROM T; SELECT COUNT(*) INTO ...
4
votes
2answers
1k views

Has SELECT an INSERT clause?

I know the form Select * into new_table from old_table statement. Bur recently I found 2 posts here and here which use it like SELECT * FROM NhlPlayer INSERT INTO PlayerBackups I find nothing in ...
3
votes
2answers
20k views

Select column names whose entries are not null

I would like to have a list of those columns of a table that have at least one not-NULL data entries in them. In other words, I would like to get the column names for which the following returns at ...
2
votes
1answer
5k 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 ...
2
votes
1answer
713 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 ...
2
votes
1answer
7k 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 ...
0
votes
0answers
27 views

MySQL most specific to least specific

I have records in the next table: id_a | id_b | id_c | data each of the parameters can be 0 or arbitrary integer number. I want to get the most specific record for provided values of a, b and c. ...
3
votes
3answers
148 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
2answers
2k 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 ...
2
votes
1answer
237 views

Difference data between adjacent rows

I have a table (called Visits) with ReqTime DATETIME NOT NULL column AND Duration INT UNSIGNED column. Duration should be set to the difference of ReqTime for two adjacent rows. But Duration is not ...
2
votes
2answers
158 views

Why is this MySQL SELECT performing so slowly despite indexing?

I have a large MyISAM table A (500m entries) that connects two other tables B,C with a n:m relation. So my table A looks like this: CREATE TABLE `A` ( `id_b` int(10) DEFAULT NULL, `id_c` int(12) ...
2
votes
2answers
5k 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(*) ...
2
votes
2answers
1k views

Select rows with repeat data within certain timeframe

As a follow-up question to my previous question on difficult row categorisation, I have another problem which I'm sure will be easily solvable. After this I plan on reading a good book or two on ...
1
vote
2answers
563 views

multiple select query for the same tables with union [closed]

Using below query i want to find out the top number of students from each catagory(SC,ST,OBC,etc) on plus2percentage basis. eg i want top 3 students from SC catagory, top 2 from ST and Top3 from OBC. ...
1
vote
1answer
308 views

How to improve the performance of this select query?

I have two tables: TABLE1 and TABLE2. And I am running this select query to fetch the data. It takes long time to run: SELECT TABLE1.* , TABLE2.COL1 , TABLE2.COL4 FROM TABLE1, TABLE2 WHERE ...
1
vote
1answer
416 views

How to add additional tables/fields into a query with multiple inner joins?

Given this query (many thanks to RolandoMySQLDBA from question Select records that do not have associations outside a certain list), how do I add other fields from other tables into my result? SELECT ...
1
vote
1answer
351 views

Select records that do not have associations outside a certain list

I have a mySQL table of nodes and each node can be associated with many categories. I have a list of category ids that are acceptable (498, 499). I want to select only nodes who do not have ...
1
vote
2answers
3k views

Getting repeated value in a group_concat only if ids are different

I have two tables, first the table Product: id|category_id --+----------- 1 | 12345 2 | 12345 3 | 12465 And then a table activity: id|prod_id|activity_type |description ...
1
vote
0answers
175 views

Get all comment.message, status.message, users.* and profiles.* where users.user_id exists in friend.friend_id or friend.user_id

I need some help building this query because it's a bit complicated at least for me. I need to get all comment.message, status.message, users.* and profiles.* where users.user_id exists in ...
1
vote
1answer
265 views

Shift db data to another column

I have the following table id | ev_id | ev_loc_id | a | b | prev_a | prev_b If I the (ev_id AND ev_loc_id) not exist in the table then I just insert them, otherwise I'm putting ...
0
votes
1answer
34 views

How to sum the buy order and sell order in one row per each ticker

I have a table containing some trading information of several tickers and I would like to divide the data into sum(Amount) for buy and sell separately, grouping by each ticker. I have one table ...
-1
votes
2answers
1k views

Select query having count and variable

I have a table like the one below. I need to form a select query. I'm going to pass a RefID like SELECT SNo FROM Register WHERE RefID = 1 And beside the SNo in the Statement, I need to have ...