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!!

Thank you in advance.

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; 

enter image description here

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:

enter image description here

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; } 

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?