Questions tagged [row]
The row tag has no usage guidance.
72
questions
2
votes
1
answer
160
views
How can I paginate when ordering by `date_bin`?
I have the following query
SELECT u.update_time, about_me
FROM users u
ORDER BY date_bin('14 days', u.update_time, '2023-04-07 23:11:56.471560Z') DESC, LENGTH(u.about_me) DESC, u.user_id;
I get the ...
- 145
0
votes
2
answers
1k
views
Postgresql: Trigger is not working at times
I have a PostgreSQL trigger that is not firing sometimes, even though the status is always shown as "enabled".
My trigger code is as follows:
CREATE OR REPLACE FUNCTION audit_src_exhibit() ...
- 13
0
votes
1
answer
313
views
Postgresql: Trigger is not working at times
I have a PostgreSQL trigger that is not firing sometimes, even though the status is always shown as "enabled".
My trigger code is as follows:
CREATE OR REPLACE FUNCTION audit_src_exhibit() ...
- 13
1
vote
1
answer
297
views
Best way to cast a VIEW row type to the underlying TABLE row type
I have a table with an index and a row-level security policy. Due to this problem (more details: 1, 2, 3, 4, 5), the index is not used when the policy applies, which makes my queries unbearably slow.
...
- 505
1
vote
2
answers
504
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
2
votes
1
answer
64
views
Insert rows into a table from another table row?
I've got a table Schedule
Id
unserId
Sunday
Monday
Tuesday
Wednesday
Thursday
Friday
Saturday
0
10
0
1
1
1
1
0
0
1
20
1
0
0
0
0
0
0
I need to insert lines from one row in table Schedule into seven ...
4
votes
5
answers
3k
views
How to update table records in reverse order?
I've a table Student
Id Name Mark
1 Medi 10
2 Ibra 15
3 Simo 20
and I want to update it, where I want to reverse it in descending order only Name and Mark and keep Id in its order:
...
0
votes
2
answers
3k
views
How to limit the number of rows in a mysql table
How can I set a limit of rows to a mysql table? (I am using php)
POSSIBLE METHOD-1:
When the user tries to SIGN-UP I can select everything from the TABLE and get the number of ROWS in the TABLE and ...
user253088
-2
votes
1
answer
1k
views
split a row value according to a specific column
I have total value in table A and need to get row value in table B until reached value in table A.
Table A
ID
Available
1
99
2
0
Table B
ID
Rack
RequiredQty
1
A
60
1
B
102
1
C
8
2
A
10
...
- 1
-2
votes
1
answer
967
views
Count rows from multiple tables with similar names
I want to have 2 columns: Table Name and Row Count
When I run this, I get 12 table names in my database. How do I then count the rows in each one without having to union 12 count queries together:
...
0
votes
1
answer
50
views
Can analytic functions add or remove rows from a resultset?
I'm a novice who is starting to learn about Oracle analytic functions (window functions).
I know from experience that it's important to fully understand how SQL mechanisms work when using them in ...
- 1,447
0
votes
1
answer
168
views
MySQL 8.0.20 - Master Replica scheme, errors during replication process
This threads follows a previous one, given at this URL: MySQL 8.0.20 - Master Replica scheme, increasing delay between Source and Replica
Replica Server has been configured this way, which ensures a ...
- 13
1
vote
1
answer
4k
views
How to check for row lock level when FOR UPDATE is used in PostgreSQL?
Related to previous question here
From the pg_locks documentation, row level lock information is not available when FOR UPDATE is used in a SELECT statement.
Is there any way to check for row level ...
- 127
1
vote
2
answers
236
views
Mysql: Query which orders by default and rank (pin/hold up entries?)
I have a products table which contains all my products. Those products table gets filled permanently with new products. However, I want to have the possibility to "hold up"/"pin" ...
- 113
0
votes
1
answer
986
views
Postgres: Find previous and next row or existing value created by SERIAL colum for separate query
I got a Postgres 13 DB and a table with an id based on a SERIAL. After deleting rows such a sequence would have gaps.
In each row binary data is stored too and I would like to process it row after row ...
2
votes
1
answer
370
views
Why is the ROW keyword needed when constructing a row with one element, but not two?
In PostgreSQL 11, the following expression
UPDATE test_table SET ("column_a","column_b") = ('value-a','value-b') WHERE "column_a" = 'value-c'
is accepted and performs ...
- 121
1
vote
1
answer
739
views
How to alter tables safely to set row_format as Compressed or Dynamic in Mariadb Galera Cluster?
We have some tables with row_format=Compact. The biggest table is around 30Gb, so how safely i can alter tables and set the row format as Compressed for the big table and Dynamic for other three ...
0
votes
1
answer
261
views
SQL: Merge rows when value doesnt change in continuous time
I am new to SQL and I need to summarize a table that has this information: Serial Number, StartDate, EndDate, StatusID and TimeInState, what I need is to merge rows that keep the same StatusId during ...
0
votes
1
answer
229
views
How to get one row out of many rows with same column id
I have a use case in which I have three tables.
Courses Table
Course_ Id Name
1 MathMatics
2 Physics
Teacher Table
Teacher_ID Teacher_Name
1 AAA
2 BBB
TeacherVsCourses ...
1
vote
1
answer
46
views
Identify changes across records for the field ID
Consider the following table
TabName ID Gender FirstName
Tab1 C1 M John
Tab2 C1 M John
Tab3 C1 F John
Tab1 C2 ...
- 75
0
votes
1
answer
144
views
Update Mysql records using Same table but Based on same table record
I have table in which i want to update records based on it's 'wishlist_id' value.
Sample format of table records.
Expected table record after update.
So can you help to update this record using ...
- 135
0
votes
1
answer
30
views
combine rows from table with NUM columns
I have this table:
Product | Qtdy | Price
A | NULL | 123.00
A | 23 | NULL
How Can I represent convert like that?
Product | Qtdy | Price
A | 23 | 123.00
Which ...
0
votes
1
answer
2k
views
The connection was recovered and rowcount in the first query is not available. Please execute another query to get a valid rowcount
I get the error message as appears in the title and seen below when running a query against one of our SQL databases. The error seems to be consistent with this specific query when trying to return ...
- 353
2
votes
3
answers
1k
views
Fix Oracle Database chained rows
A Oracle 12c Database has a table with Chained rows:
select
owner c1,
table_name c2,
pct_free c3,
pct_used c4,
avg_row_len c5,
...
- 998
0
votes
1
answer
2k
views
Display all fields with null values in a particular row in SQL Server
I have a requirement where I want to fetch and display all the fields with NULL value for a particular row in the table.
Some thing like:
select 'all columns with NULL'
from table_xyz
where ...
- 1
0
votes
1
answer
62
views
Can I compress two rows with a difference in two columns into a single row with both columns?
I have a query that returns customer product information that shows which region a product is sourced from.
Query:
SELECT
prod.prod_id,
prod.is_source,
link.prod_link_disabled,
...
- 101
1
vote
1
answer
48
views
What happens if I load last 10 rows then next 10 after other rows are inserted?
What if I had a PHP script that had a pagination that loads last 10 rows then page two loaded next 10 in DESC order, but before loading next 10 on page 2, other 10 rows were added to the table?
What ...
- 15
0
votes
1
answer
4k
views
Oracle Database troubleshooting enq: TX - row lock contention
AWR Reports of a 12.1.0.2 EE Oracle Database shows enq: TX - row lock contention with up to 80% of DB Time. The application is huge with more than 500 tables and 100.000 registered users, on average ...
- 998
0
votes
1
answer
48
views
subtract missing Value from Last Value
Ok, so it's kind if difficult for me as a non-native english speaker to get this into writing, so please bear with me.
I have a source-Table as following:
CREATE TABLE [dbo].[Mailbox](
[...
- 15
0
votes
1
answer
296
views
How does DYNAMIC Row format handle NULL storage MYSQL 5.7?
As a newbie to Mysql I just can't understand how NULL values are stored in each row formats.
As per docs(last bullet-in), NULLs for fixed length columns (BIGINTs) also take their data space reserved. ...
- 654
1
vote
1
answer
929
views
IS NOT DISTINCT FROM vs row-wise equality with =
Looking at the below query,
SELECT null IS NOT DISTINCT FROM null AS indf,
null = null AS eq;
indf | eq
------+----
t |
From this we can see the result of the IS NOT DISTINCT FROM is true, ...
- 62.2k
0
votes
3
answers
3k
views
calculate duration between intervals of 15 minute [closed]
so i've been trying to find an answer for a problem i have.
i have a client with call information table.
the table look like this:
and he want me to create a new table that splits every record to ...
- 345
1
vote
1
answer
4k
views
SQL - sum columns with same name from diferent tables
I have 3 Tables:
_tbl1_____
ID C1 C2
-- -- --
AA 2 3
BB 10 12
CC 20 22
_tbl2_____
ID C1 C2
-- -- --
BB 10 12
AA 2 3
CC 20 22
_tbl3_____
ID C1 C2
-- -- --
AA 0 0
...
- 13
14
votes
3
answers
29k
views
How to insert 10000 new rows?
Presumably there is a straight forward and easy solution. I'm wanting to create 10000 new rows - that are numbered sequentially with no data per row (except sequentially numbered id).
I have used:
...
- 141
0
votes
1
answer
125
views
select items from multiple rows and add to one
I need some help on SQL as this kind of selecting is beyond my knowledge. The result of the select should be in one row as it is shown in the picture. Can someone provide any ideas on how to achieve ...
- 1
4
votes
3
answers
3k
views
Is there anyway to UPDATE a series of columns on a row with a composite type record/row?
Take a table foo,
CREATE TABLE foo(a,b,c,d)
AS VALUES
(1,2,3,4);
I can insert into foo with a row type..
INSERT INTO foo
SELECT (foo).*
FROM foo;
INSERT INTO foo
SELECT (v).*
FROM ( ...
- 62.2k
6
votes
1
answer
2k
views
Using a type in place of a column definition list?
Running the below, I get "a column definition list is required for functions returning "record"".
SELECT *
FROM json_to_record('{"a":1,"b":2,"c":3,"d":4}');
ERROR: a column definition list is ...
- 62.2k
2
votes
1
answer
2k
views
Inserting into a table a row represented by JSON
I've been laboring away trying to create a procedure which can data JSONB (or JSON, doesn't really matter), a table, and convert the JSON to a record and insert that record into the table. This is (...
- 23
2
votes
1
answer
624
views
Identifying field changes between tables and consecutive rows
I need to build an audit report to identify field changes between two tables, as well as between consecutive rows in one of the tables. The first table (data) holds the current data:
id data_id ...
- 23
0
votes
2
answers
249
views
How does a integer data record store in SQL Server page
I have seen the 8KB (8192 = 96 Header + 36 Row Offset + 8060 Free space) page architecture in SQL Server. When it comes to storing a data record in Page i am confused.
In Below table i have create ...
- 317
2
votes
1
answer
416
views
Page Number in SQL Server database is not starting from zero
I have a question on SQL Server page, I have created a new database with only one new table as below
Here is the Code:
CREATE TABLE PAGETEST
(
ID int primary key
)
When i see my staring page ...
- 317
2
votes
2
answers
149
views
Get the date interval from table rows, based on a start stop column
I have a table returning the following result set:
mydate | startstop
------------+----------
2018-02-07 | start
2018-02-14 | stop
2017-02-06 | start
2017-02-12 | stop
2016-02-05 | start
...
- 1,531
0
votes
1
answer
1k
views
Storing data in multiple rows vs serialized data
I am currently designing a database table where I store list of courses chosen by students for a session. My current design has to do with storing each course in a row and a student can have up to 30 ...
- 103
0
votes
2
answers
1k
views
Replication error from Mariadb 10.1 to Mysql 5.1/5.0/5/5 when master's logging format is set to row based
While replicating from Mariadb 10.1 to MySQL (5.0, 5.1, 5.5) or Mariadb (5.2, 5.5) lower versions, if master's binlog_format is set to row, the replication failure occurs with the following message ...
26
votes
4
answers
37k
views
How to limit maximum number of rows in a table to just 1
I have a configuration table in my SQL Server database and this table should only ever have one row. To help future developers understand this I'd like to prevent more than one row of data being added....
- 447
2
votes
1
answer
9k
views
Is it possible to perform skip N rows and fetch M rows without full scan
Let's say I've got this query:
SELECT EMP_ID,LAST_NAME FROM EMPLOYEES
WHERE POSITIONID IN (1,3) ORDER BY POSITIONID,LAST_NAME;
Now if I want to show only the first ten rows between 50 and 60, the ...
2
votes
1
answer
2k
views
Duplicating rows but incrementing the ID each time
I have the strenuous task of merging multiple Magento databases together and I am importing hundreds of blocks into the main database but subsequently have to go through every one and change the store ...
- 137
2
votes
2
answers
163
views
TSQL Generic Row number theoretical question
To start, We have a pretty complicated system that's been a compilation of over 20 years of code... And the code needs to work with SQL2005 all the way to SQL2014 (can't use the offset Fetch Next)
...
- 1,071
0
votes
1
answer
7k
views
Array of strings when updating a field
Unintentionally discovered that following query works in PostgreSQL:
UPDATE "sometable" SET "somefield" = ('string1', 'string2') WHERE "id" = 1;
I'm passing an array of strings to field and the ...
- 479
7
votes
2
answers
655
views
Create smaller row from alias and preserve column names
Using Postgres:
SELECT users."name" AS "name"
, array_to_json(array_agg(sites)) as sites
FROM remodel.users AS users
JOIN remodel.user_sites AS user_sites
ON users.id=user_sites.user
JOIN ...
- 73