Questions tagged [row]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
0
votes
2answers
486 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 ...
18
votes
6answers
50k views

Select columns inside json_agg

I have a query like: SELECT a.id, a.name, json_agg(b.*) as "item" FROM a JOIN b ON b.item_id = a.id GROUP BY a.id, a.name; How can I select the columns in b so I don't have b.item_id in the ...
0
votes
1answer
24 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
0answers
153 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 ...
0
votes
2answers
91 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, ...
22
votes
2answers
29k views

Postgres multiple columns to json

I am running postgresql 9.3.4. I have a table with 3 fields: id name addr --- ---- ---- 1 n1 ad1 2 n2 ad2 ... I need to move the data to a new table with fields like: id data --- ...
0
votes
1answer
57 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 ...
0
votes
1answer
46 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, ...
0
votes
1answer
651 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 ...
1
vote
1answer
30 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 ...
0
votes
1answer
38 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]( [...
0
votes
1answer
50 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. ...
1
vote
0answers
149 views

Which columns are off-paged (ROW_FORMAT=DYNAMIC)?

When going through the MySQL 5.7 manual, I found some contradictory facts. Fact 1: When a table is created with ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED, InnoDB can store long variable-length ...
0
votes
1answer
63 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, ...
9
votes
5answers
37k 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 ...
0
votes
1answer
1k 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 ...
0
votes
3answers
1k 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 ...
1
vote
1answer
2k 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 ...
1
vote
1answer
701 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 (...
0
votes
1answer
70 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 ...
4
votes
3answers
1k 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 ( ...
3
votes
1answer
715 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 ...
2
votes
1answer
144 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 ...
0
votes
2answers
90 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 ...
2
votes
1answer
178 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 ...
2
votes
2answers
87 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 ...
0
votes
1answer
499 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 ...
-3
votes
1answer
74 views

multiple row selection to a single row [closed]

I'm a beginner. 4 tables and result required is given below . I'm able to achieve the result partially with this query. (getting only uat date , not all 3) Please help to achieve entire result .. ...
1
vote
2answers
6k views

Multiple table rows in one row Informix

I have problem just like this one but in Informix. I have table with 2 attributes ID, email like this one: ID email 1 my_mail1@mail.com 2 my_mail2@mail.com 2 my_mail3@mail.com 2 my_mail4@...
21
votes
4answers
20k 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....
4
votes
2answers
9k views

Oracle ROWID, can we assume it is sequential in a insert-only scenario?

How safe is it to assume that ROWID is sequential or ascending when a table is only inserted to? and why? I had a situation where I had to verify whether new records were added to table which has no ...
2
votes
1answer
5k 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 ...
56
votes
8answers
25k views

What is the difference between a “record” and a “row” in SQL Server?

There was a rather innocuous question about adding dates and times in SQL Server that set off a rather fascinating taxonomic debate. So how do we differentiate between these related terms and how we ...
2
votes
1answer
354 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 ...
2
votes
0answers
108 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) ...
6
votes
2answers
208 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 ...
2
votes
4answers
4k 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 ...
2
votes
1answer
86 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 ...
0
votes
0answers
57 views

Calculating the row size of a table [duplicate]

Is there a way to calculate the row size (in bytes) of a particular table in postgresql / pgadmin3?
2
votes
1answer
5k 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 ...
0
votes
1answer
3k 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 ...
1
vote
1answer
745 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: ...
1
vote
0answers
31 views

PostreSQL execute a default query when no row are returned [duplicate]

I need to calculate the average score of a list of userId. I have a table "user_place_score" (user_id / place_id/ score). Sometimes I do not have the score for a user_id so I need to fetch a "...
1
vote
1answer
734 views

decrement all columns entry when row is deleted

I have a column in a table containing the row_number, some kind of id number to identify the row. When I delete one row, I need the other rows to have their row_number decremented, to keep them sorted....
3
votes
2answers
4k views

How to update ID=null values in table to incremental counter values?

On SQL Server 2012, i've got an intermediate/staging table for merging existing with new data, where I want to insert numeric IDs for newly created rows: ID NaturalID Comment 1 ...
0
votes
1answer
95 views

Coalesce per row - get all Detail IDs grouped per HeaderRow e.g. Invoice1 | 1,2,3 Invoice 2 | 4,5,6

I have the following demo data (taken from Adventureworks) SalesOrderNumber SalesOrderDetailID SO43659 12 SO43659 11 SO43659 8 SO43659 9 SO43660 ...
0
votes
1answer
95 views

Last used index from specific table

how can i take last used index from a specific table and add a +1 to it? database login, table cars I think i need to add it here #### $sql = 'INSERT INTO '.$this->options['db_table'] ...
5
votes
1answer
7k views

Increment value in row if it exists, otherwise create row with value?

Below is the MySQL table layout that I'm working with. Basically, I'd like to increment views, watchers, and inquiries as they occur through the front end of the website. My question is how would I ...
0
votes
1answer
1k views

migration from mysql 4.1.0 to mysql5.6.2

I have been trying to backup data with structure from my old legacy server to my local server for modifications. PhpMyAdmin timed out at the time of export, MySQL Workbench has performed connections ...
2
votes
1answer
713 views

Does a SQL server unique constraint partially or fully fail a multi-row write?

If some rows of a writing query fails a unique constraint, will only those writes be rejected, or will the entire query fail?