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
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 ...
user1589188's user avatar
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-...
John Bachir's user avatar
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 ...
WolfiG's user avatar
  • 103
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 ...
Daniele's user avatar
  • 113
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-...
Ajax's user avatar
  • 111
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 = ...
Brunaldo's user avatar
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 * ...
Simon's user avatar
  • 103
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 ...
Erik's user avatar
  • 1
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 ...
jvkloc's user avatar
  • 133
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 ...
user1508072's user avatar
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 ...
Adrian M 's user avatar
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 &...
Carolina's user avatar
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 ...
Sarah Lewis's user avatar
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 = ...
Andreas Hinderberger's user avatar
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. ...
user1879840's user avatar
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 (...
ntruter42's user avatar
  • 121
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 ...
datascinalyst's user avatar
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 ...
Gary U.U. Unixuser's user avatar
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 ...
Bogdan Floareș's user avatar
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 ...
Himanshuman's user avatar
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-...
Mofarah's user avatar
  • 15
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 ...
Webzber's user avatar
  • 11
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 ...
Putnik's user avatar
  • 285
-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: ...
BAK's user avatar
  • 99
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 ...
Les's user avatar
  • 1
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 ...
Benito's user avatar
  • 1
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 ...
Roshan Upreti's user avatar
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 ...
Tom Tom's user avatar
  • 21
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 ...
Rinkratt's user avatar
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.
sh3hz's user avatar
  • 1
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 ...
FiFT's user avatar
  • 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) ...
Tuxie's user avatar
  • 182
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 ...
Kalki Truth's user avatar
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`.`...
Rupam Jaiswal's user avatar
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 ...
User1974's user avatar
  • 1,457
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. ...
Vineeth's user avatar
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: ...
Imad Ahmad's user avatar
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 ...
Anvil's user avatar
  • 11
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. ...
user39063's user avatar
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 ...
ILCAI's user avatar
  • 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 ...
Mads's user avatar
  • 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) ...
hikari's user avatar
  • 101
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('...
Ron Piggott's user avatar
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 ...
sunShine's user avatar
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 ...
Gizaw Simeon's user avatar
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 ...
User1974's user avatar
  • 1,457
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 ...
User1974's user avatar
  • 1,457
-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 ...
Steadyz's user avatar
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 ...
pileo's user avatar
  • 23
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 ...
Federico Baù's user avatar

1
2 3 4 5
22