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.

Filter by
Sorted by
Tagged with
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. ...
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: ...
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('...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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: ...
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 ...
-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' ...
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 ...
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 ...
-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 ...
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 ...
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 ...
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 ...
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?
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 ...
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....
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, ...
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 ...

1
2 3 4 5
21