Questions tagged [row]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
-2 votes
1 answer
36 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 ...
user avatar
  • 1
0 votes
0 answers
20 views

Update Row --- get rid of row lock for perfomance? (postgres)

I need to update table A with the earliest "event" from Table B (event_time), matching on an ID. A might have 1000 ids ... and B has a bunch of foreign ids and linked events. So each day, a ...
user avatar
  • 1,493
-2 votes
1 answer
127 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: ...
user avatar
0 votes
1 answer
30 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 ...
user avatar
  • 1,373
0 votes
1 answer
62 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 ...
user avatar
0 votes
0 answers
39 views

Which DBs hide rows INSERT order (header metadata related)?

I discovered that PostgreSQL has a "t_xmin" row header metadata field that is used to store the transaction ID of an INSERT command. So, as I understand, even if I use a random INT primary ...
user avatar
0 votes
1 answer
613 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 ...
user avatar
  • 117
1 vote
2 answers
56 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" ...
user avatar
  • 113
0 votes
1 answer
466 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 ...
user avatar
0 votes
0 answers
35 views

Good way to limit access of rows different for different users? (Postgres)

I am writing a closed application in which a user enters his db-connection details and then all queries are hard coded. Let's assume I got a table "tbl" with 1000 entries (rows). One day the ...
user avatar
2 votes
1 answer
145 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 ...
user avatar
1 vote
1 answer
424 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 ...
user avatar
0 votes
1 answer
72 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 ...
user avatar
0 votes
1 answer
76 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 ...
user avatar
1 vote
1 answer
45 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 ...
user avatar
0 votes
1 answer
43 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 ...
user avatar
  • 135
0 votes
1 answer
27 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 ...
user avatar
0 votes
0 answers
1k 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 ...
user avatar
  • 333
1 vote
2 answers
914 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, ...
user avatar
  • 868
0 votes
1 answer
588 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 ...
user avatar
0 votes
1 answer
53 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, ...
user avatar
  • 101
1 vote
1 answer
36 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 ...
user avatar
0 votes
1 answer
3k 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 ...
user avatar
  • 868
0 votes
1 answer
42 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]( [...
user avatar
  • 15
0 votes
1 answer
125 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. ...
user avatar
0 votes
1 answer
351 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, ...
user avatar
  • 55.7k
0 votes
3 answers
2k 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 ...
user avatar
  • 325
1 vote
1 answer
3k 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 ...
user avatar
0 votes
1 answer
88 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 ...
user avatar
  • 1
4 votes
3 answers
2k 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 ( ...
user avatar
  • 55.7k
6 votes
1 answer
1k 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 ...
user avatar
  • 55.7k
2 votes
1 answer
1k 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 (...
user avatar
2 votes
1 answer
245 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 ...
user avatar
0 votes
2 answers
144 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 ...
user avatar
2 votes
1 answer
323 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 ...
user avatar
2 votes
2 answers
110 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 ...
user avatar
0 votes
1 answer
943 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 ...
user avatar
0 votes
2 answers
826 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 ...
user avatar
25 votes
4 answers
33k 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....
user avatar
  • 437
2 votes
1 answer
8k 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 ...
user avatar
2 votes
1 answer
1k 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 ...
user avatar
  • 137
2 votes
2 answers
154 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) ...
user avatar
  • 1,051
0 votes
1 answer
3k 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 ...
user avatar
7 votes
2 answers
436 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 ...
user avatar
  • 73
2 votes
1 answer
100 views

I want to select all records for those persons who have one or more entries that equals zero dues

DUES_TABLE ID LNAME FNAME DUES DATE 3456 Smith Bob 12.50 2014-10-01 3456 Smith Bob 0.00 2014-11-01 3456 Smith Bob 12.50 2014-12-01 9450 Jones ...
user avatar
  • 323
2 votes
4 answers
5k views

MSSQL Select all parent rows for which exists a combination of children rows with desired value

I would like to select all parent rows for which exists a combination of children rows with desired value. The easiest approach is to use multiple exists clauses, but this is not optimal - anyone ...
user avatar
  • 99
4 votes
2 answers
6k views

SQLite3 - remove duplicates by multiple columns

I'm looking for the most efficient way to remove duplicates from a table where id's are unique but there are equal rows when you check other columns. Example: id name age x 1 peter 25 ...
user avatar
  • 143
0 votes
1 answer
5k views

SELECT ROWS WITH SIMILAR ID AND MERGE THE FILED VALUES IN ONE

My situation is as follows. I have the query below and the query is doing its job pretty well. However I have excluded one able from it which is field_data_field_luogo and the rows inside this can ...
user avatar
1 vote
1 answer
1k views

Which terms are used to refer a row in database?

I am creating a Java application, in which I want to create a class that will relate to a row in database. I decided to use Entity while naming the class with a prefix/suffix. But, This page says: ...
user avatar
  • 113
12 votes
5 answers
62k views

Calculate row value based on previous and actual row values

Hi everyone and thanks for your help. I have the following situation: a table called statements that contains fields id(int), stmnt_date(date), debit(double), credit(double) and balance(double) I ...
user avatar