Questions tagged [select]
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.
88
questions
111
votes
3
answers
168k
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 (k, v)
values (10, 1),
(10, 2),
(10, 3);
When ...
49
votes
8
answers
97k
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
...
24
votes
3
answers
24k
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 ...
10
votes
1
answer
70k
views
Insert into table select * from table vs bulk insert
I just wanted to know that SQL Statement INSERT INTO TABLE1 SELECT * FROM TABLE2, will work same like bulk insert ?
If no, Is there any way exclude index while inserting records. Process is inserting ...
5
votes
2
answers
661
views
SQL Server: Order of Rows
We all know that a simple statement such as:
SELECT * FROM stuff;
should not produce an ordered result. However, when I try to demonstrate this, it always comes out in primary key order.
Moreover a ...
45
votes
5
answers
48k
views
Benefits of using backtick (`) in MySQL queries?
In MySQL we can create queries with or without the backtick (`) symbol. Example:
SELECT * FROM TEST;
SELECT * FROM `TEST`;
Both works fine in mysql-console.
Is there any technical difference ...
3
votes
1
answer
828
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 ...
16
votes
4
answers
59k
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 ...
8
votes
5
answers
9k
views
Get a truly RANDOM row from a PostgreSQL table quickly
I always used to do:
SELECT column FROM table ORDER BY random() LIMIT 1;
For large tables, this was unbearably, impossibly slow, to the point of being useless in practice. That's why I started ...
7
votes
1
answer
92k
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 ...
6
votes
1
answer
2k
views
How to Choose Between VALUES and SELECT for INSERT?
This answer raised the question for me how to choose between VALUES and SELECT in such a function . Using PostgreSQL 9.4.3 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit:
...
5
votes
3
answers
9k
views
slow query with fulltext and left join
I have 3 tables
about 250,000 records.
table1 is MyISAM, add fullindex with title and content, add index with pid.
table2 and table3 are InnoDB, add index with pid.
Only query table3, just cost 0.04 ...
0
votes
3
answers
474
views
Selecting multiple rows by given amount of volume
i have a table like this
+--------+------------+-----------+
| id | volume | price |
+--------+------------+-----------+
| 1 | 1.0000 | 14250.00 |
| 2 | 0.2500 | 14251....
-1
votes
2
answers
23k
views
Return hierarchical data from a self-referencing table (parents, children)
I have the following SQL Server 2008 table and data:
CREATE TABLE dbo.MyTable (Sno int, refid int);
INSERT INTO dbo.MyTable VALUES
(17, 8),
(16, 8),
(15, 7),
(14, 7),
(13, 6),
(12, 6),
...
43
votes
7
answers
172k
views
How to cast an integer to a boolean in a MySQL SELECT clause?
I'm new here so be kind to me. I have the following scenario:
I have many tables which, for the sake of simplicity, are represented in a View in my MySQL database. My problem is that I need a value ...
35
votes
1
answer
138k
views
MySQL Count rows from another table for each record in table
SELECT
student.StudentID,
student.`Name`,
COUNT(attendance.AttendanceID) AS Total
FROM
student
LEFT JOIN attendance ON student.StudentID = attendance.StudentID
I am trying to count the last ...
6
votes
1
answer
98k
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 ...
5
votes
1
answer
9k
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
1
answer
34k
views
Delete from table where multiple fields match select subquery from other table
I want to delete an entry in a table where multiple fields match the results of another select subquery which takes data from another table.
This is what I have so far, though it doesn't work:
...
2
votes
1
answer
4k
views
Select with if statement return 'column does not exist'
Database, application.
PostgreSQL 11.4 (Ubuntu 11.4-1.pgdg19.04+1),
Dbeaver 6.1.1.
What I need?
Return right values.
What I do.
Here is part of code.
select
if(available_count < 5, "neni skladem"...
2
votes
1
answer
162
views
Issue with one query - can some Index help to make it faster?
I am working on some IBM web application and it has one query hard coded in some part of the application which I cannot change or optimize - so modification of the SELECT statement is not possible.
...
2
votes
2
answers
559
views
Select arbitrary element in postgres
I have a table that has path1, path2, and sha1 value. For any values of path2 and sha1, there can be multiple values of path1. I just want one of those paths. I don't really care which one.
I'm ...
2
votes
1
answer
19k
views
ora-01410 invalid RowID sporadically in a select statement
I have a select that looks up some data by a join. The system runs Oracle 10.02g and the select looks like this
SELECT distinct t1.crit
FROM table_name1 t1
INNER JOIN table_name2 t2
ON t1.crit ...
2
votes
3
answers
4k
views
How to speed up a multiple select query
EDIT - Putting my whole stored procedure:
I have the below stored procedure:
CREATE PROCEDURE [dbo].[data_Update]
@Owner NVARCHAR(255),
@ID VARCHAR(255),
@Division VARCHAR(255),
@Manager ...
1
vote
1
answer
3k
views
Error 1052 Field is Ambiguous using Inner Join Function
Error '1052' Field is ambiguous. I understand the error that is happening, and I've looked at several places for the fix and then applied the inner join function in attempt to fix it. It's still not ...
0
votes
3
answers
14k
views
Is there a default SELECT order? [duplicate]
I need to clear up what I mean by order here. I am simply asking about what order the data is fetched in.
If I simply SELECT * FROM table; in SQL Server without an ORDER BY clause, I find that the ...
-1
votes
2
answers
1k
views
Select multiple rows with different criteria for each [closed]
I am trying to put together a query will return sales information across multiple date ranges. I was planning to do something like this:
SELECT count(*) AS thirty_day_sales FROM "MySchema"."MyTable" ...
63
votes
2
answers
74k
views
Reference column alias in same SELECT list
I'm converting an old MS-Access-based system to PostgreSQL. In Access, fields that were made up in SELECTs could be used as parts of equations for later fields, like this:
SELECT
samples.id,
...
40
votes
1
answer
18k
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 *
...
35
votes
3
answers
5k
views
Why does this query work?
I have two tables, table_a (id, name) and table_b (id), let's say on Oracle 12c.
Why does this query not return an exception?
select * from table_a where name in (select name from table_b);
From ...
34
votes
2
answers
317k
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 ...
21
votes
1
answer
28k
views
Why is "select *" faster than "select top 500 *" in SQL Server? [duplicate]
I have a view, complicated_view -- there are a few joins and where clauses. Now,
select * from complicated_view (9000 records)
is faster, much faster, than
select top 500 * from complicated_view
...
16
votes
2
answers
30k
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 ...
12
votes
2
answers
13k
views
MySQL - UUID/created_at cursor based pagination?
For a large dataset, paginating with an OFFSET is known to be slow and not the best way to paginate. A much better way to paginate is with a cursor, which is just a unique identifier on the row so we ...
10
votes
4
answers
16k
views
show table name + number of records for each table in a mysql innodb database
How to list all tables in the current database, together the number of rows of the table.
In other words, can you think of a query to come up with something like this in mysql?
+---------------------...
10
votes
2
answers
55k
views
Select query takes more time than it should
I have a MySQL database table with nearly 23 million records. This table has no primary key, because nothing is unique. It has 2 columns, both are indexed. Below is its structure:
Below is some of ...
9
votes
2
answers
21k
views
Why can I select all fields when grouping by primary key but not when grouping by another column
How is this a valid statement (where id is the primary key of the table):
select * from table group by id ;
and this is not:
select * from table group by name ;
ERROR: column "pgluser.id" must ...
8
votes
1
answer
49k
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 ...
8
votes
2
answers
48k
views
How to use variables inside a select (SQL Server)?
If I want to compute a column and use the result in more than 1 column, how do I do this without making the computing twice?
Example:
SELECT LOWER(SUBSTRING([NAME], 4, 100)) + '@somedomain.com' as ...
7
votes
2
answers
85k
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 ...
5
votes
7
answers
52k
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 ')'....
5
votes
3
answers
41k
views
Influxdb and Grafana combine multiple SELECT
I have some network counters in Influx that I graph with Grafana.
I'm trying to combine stats from a couple ethernet interfaces and combine them to show aggregate bandwidth.
These two separate ...
5
votes
1
answer
4k
views
PostgreSQL converting undefined JSONB fields into a row dynamically
I have a table with a field of type jsonb, I would like to split the column of field jsonb for all the json keys. This column lacks a schema. For instance:
From
CREATE TABLE v(id,jsonb)
AS VALUES
...
5
votes
1
answer
4k
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
2
answers
3k
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 ...
4
votes
2
answers
3k
views
select specified number of unique IDs where second column is unique
Look at the following example starting from the top row (id=9) and work your way down, selecting a limit of 4 rows that have sec's that we have not yet seen. We "select" id=9 because we don't yet have ...
3
votes
1
answer
4k
views
SELECT Parent-Child with SORT
I have a simple, one level parent child relation table, with following columns:
ID_Asset| Parent_ID_Asset | ProductTitle
I need output grouped by Parent followed by children, and also sorted by ...
3
votes
3
answers
266
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 its current form requires a regular truncation and recreation of a lot of rows (about 1 million).
We're ...
3
votes
2
answers
15k
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
--+-------+----------------...
3
votes
1
answer
669
views
Search within same ID across several rows
My table has the following structure:
+----+---------+------------+-------------+
| id | word_id | meaning_id | sentence_id |
+----+---------+------------+-------------+
| 1 | 1 | 15333 | ...