## How can I lock rows for update without returning data over the connection?

I want to do something like this:

begin select * from foos where owner_id=123 and unread=true for update update foos set unread=false where owner_id=123 end 

In the statement where I acquire the lock, I don’t need any info about the rows. I just want to lock those particular rows. Is there a way to do this (elegant or hacky) which tells postgres to not do any of the work of actually giving me the data?

## How to spawn enemy by individual rows, scrolling downward the screen [using 2D DirectX,C]

I’m a beginner to the game development and just started making one 6 months ago. I’m creating a 2D top-down scrolling shooter game using DirectX9 and C Language.(I know it’s an old one to use but have no choice since it’s a school project).

The result I want:

• The enemies will be scrolled down by endless rows from top until the screen ends

The problem I’m having:

Currently I’ve managed to spawn the enemies in just one single row. I don’t know how to cut and sort them into 8 enemies in each single row. I really need to know the idea to sort this out and hope someone help me figure this out!!

## Select distinct combinations of rows

I try to select distinct combinations of rows (author_id, recipient_id), preserving following order and limit. Like on Facebook you have a list of recent conversations with given users. For example I’m a user with id=665 and I need a list of 20 recent conversations. My current code:

select * from (   select distinct on (author_id, recipient_id) * from private_message where author_id=665 or recipient_id=665 ) p_m order by id desc limit 20; 

In this case everything is ok besides that I want to keep bigger id (more recent), so message with id=10 needs to be drop, because it’s duplicate of id=18.

So the expected result is:

## Find the minimal subset of rows of some matrix such that the sum of each column over this rows exceeds some threshold

Let $$A$$ be a an $$n\times m$$ real valued matrix. The problem is to find the minimal subset $$I$$ of rows (if there is any) such that the sum of each column $$j$$ over the corresponding rows exceeds some threshold $$t_j$$, i.e. $$\sum_{i\in I}A[i,j]>t_j$$ for all $$j\in\{1,\dots m\}$$.

Or, stated as optimization problem:

Let $$A\in\mathbb{R}^{n\times m}, t\in\mathbb{R}^m$$. Now solve \begin{align}\min_{\xi\in\{0,1\}^n}&\sum_{i=1}^n\xi_i\\text{s.t.}&\,A^\top\xi>t\,.\end{align}

Actually, i would need a solution only for $$m=2$$, but the general might be interesting too.

## SQL: How to convert rows to column for every n’th row by ID

I’m trying to convert rows to columns.

I have the following columns: user_id, meta_key, meta_value

i have the following rows: 'first_name', 'last_name', 'stillingsbetegnelse'

Right now i can generate the rows and coloumns sorted and from a sepcific user_id. In my case it’s from 15. This is with the following querery

> SELECT user_id, meta_key, meta_value FROM clk_f76242643e_wp_usermeta > ORDER BY user_id DESC; where meta_key IN ('first_name', 'last_name', > 'stillingsbetegnelse') AND user_id > 15; 

And the result is:

user_id     meta_key                meta_value 16          first_name              Laxgdfds 16          last_name               Narentdsaa 16          stillingsbetegnelse     Andet 18          first_name              Riedsa 18          last_name               Joe 18          stillingsbetegnelse     Andet 

(all information is anonymized)

How do i get the following output

first_name    last_name         stillingsbetegnelse Laxgdfds      Narentdsaa        Andet Riedsa        Joe               Andet 

I have tried methods with Pivot, but i’m getting an error

    select         p.*     from clk_f76242643e_wp_usermeta     pivot(         max([meta_value])         for [meta_key] in ([first_name], [last_name], [stillingsbetegnelse]) ) as p 

## Select rows within group before condition is met

I want to select rows within each group following a condition in time. I have the following table trajectory_data in PostgreSQL 10 with daily points for each group.

| traj_id | timestamp  | height | lat   | Lon    | |---------|------------|--------|-------|--------| | 1       | 2006-01-01 | 10     | 34.55 | -70.80 | | 1       | 2006-01-02 | 9      | 34.56 | -70.88 | | 1       | 2006-01-03 | 0      | 34.56 | -70.89 | | 1       | 2006-01-04 | 5      | 34.57 | -70.90 | | 1       | 2006-01-05 | 20     | 34.60 | -70.94 | | 2       | 2006-01-01 | 10     | 34.55 | -70.80 | | 2       | 2006-01-02 | 5      | 34.55 | -70.82 | | 2       | 2006-01-03 | 3      | 34.54 | -70.89 | | 2       | 2006-01-04 | 0      | 34.53 | -70.90 | | 2       | 2006-01-05 | 10     | 34.51 | -70.95 | | 3       | 2006-01-01 | 10     | 34.55 | -70.80 | | 3       | 2006-01-01 | 9      | 34.56 | -70.75 | | 3       | 2006-01-01 | 11     | 34.54 | -70.90 | | 3       | 2006-01-01 | 15     | 34.60 | -70.95 | | 3       | 2006-01-01 | 30     | 34.67 | -70.90 | 

I want to filter the data to get the rows within each group which height is greater than zero. But, is not as simple as just removing the zeros with a WHERE statement. I want to keep the all the rows before the group reach zero for the first time:

| traj_id | timestamp  | height | lat   | Lon    | |---------|------------|--------|-------|--------| | 1       | 2006-01-01 | 10     | 34.55 | -70.80 | | 1       | 2006-01-02 | 9      | 34.56 | -70.88 | | 2       | 2006-01-01 | 10     | 34.55 | -70.80 | | 2       | 2006-01-02 | 5      | 34.55 | -70.82 | | 2       | 2006-01-03 | 3      | 34.54 | -70.89 | | 3       | 2006-01-01 | 10     | 34.55 | -70.80 | | 3       | 2006-01-01 | 9      | 34.56 | -70.75 | | 3       | 2006-01-01 | 11     | 34.54 | -70.90 | | 3       | 2006-01-01 | 15     | 34.60 | -70.95 | | 3       | 2006-01-01 | 30     | 34.67 | -70.90 | 

In this example, groups 1 and 2 are filtered and the rows after zero are removed.

I know that I can run something like:

SELECT * FROM trajectory_data GROUP BY traj_id HAVING height > 0 

But, this query will keep rows that I do not necessarily want (i.e. rows where the group already reached zero), and I have no intentions of aggregating the data, just select rows. There is another way of tackling this?

## How to clear rows in Tetris?

I’m doing an java assignment on tetris and this little bit of code is giving me trouble. I can’t seem to figure out why my code for clearing completed lines is not working. Can some one please help me out?

public int clearLines() {     int numCleared = 0;      while(true)      {          int index = -1;          for(int j = 0; j< 22; j++)          {              int cnt = 0;              for(int i = 0; i < 10; i++)              {                  cnt += board[j][i] != 0 ? 1 : 0;               }              if(cnt == 10)              {                  index = j;                  break;              }          }          if(index == -1)          {              break;          }          int[][]temp = new int[22][10];          for(int i = 0; i < 22; i++)          {              for(int j = 0; j < 10; j++)              {                  temp[i][j] = board[i][j];              }          }          for(int i = 0; i < index + 1; i++)          {              for(int j = 0; j < 10; j++)              {                  if(i == 0)                  {                      board[i][j] = 0;                  }                  else                  {                      board[i][j] = temp[i - 1][j];                  }              }          }          linesCleared++;          numCleared++;      }      return numCleared; } 

## Get the CreatedAt Timestamp when the number of rows is equal to some number

I have a simple table in sql server.

id, OtherId, createdAt 1, A, 2019-11-28 05:03:03.983 2, A, 2019-11-28 05:03:06.937 3, B, 2019-11-28 05:03:12.983 

What I want to retrieve is a timestamp when the number of rows became a fixed number let us say 10 where OtherId is A

## What’s the best method to group/ungroup rows in a data table?

I’ve been struggling with a good solution to group/ungroup rows in a data table. We’ve initially implemented drag-n-drop but that was a nightmare to implement due to 3rd-party library restrictions, & a nightmare to use for end users.

I’ve come up with a different method that’s hopefully more straightforward to use, but I’m still not 100% happy with it. Check out the demo below and let me know how I could improve it.

Some items on my list:

• Make it more obvious what clicking on the checkbox will do. Maybe an edit mode or a tooltip?
• Somehow streamline the ways the “Group Items” buttons are displayed.

One other idea is to mimic how one would group/ungroup elements with a combination of Shift+Select elements > Right Click > Group/Ungroup and keyboard commands. However, with this being a browser app that’d also support touch-devices AND is geared toward an older demographic, I’m not sure if that’s a good idea.

YOUTUBE DEMO: Grouping & Ungrouping Rows in Data Table Demo

## How to select rows from two matrices to have one larger?

You are given two matrices $$\mathbf{A}=\begin{pmatrix}a_{11} & \ldots & a_{1n}\\vdots & \ddots & \vdots\a_{m1} & \ldots & a_{mn}\\end{pmatrix},$$ and $$\mathbf{B}=\begin{pmatrix}b_{11} & \ldots & b_{1n}\\vdots & \ddots & \vdots\b_{m1} & \ldots & b_{mn}\\end{pmatrix},$$ of positive numbers.

We want to find, for $$1\leq j\leq n$$, a subset $${S}_j\subset\{1,2,\ldots,m\}$$ of at most two rows such that $${S}_i\cap{S}_j=\emptyset$$ (for $$i\ne j$$) and $$\left|\bigcup_{j=1}^n{S}_j\right|$$ is as large as possible and for $$i,i’\in{S}_j$$, we have: $$a_{ij}\geq b_{i’j}.$$

I started by reducing the problem to a maximum matching problem. If we create $$m$$ nodes. For each column $$1\leq j\leq n$$, we create an edge between node $$1\leq i\leq m$$ and node $$1\leq i’\leq m$$ ($$i\ne i’$$) if there is no edge already and if $$a_{ij}\geq b_{i’j}$$. Next, run a matching algorithm on the created graph to find a maximum-cardinality matching $${M}$$.

Do you have any hints on how to solve the problem? Am I on the right direction?