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.
1,075
questions
0
votes
2
answers
101
views
Is default ordering affected by clustered index? [duplicate]
My colleague and I are debating whether the default ordering of rows from a SELECT statement (without specifying any ORDER BY) is controlled by the clustered index or not.
He demoed this by creating a ...
0
votes
1
answer
41
views
Ideal multi column index ordering for a low-cardinality match and a high-cardinality range
In postgres, I am doing a query on 2 columns
string type with only 3 possible values
timestamp the_timestamp
the query is
select id from my_table where type = 'a-value' and the_timestamp < '2024-...
0
votes
2
answers
54
views
How to calculate the division of two SELECTs in Postgresql?
I would like to calculate the division of two rowcounts, so something like this:
(SELECT COUNT(*) FROM table WHERE col_name > crit) / (SELECT COUNT(*) FROM table)
However, this creates a syntax ...
0
votes
2
answers
27
views
How to query two tables and get the rows from one mask the ones from the other?
WHAT I HAVE
I have two tables with the same structure. We can say that one is the main table, while the other is a side table, meaning that each row tracks an edit to a corresponding row from the main ...
0
votes
2
answers
89
views
Select last record of every hour, but time is not precise
I have table in MariaDB like this
INSERT INTO `electric_meters` (`id`, `ts`, `id_sensor`, `value`) VALUES
(166063, '2024-02-16 07:00:00.625', 'ats01_TotalCounter', 8801.936),
(166043, '2024-...
1
vote
1
answer
34
views
Moving records between tables in CockroachDB is taking too long
I have a requirement where I have to move records from table original_table to table new_table for the last N days.
Below is a small sample of my basic script that does this.
while True:
result = ...
0
votes
1
answer
38
views
SELLECT * FROM Table WHERE name=$variable not working
I'm trying to set up a simple SQL Code to collect a users details when they log in to a new site I'm writing, but it throws an error every time. Any Ideas what. I'm doing wrong?
$sql = "SELECT * ...
0
votes
0
answers
31
views
Why does this index make one query faster but another one slower
I am optimizing my database I found an index that makes one query go from 400ms to 2ms but it also makes another query go from 1000ms to 4000ms. It seems that the planner is choosing a suboptimal path ...
0
votes
0
answers
82
views
An alternative to PostgreSQL union?
In the following example variables in single quotes '' are arguments passed to the PostgreSQL query. If 'tag_args' is an empty array, the first row of result table is a row of three nulls and I use ...
1
vote
1
answer
51
views
Does a commit occur all at same time from a transactional point of view?
Assume a "Bank_Account" table exists and contains a single column "balance", where everyone has balance=100.
If a transaction performs "update Bank_Account set balance = 200 ...
0
votes
1
answer
48
views
Out of my depth with post mysql 8 update (Warning: #1287 Setting user variables within expressions is deprecated)
Now I can get my way around PHP, and I can get around basic MySQL queries, but I'm not really comfy with MySQL, and have inherited an issue that occurred today after our hosting company updated their ...
0
votes
2
answers
119
views
Duplication records with INNER JOIN
I’m validating records after a partition’s rotation.When I run the SELECT COUNT for each table I get 114,817,959 records.
SELECT COUNT(*) FROM SMS.dbo.MessagesPerf WITH (NOLOCK) WHERE
DateEntered &...
0
votes
1
answer
22
views
select user with least amount of tiquets between all other users
I have a table where I store users info like this
user
tickets
s.lewis
0
a.barrantes
2
x.mendez
3
j.robert
0
I want to create a query to select the user that has the least amount of tickets ...
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 = ...
0
votes
0
answers
26
views
SQL Select based on pattern
I have a SQL column in my system that has a bunch of file names. I am attempting to separate the file names based upon a matching file name pattern. Some files have a middle initial and some dont. ...
1
vote
1
answer
55
views
How can I SELECT columns from one table that corresponds with a column of another table on JOIN without creating a relation between the tables?
I have 3 tables:
CREATE TABLE catalog.shoes (
shoe_id SERIAL PRIMARY KEY,
brand VARCHAR(255) NOT NULL,
model VARCHAR(255) NOT NULL,
price INT NOT NULL,
CONSTRAINT shoe_name UNIQUE (...
0
votes
1
answer
39
views
Select distinct
I had some confusion regarding distinct keyword...
For example Table test has 5 columns a b c d e, and column a has repeated value, and I want to use distinct keyword to get distinct values while also ...
0
votes
1
answer
104
views
UPDATE & SELECT & INNER JOIN combined (SQL92) — How?
I have two tables with the following columns:
Table1
id
Sort
dou
and
Table2
id_Table1
Sort
and I need to read all values of Table1.Sort where Table1.dou = 1 and write them into Table2.Sort, into ...
1
vote
0
answers
30
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 ...
1
vote
1
answer
62
views
Select first row of a particular group with one attribute selected as 0 while all other rows should be selected as is
I have a requirement where I need to select first row of a particular group and for all other groups I need to select the row as is in the same query.
Here is my table
CREATE TABLE mytable(
afid ...
0
votes
1
answer
46
views
PostgreSQL select a record that not pass the condition
I have partitioned a table named Order into two partitions, Order_open and Order_close.
I run following query on Order table to retrieve the related record ("orderID"='e641d634-5c6c-4c75-...
1
vote
0
answers
140
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 ...
0
votes
1
answer
237
views
How to speed up SELECT from not-unique column?
I have a simple query in a Postgres 15 database. No joins or other fancy stuff:
select * from my_table where col='val'
The goal is to speed up the query. I'd say I need an index, but the values of the ...
-1
votes
2
answers
83
views
Is it better to run one query returning a large quantity of rows or several smaller queries?
I have a table with 800M+ rows.
I have some process that need to loop over 500k+ rows from this table.
The query is a basic SELECT, without join nor aggregate function. The query is approximately:
...
0
votes
1
answer
199
views
MSSQL - Insert Into Select number from global count table and update the global count for each row then inserted
Not sure I've articulated that well in the title, so here goes...
I have three tables
Table1
ID
NewNames
10
some row data
11
some more row data
Table2
ID
OldNames
1
my old data
2
and more data
...
0
votes
1
answer
1k
views
Left Join with only one of multiple matches in the right table
I have a database, where every entry may have multiple names, therefore I made a name database. While all names will be displayed otherwhere I need a list of all main entries with one representative ...
0
votes
2
answers
110
views
Return rows if and only if all the values specified in the IN clause are present
I have a table called users that have id and name columns. Let's suppose I have users with following ids:
1, 2, 3, 4, 5, 6, 7
I need to craft a query that returns rows if and only if all the values ...
2
votes
1
answer
58
views
Inefficiency of index in SELECT requests after large INSERT requests on myisam tables on MariaDB 10.6
This is my first post on this website. Please don’t hesitate to indicate me if there is any problem in my post.
I have a new issue when migrating from MariaDB 10.1 to MariaDB 10.6 on indexes with ...
0
votes
1
answer
32
views
Replace course_id with course_name in results
I have schedules of class times and I place the course id in the classtimes. When I run the query I see that in 30 minute times what courses are scheduled, but in the results I want to see the name ...
0
votes
1
answer
42
views
How to query in the given order
select * from table where options=(10,223,43,1,23,54,323,32)
But the result is not coming in the given order of options. Options need to be changed frequently.
0
votes
2
answers
48
views
Count amount players played in same game
I'm trying to count a set of values in a mysql table, where I'm not sure if there is an easy way to do it directly in mysql or if it would be easier to just do it in any programming language after ...
6
votes
2
answers
4k
views
Mysql query works in 200ms with 33594 parameters in SELECT IN (xx,xxx,xx) but runs hours with 33595 or more parameters, why?
We had a small malfunction which generated unexpected amount of data for a device. Our code ends up creating a select query along the lines of
SELEXT * from XXX WHERE yyy IN (xxx,xxx,xxx....,xxxx)
...
0
votes
1
answer
55
views
Mariadb vs Mysql: Sum aggregate select with outer query column fails in MariaDB
I am having an issue migrating from MySQL to MariaDB 10.11.
My query gets an order by an id and calculates the balance for the order by summing the transactions/taxes and discounts via a select ...
1
vote
2
answers
113
views
combine multiple sql with same FROM clause and diff where clause
I am relatively new when working with queries so let me apologize in advance.
my sql is
$sql1 = SELECT
AVG( ST_Distance_Sphere( point( `event`.`event_location_longitude`, `event`.`...
1
vote
2
answers
46
views
Name of SQL programming style where KEYWORDS | CRITERIA are justified to centering line
Is there a name for this kind of SQL programming style?
--Courtesy of Markus Winand of modern-sql.com/
SELECT *
FROM (SELECT cities.*
, ROW_NUMBER() OVER(PARTITION BY country ORDER BY ...
0
votes
1
answer
611
views
MySQL Select query for a calculated column that uses previous row value for current row
I am stuck at this. Trying to help PM team to translate a calculation from Excel using a MySQL select query to do reporting. Your help is appreciated.
Table has 3 columns - Employee, Month, Salary. ...
3
votes
1
answer
114
views
Selecting second highest value using two SQL queries returns different results
I have the following tables from which I am to select the second highest salary:
Table 1:
id
salary
1
100
2
200
3
300
Table 2:
id
salary
1
100
I wrote two SQL queries to achieve this:
Query 1:
...
0
votes
2
answers
705
views
PostgreSQL, INSERT INTO SELECT DISTINCT generates more rows?
I have a large PostgreSQL table (NOT indexed) with duplicates so I ran the following query to eventually end up with a unique rows table:
INSERT INTO newTable(Field1, Field2, Field3)
SELECT DISTINCT ...
0
votes
1
answer
27
views
Fetch distinct / different from previos row Data from MySQL Table
I have a simple Table. It contains an ID, a Temperature and a Date. Every 10 Seconds a Sensor writes a Row with the measured Temperature-Value.
What I need is the Data, where the temperature changed. ...
1
vote
1
answer
91
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 ...
0
votes
2
answers
1k
views
MySQL select query slow despite having indexes
Have this rather simple SELECT on just one table:
SELECT id AS price_id
FROM prices
WHERE product_id IS NOT NULL AND product_id > 1
AND search_name_updated IS NULL
LIMIT 1000
For some reason, the ...
0
votes
1
answer
23
views
complex SELECT across 2 tables [closed]
Given these 2 tables, how would I write a SQL query to show the teams names with its number of riders?
CREATE TABLE `Rider` (
`dorsal` int NOT NULL,
`age` int DEFAULT NULL,
`name` varchar(50) ...
0
votes
1
answer
66
views
PostgreSQL INNER JOIN With Most Current Records
I am trying to create this VIEW:
CREATE VIEW highlight_on_rss_feed AS
SELECT highlight_on_article.title AS title,
highlight_on_meta_description.meta_description AS description,
concat('...
2
votes
1
answer
674
views
How do I get the date next week in SQL (Firebird)?
I want to get a date using an SQL statement. For instance, if the date is '2018-08-07' output this should return: '2018-08-14' (one week later). Then I'm going to get the total rows between these ...
2
votes
0
answers
76
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 ...
0
votes
1
answer
74
views
PostgreSQL: Generate rows for missing years within range (system year + 9)
I have an PostgreSQL 13.6 table that has rows for certain years:
with data (year_, amount) as (
select 2024, 100 union all
select 2025, 200 union all
select 2025, 300 union all
select 2026, 400 union ...
3
votes
1
answer
590
views
SQL Server: Generate rows for missing years within range (system year + 9)
I have an SQL Server 2019 table that has rows for certain years:
with data (year_, amount) as (
select 2024, 100 union all
select 2025, 200 union all
select 2025, 300 union all
select 2026, 400 union ...
-2
votes
1
answer
960
views
SQL Select Query not showing data in the statement
I have a very simple table that links ITEM numbers to GL Codes, I am running into a problem with my SQL Query not showing me every ITEM number in my statement
Here is my table
ITEM ITEM_NAME GL ...
2
votes
3
answers
1k
views
select records with condition on other field?
Maybe someone can help.
id
status
1
2
1
2
1
4
2
2
2
2
2
1
I try to select all ids, with ALL status equal to 2 or 4.
So it should result id = 1, because all status with id=1 are 2 or 4.
How can ...
4
votes
1
answer
1k
views
MySQL | Set Column default with value of an existing database that is a SELECT result
Problem
Having an exsiting database, a we need to add a column to a table, this column being of type int, is there a way to set the default initial value a result of a query in the database?
Just to ...