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,068
questions
0
votes
2
answers
45
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 &...
- 37
-1
votes
1
answer
19
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
votes
0
answers
71
views
SELECT query with ORDER BY is slow
A simple SELECT with ORDER BY on a table of 25 columns and 20 Lakh rows is slow, taking almost 40 seconds.
When ORDER BY is id (PK) the no issue takes around 0.003 seconds but when ORDER BY is on a ...
1
vote
1
answer
26
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
23
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
43
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 (...
- 121
0
votes
1
answer
36
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
66
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
25
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
1
answer
39
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 ...
- 197
0
votes
1
answer
34
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-...
- 11
1
vote
0
answers
83
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
0
votes
1
answer
103
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 ...
- 285
-1
votes
2
answers
45
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:
...
- 99
0
votes
1
answer
115
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
...
- 1
0
votes
1
answer
580
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 ...
- 1
0
votes
2
answers
53
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 ...
- 101
2
votes
1
answer
51
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 ...
- 21
0
votes
1
answer
31
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 ...
- 1
0
votes
1
answer
39
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.
- 1
0
votes
2
answers
44
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 ...
- 3
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)
...
- 183
0
votes
1
answer
50
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
101
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 ...
- 1,447
0
votes
1
answer
457
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. ...
- 1
3
votes
1
answer
88
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:
...
- 133
0
votes
2
answers
508
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 ...
- 11
0
votes
1
answer
26
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
68
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
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 ...
- 15
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) ...
- 101
0
votes
0
answers
152
views
How to list all columns the has a specific value on them as well as their corresponding table and schema [Mysql]
So I'm trying to determine which date/datetime/timestamp columns among all my schemas are having zero values on them (as well as the corresponding table and schema). This zero values I'm looking for ...
0
votes
1
answer
46
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('...
- 165
2
votes
1
answer
580
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 ...
- 35
1
vote
0
answers
67
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 ...
- 11
0
votes
1
answer
59
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 ...
- 1,447
3
votes
1
answer
493
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 ...
- 1,447
-2
votes
1
answer
630
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 ...
- 3
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 ...
- 23
0
votes
0
answers
138
views
Opencart 3 - Slow select query due to primary key value
Opencart 3 website is loading very slow at 24s for category pages. On debugging, it seems as if the issue is from here.
catalog/model/catalog/product.php - public function getProduct($product_id)
The ...
- 101
4
votes
1
answer
836
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 ...
- 145
0
votes
1
answer
57
views
MySQL | Related counter on one to many relation
Problem
Is there a way to automatically count related tables (one to Many), where the count will automatically increment / decrement depending if we add or remove a related item.
So, I could obviously ...
- 145
0
votes
1
answer
110
views
SQL : How to automatically run different SELECT query on a daily basis base on the current system date
I have 31 tables one for each day of the month where the table names are like subscription_x where x is the day of the month
On a daily it should run a select * from subscription_x to get data from ...
0
votes
0
answers
135
views
SELECT statement columns are truncated
I am running a SQL SELECT query to generate a CSV file, and some of the longer lines are getting truncated
|ty.cook|NCTE Interoperability Engineering|
|gerald.boursiquot|NCTE Interoperability Enginee.....
3
votes
1
answer
623
views
Returning table name and schema name along with column values
I have table in SQL:
public.employee
name
age
abc
20
xyz
30
I want a query which would return the column values along with schema name and table name.
Input:
select * from public.employee
Output:
...
- 31
4
votes
1
answer
602
views
Hide rows from all existing queries
I have a Postgres 14 database and want to implement the following scenario:
I recently added a column, is_hidden to the table foo. I have a couple of queries (around 15 read operations) in my ...
- 181
0
votes
3
answers
2k
views
Select rows where date exist in list. If not found, get next closest date
I have been trying to get records from a table by providing a list of dates. But some dates from that list doesn't exist in my table, so in that case I want to get the next closest date.
I used this ...
- 3
-1
votes
2
answers
1k
views
SELECT rows, but remove rows that have duplicate column
I believe this is a very simple query to make, but for some reason it doesn't work in Postgres (it works in other dbs)
This is how db schema kooks like:
tableA(
id integer,
...
)
tableB(
id ...
- 165
0
votes
1
answer
322
views
OPENJSON Insert Select by Row [closed]
I have some JSON which I can successfully insert into a table, but there are some values I need to perform addtional queries on per 'row' of json. I cannot seem to perform the select within the 'With' ...
- 173