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.
5
votes
3answers
1k 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 ...
5
votes
6answers
6k 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
...
2
votes
1answer
170 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 ...
20
votes
1answer
941 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 *
...
7
votes
2answers
19k 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 ...
2
votes
5answers
220 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 ...
3
votes
2answers
2k 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 ...
0
votes
3answers
344 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 ...
5
votes
4answers
493 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 ...
2
votes
2answers
275 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 ...
2
votes
2answers
630 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 ...
1
vote
1answer
29 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
45 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
0answers
85 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
191 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 ...
-1
votes
2answers
102 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 ...
