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.
155
questions with no upvoted or accepted answers
4
votes
1
answer
2k
views
How to put an execution time limit for MySQL Temporary table creation
We can use SET MAX_EXECUTION_TIME=10; statement to stop the execution of a SELECT statement, if it took more than 10 milliseconds to execute the query. But the same wont work for other statements like ...
- 163
3
votes
0
answers
6k
views
How to apply a condition on "count over partition"?
I'm using Oracle 11g.
Assuming multiple consecutive entries (sorted by datetime) with the same place_id represent a single visit.
With this table:
datetime_id | user_id | place_id
and I want to ...
- 159
3
votes
0
answers
180
views
"FOR UPDATE" in an "EXISTS SELECT" statement
Is there any potential problem in using (in a transaction)
SELECT EXISTS( SELECT * FROM mytable WHERE id=36 FOR UPDATE )
i.e. having SELECT ... FOR UPDATE within an EXISTS?
MySQL does certainly ...
- 473
3
votes
0
answers
9k
views
Speed up query with multiple EXISTS
I've split a monolithic table with hundreds of strings into a set of generic table + strings table, split by language. Here are the creates:
CREATE TABLE `items` (
`item_id` int(11) unsigned NOT ...
- 165
2
votes
0
answers
78
views
How to enable Database Auditing on SQL server
I want to audit SQL database activities, but when I am trying to enable Database Level Adut specification, it is capturing all activities that is done from application side and also from queries that ...
- 21
2
votes
2
answers
2k
views
SELECT inside SELECT in Redshift
select
t.col1,
t.col2,
a.col1,
b.somecolumn
VQ.a,
VQ.b,
VQ.e,
VQ.d,
VQ.f,
(select
t.status as a,
p.id as b,
p.permit as c,
p.des as d,
p.error_code ...
2
votes
0
answers
3k
views
Oracle 11g Select query returns NO ROW SELECTED but the table actually has data
In my production, There is a table named ABC under X schema. I have granted SELECT privilege on ABC table to another user named Z. When I execute the query
SELECT COUNT(*) FROM X.ABC;
as user Z it ...
2
votes
0
answers
81
views
How to select all results tied for first position
My current output is a list of caretakers and how many budgies they take care of. I get it via the following SELECT statement:
SELECT caretakers.name, COUNT(species.name)
FROM caretakers
JOIN ...
- 21
2
votes
0
answers
2k
views
Ran "Grant all privileges" but still getting "permission denied for relation"
I'm using PostGres 9.5. I'm confused about how to grant all privileges to a user for a particular database. I thought this would do the trick
postgres=# GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA ...
- 703
2
votes
0
answers
86
views
Multiple selects ordering desc from single table
I'm trying to do multiple selects to give multiple ordered columns. I'm struggling to get the below mysql query to achieve the output I want.
SQLfiddle: http://www.sqlfiddle.com/#!2/a1002/3
...
- 21
2
votes
0
answers
2k
views
mariadb out of memory crash
I have recently migrated from mysql (5.5.36 MySQL Community Server (GPL)) to MariaDB (10.0.10-MariaDB-1~wheezy), along with switching my cloud hosting provider.
Both machines have 2GB RAM and 1GB swap....
- 121
2
votes
0
answers
230
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 friend....
- 1,764
2
votes
1
answer
2k
views
Select last row for a certain group
I have two tables:
Table users
columns | type | notes
--------|--------|-------
id | bigint | PK
... | ... | ...
Table events
columns | type | notes
-----------|---------|------...
- 121
1
vote
1
answer
43
views
MariaDB: inconsistent SELECT results involving JSON
When executing the following statments on MariaDB 10.6.14
SET @json_1 = JSON_OBJECT('id', 'name');
SET @json_result_1 = JSON_OBJECT('test', @json_1);
SELECT @json_result_1;
SET @json_result_2 = ...
1
vote
0
answers
31
views
Possible explanation for why "SELECT" query executed from JDBC returning just the first row in the table
I installed OracleDB(XE - free version) and I tried to use it in Java. I have this code:
static final String DB_URL = "jdbc:oracle:thin:@localhost:1521/XE";
static final String ...
- 111
1
vote
0
answers
146
views
Copy rows and change corresponding values
I have 4 tables
Tables are used to store product attributes and its values in different languages
I'm trying to copy attribute (copy in same table to new row and increment/change some values).
First ...
- 11
1
vote
1
answer
95
views
Postgresql query takes unnecessary (?) long time
I know the web is full of questions, complaints, etc. like my question. But each of them still does not answer the questions of people finding those posts via Google, ...
I try to post my question ...
- 111
1
vote
0
answers
656
views
Very slow MariaDB query when adding ORDER BY/LIMIT but there are no rows
I am facing an issue with MariaDB 10.1 on a log table with the following (simplified) structure:
CREATE TABLE logs (
log_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
log_date TIMESTAMP DEFAULT ...
- 167
1
vote
0
answers
611
views
Optimize select query with OR and ILIKE
I have the following query for my PostgreSQL 13 database:
SELECT * FROM "user"
INNER JOIN "comment" on "comment"."user_id" = "user"."id"
...
- 11
1
vote
0
answers
326
views
Select value and display in a combobox if the datetimepicker value lies between two extreme dates in a table in database
I am creating an attendance management system in vb.net and using MS access as my database. I created a leave form in which the student on leave has a roll number and his details along with the from ...
- 11
1
vote
1
answer
179
views
MySQL - Select query to return all cols but only 1 of each combination of 2 identifying columns
I need to write a mysql query that returns all columns, but only 1 of each set of identifying columns.
i.e.
I have a table with a primary key on 3 fields (firstName, lastName, effDate). I can have x ...
- 11
1
vote
1
answer
143
views
Compare queries
On what basis should I compare performance of queries?
Query 1:
select * from dbo.Table1 where [T1_col1] like '%string%'
Execution Plan
Query 2:
select Table1.* from Table1 left join db2.dbo.Temp on ...
1
vote
2
answers
287
views
Track SELECT statements on specific tables
I'm searching for a way to keep track of any SELECT statements on a specific table in a database where logs are not enabled. I can't find any solutions to this except to force people to use a stored ...
- 11
1
vote
0
answers
1k
views
Many to many relationship, select rows based on values in list
I want to select group_runs based on a list of group_runners.player and I'm having issue getting rid of results that don't have all the values on that list. In this case group_runs with id = 2 shouldn'...
- 111
1
vote
1
answer
1k
views
MariaDB FULLTEXT search with short / mandatory words
Just checking I'm understanding this correctly:
CREATE TABLE customer (
id INT NOT NULL AUTO_INCREMENT,
name TINYTEXT NOT NULL,
PRIMARY KEY (id),
FULLTEXT (name)
) ENGINE = InnoDB;
...
- 135
1
vote
0
answers
18
views
Query optimalization - how create proper composite index
I have mysql 5.5 and table item have around 1-3 milions rows.
CREATE TABLE `item` (
`Id_item` int(11) NOT NULL DEFAULT '0',
`item_Kod` varchar(15) DEFAULT NULL,
`Date1` datetime DEFAULT NULL,
`...
- 11
1
vote
0
answers
1k
views
SQL: ON vs. WHERE in SEMI JOIN
Considering the following two (equivalent) SQL statements.
Why do their execution plan differ (especially in performance) that much?
I'm using Postgres 11.9 in this example.
select p.name
from person ...
- 111
1
vote
1
answer
383
views
How to use SELECT statement to get data from SQlite table using with dictionary values?
I have below dictionary values.
Example:
dic = {'ID1': ['Name1', 'Name2'],
'ID2': ['Name1', 'Name3'],
'ID3': ['Name2']}
I want to print address of every matched names from above dict ...
- 51
1
vote
0
answers
587
views
Why is PDO::ATTR_EMULATE_PREPARES = false causing "MySQL server has gone away" errors?
I've been using PHP PDO with the default setting of ATTR_EMULATE_PREPARES = true and it was working fine.
However, I wanted to be able to select database values as integers instead of having them ...
- 101
1
vote
1
answer
311
views
Count nested joined table records only if the table associated via bridge table have two records with given names
I have the main table on which I have to apply count. I have two bridge tables and two nested tables for has_many relation. You can generate tables and bridge tables with below code and also insert ...
- 149
1
vote
2
answers
297
views
What should I index when joining on a table with conditions?
I have a query like this:
SELECT "main_table".*
FROM "main_table"
INNER JOIN "other_table" ON "other_table"."deleted_at" IS NULL
AND "other_table"."id" = "main_table"."appointment_id"
WHERE "...
- 817
1
vote
0
answers
4k
views
Grafana - combine multiple SELECT from different measurments
I'm totally new here, I tried to find solution and I found this:
Influxdb and Grafana combine multiple SELECT
but this works fine if we have different fields from the same measurement, but this ...
- 11
1
vote
1
answer
91
views
IN subselects vs INNER JOIN (performance)
I have to make a choice:
choice 1: using IN subselect
choice 2: using INNER JOIN
See the examples:
IN subselect:
SELECT contractServiceCode FROM contractServices
WHERE contractID IN (
SELECT ...
- 452
1
vote
2
answers
39
views
Showing data that is not already in the data base
I have the following problem. I want to search a query in an existing data base and that the result shows me the entries of the query that are NOT already in the database.
Minimal example:
| ID | ...
- 11
1
vote
0
answers
495
views
MySQL count rows of multiple tables for each user
I basically need this with some adjustments, I just can't seem to figure it out how to do it.
I need to sum the rows of two tables, for example:
SELECT
student.StudentID,
student.`Name`,
COUNT(...
- 11
1
vote
0
answers
25
views
Relation Algebra: Selection of Cartesian Product
I am learning Relational Algebra basics for sql. I came across an example where the selection of cartesian product of two relations (R1 x R2) has been taken and I realize that it is same as selection(...
1
vote
0
answers
227
views
InfluxDB - How to get start timestamp of alternating series
I have these data series in an influx database, alternating between 0 and 1.
2018-10-02T07:12:00Z 0
2018-10-02T07:15:00Z 0
2018-10-02T07:18:00Z 0
2018-10-02T07:21:00Z 0
2018-10-02T07:24:00Z 0
2018-10-...
1
vote
0
answers
82
views
Why does the Select statement stalls when executed directly in sequence?
When executing the following in workbench or in code, the Drop table/Create Table works ok. The Select statement stalls when executed in one go.
If the table is MyIsam, it works ok. If executed [...
1
vote
0
answers
312
views
MS Access 2016 M:N relationships return multiple columns instead of multiple rows from a JOIN
Let's assume a data model like this:
Books could have multiple authors as well as authors could have written multiple books.
If a book and its authors are selected by the following statement
(this ...
1
vote
1
answer
1k
views
How to select recursively in a child parent design situation (in MySQL)?
Let's take into consideration the following tables:
CREATE TABLE actions
(
id BIGINT(20) unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
user_id BIGINT(20) ...
- 293
1
vote
0
answers
51
views
How to efficiently query relationships across multiple tables, stored in multiple rows
I'm having trouble conceptualizing how to query a set of tables that have various granularities of data, to get the information that I want. Let me explain the general table structure:
Table 1: ...
- 11
1
vote
0
answers
43
views
Get elapsed from log table
From a log table with a row for every column changed in another table I need to get the elapsed time between two complex event.
In particular the log table has the format
create table audit_log as
...
- 641
1
vote
0
answers
153
views
SQL select for a specific node all subnodes
I'm using mySQL on phpMyAdmin, my table is
| code | name | parent |
|------|------|--------|
| 10 |node10| 5 |
| 5 |node05| 2 |
where 5 is the parent of 10 (which is also a node), I ...
- 119
1
vote
1
answer
42
views
MySQL Help - Combining 2 MySQL Selects - Result in 1
I have two MySQL statements (see below), I would like to combine them together so if they both result in a 1 then the end result will be 1. I'm not sure how to construct this and was hoping for some ...
- 59
1
vote
0
answers
60
views
Fetch items where related rows has different values
I have table in MySQL called attributes, and other table, attribute_values, one attribute can have multiple values, values identified by context attribute and has value_c attribute wich contain value. ...
- 111
1
vote
0
answers
305
views
Matching same unknown characters in two columns
So here is the situation. I have a database with values in columns that look something like this
field_0_data1, field_0_data2, field_1_data1, field_1_data2
Is it possible to only select the columns ...
- 11
1
vote
0
answers
109
views
Find the last record of group of overlapped records
I have a little dilemma with filtering out result.
The result and what I want to achieve is not very trivial and I didn't find an answer anywhere after extensive search. So here is my problem:
I ...
1
vote
0
answers
125
views
Row locks on selects
select t.*, row_number() over (
partition by t.Entity_Identifier
order by t.Message_Sequence
) as seqnum
from Process_Activity t (ROWLOCK)
where t.Process_Id =
Is it possible for this ...
- 39
1
vote
0
answers
88
views
How to use the value from record like as varchar?
I'm trying update a old table with result of selec for another tables but get a error using this sintaxe (e.g.):
DO
$$
DECLARE
_roles refcursor;
_onerole varchar;
_record RECORD;
...
- 173
1
vote
0
answers
669
views
two column compare ,get count(*) of two column A value same cloumn B value
I have a Mysql table as
---------------------
-- page - user --
---------------------
-- page a - user 1 --
-- page a - user 2 --
-- page a - user 3 --
-- page b - user 1 --
-- page b - user 3 --
...
- 11