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,049
questions
1
vote
2
answers
24
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
41
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,417
0
votes
1
answer
26
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
35
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
41
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
23
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
32
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
60
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
21
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
44
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
20
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
202
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
36
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
39
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,417
3
votes
1
answer
215
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,417
-2
votes
1
answer
43
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
437
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
39
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
55
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
35
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
62
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
53
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
35
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
269
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
501
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
215
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 ...
- 163
0
votes
1
answer
138
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
0
votes
1
answer
82
views
Data normalization on insertion
Please excuse the noob question but SQL is not where my strength lies.
Let's say that I have a table where I am storing users' data. Typically there are columns of type TEXT such as name, email...
e.g....
- 103
1
vote
1
answer
205
views
Why is bcp out much faster than select * even when bcp uses select * internally?
I tried exporting a very large table (5B rows) to a csv file using both Select * and bcp out.
Regarding Select *, I am using pyodbc module to query the database and writing it to a csv file. I also ...
- 23
2
votes
1
answer
71
views
Is it possible to INTERSECT on a subset of columns?
Short version
Per the title: would it be possible to have two SELECT statements with different columns, and call INTERSECT considering only the common columns?
Long version
Keeping the XY problem in ...
- 71
2
votes
1
answer
125
views
Converting 2 select clauses into 1 select
I'm making a "bom" list entry into the "materials" table.
I bring the materials defined for the product group to the grid so that users do not forget the relevant material.
Users ...
- 91
1
vote
1
answer
84
views
Column-wise UNION, or ordering two columns of SELECT/JOIN query with distinct orderings
I have a table, employee, and one of the columns is last_name, which stores strings.
Using a SELECT query, I am trying to get the last_name column, ordered alphabetically, as one column, and another ...
- 111
0
votes
0
answers
17
views
P&T: which approach to take with app constantly creating new tables
A customer is complaining about low performance.
No cpu or memory bottlenecks observed on the system.
The problem is that their app is constantly creating new tables that are used for a while, then ...
- 9
-2
votes
1
answer
51
views
gives error when SET variable in SELECT environment in SQL Server 2019
In the company where I work, many products are produced with names CODE1, CODE2, etc.
I have to write the function related to the "production cost" of these products.
Each IF section in code ...
- 97
1
vote
2
answers
175
views
Rownum equivalent in Informix SQL?
I want to explore (=looking at the first 100 rows) databases using the Informix SQL dialect.
In Oracle SQL I would use SELCT* FROM table_name WHERE ROWNUM < 100
In Postgress SQL I would use SELCT* ...
- 121
3
votes
1
answer
83
views
Maria DB - Is there a point in indexing each uniq index cols separately?
We are running 10.2.44-MariaDB-log MariaDB Server. Right now I am creating tables with this structure
Primary('id')
UniqueIndex('colA', 'colB', 'colc');
Index('cola')
Index('colb')
Index('colc')
I ...
- 249
0
votes
2
answers
139
views
Mysql how to help cleanse data, specifically if contains text remove
I have a column as:
Size
75cl
70im
7im
30cim
30clim
And I need to convert the text to numbers but conditionally:
Size
75cl
70
7
30
30cl
Where the size column includes "im" then ...
- 111
1
vote
3
answers
587
views
MySQL: Improve performance on large table for dynamically built query with composite index correctly
Assuming that I have a MySQL table with ~ 30 million entries and 40 columns I have an highly active query (5 queries/second) which is quite slow (avg. ~ 20 seconds) and has a high number of rows ...
- 215
-1
votes
2
answers
76
views
How to write query to select highest value from same date
please help me write this query for MySQL.
I want to select highest value from same datetime. So if there are multiple equal datetime value, return only one row with highest price.
For summary, one ...
- 83
0
votes
0
answers
59
views
speeding up mysql select's
I've a coldfusion application that currently contains just under 5,000 records on one table.
I've a query that selects around 3800 records from that table.
There are several queries that retrieve over ...
- 19
1
vote
1
answer
101
views
How do I SELECT elements using WHERE with another table's record values in WHERE statement?
Problem summary
Today part of my app crashed, but nothing else stopped with it. I need to find the first and last tracked message ids for each channel during the downtime. I log unix timestamps ...
- 13
0
votes
1
answer
24
views
Select for result both of two column values
Hoping for syntax to select for result that has both of 2 possible column values.
Right now I'm doing
select distinct f1,f2,f3 from table where f3 in ('val1','val2')
and outputting the text file and ...
3
votes
2
answers
1k
views
MySQL: How to optimize a certain SELECT statement which causes very high load?
Having a table with 25.000.000 entries, I have the following query which is started every ~ every 2 seconds which causes very high loads (up to 40 AAS in Amazon AWS). The execution takes 20 seconds up ...
- 215
0
votes
2
answers
196
views
MariaDB: can a SELECT query block other queries?
If I used a stored procedure having only Select queries in mariadb (NO Update OR Insert). Does this Select locks other queries (Select, Update or Insert) using the same tables?
- 11
4
votes
4
answers
3k
views
Select only rows where all values in a column match a value for an id
Given the table
seq_id
seq_status
1
A
1
B
2
A
2
A
3
A
4
B
5
C
I want to select the distinct seq_id's where the seq_status is A
(2 and 3) and not return if one of the values is different
so ...
- 49
0
votes
1
answer
645
views
Postgresql Search for a 6 digit number in text
I have a table with one field Address - varchar(250), is it possible to search this field for a 6 digit (integer) Pincode. Is it also possible to list out records without any 12 digit Pincode.
Sl No |...
2
votes
1
answer
1k
views
How do I create a SQL loop that generates future dates based on different frequencies and intervals for each item until a specified end date?
I have a data set that appears like this:
next_generation_date
procedure
interval
frequency
2021-01-17 00:00:00.000
Clean Restroom
1
day
2021-01-17 00:00:00.000
Vacuum
2
week
2021-02-01 00:00:00....
- 21
0
votes
1
answer
24
views
Select function-based index data as rows in a query?
I'm trying to learn about indexes in Oracle 18c.
As a novice, it's occurred to me that it might be interesting to view the actual data that's in a function-based index:
create index ...
- 1,417
0
votes
1
answer
281
views
Display count of rows from one table in columns in another without knowing how many columns there would be
Let's say I have a Library database and the following tables in it:
Books(ID, Title, Author, PublihserID, LanguageCode, Genre, DatePublished, ISBN) - contains examples of books as abstract items, ...
- 103
1
vote
0
answers
461
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 ...
- 149