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?

What is the best way to allow users select items from a very long list in a responsive web app?


Scenario

I’m designing a web app where the user adds predefined items from List A to a custom List B.
List A is over 100 items sorted alphabetically.
List B can be customized according to user needs.

I designed a drag-and-drop solution for the web app. The user can jump to List A items alphabetically by clicking a letter.

drag and drop feature

Problem

This drag-and-drop feature doesn’t work on tablets very well and is impossible for smartphones. So I designed a select form for mobile devices: When the user taps on a list item in List B, a picker/flipper populated with List A items is triggered.

mobile view select

Question

Is there a way to implement alphabetical filtering in a web app’s mobile interface?

How can the mobile interface for this feature solve the following requirements?

  1. Allow user filter List A
  2. Add items from List A to List B

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?

Using WITH together with CREATE TEMPORARY TABLE AS SELECT … in Postgres?

I would like to create a temporary table using a select statement that uses temporary views specified in a WITH statement (not the WITH statement of CREATE TEMPORARY TABLE).

e.g. something along the lines of

WITH a AS ( SELECT 1 foo, 2 bar  ), b AS ( SELECT 4 bar, 5 baz ) CREATE TEMPORARY TABLE foo AS SELECT * from a JOIN b ON (a.bar=b.bar) 

If I comment out the CREATE TEMPORARY TABLE line, this works. How to create a temporary table using the query result of the SELECT, without rewriting the temporary views into a single query?

Country Select with region filters

I’m tinkering with a design for user to select a single country, or multiple countries that are historically/culturally grouped (eg United Kingdom).

Just looking for feedback, and wondering if anyone has seen something along these lines.

Dropdown 1: Region (whole world, Europe, Nth America, Sth America, SE Asia etc)

Drowdown 2: Country (all, then list of appropriate countries based on dropdown 1)

The user first selects a region (eg Europe) then an Country (eg Germany).

If Country=All, then the search results will return all countries in the selected region (or all countries if region=whole world).

Changing region changes the options in the Country list.

One issue I have is how to deal with regions like United Kingdom. Ignoring Brexit, the UK countries are also in the Europe region. One country being in multiple regions is not a problem. The problem is there are quite a few regions like this (UK, British Isles, Balkans, Caribbean etc). I can absolutely see users wanting to have results returned for “all countries in the UK”.

Should I just have lots of regions? All world, continents (EU, Africa etc), then these special subsets (UK etc)? Or would you go All world, then Continents+SpecialRegions [sorted a-z]?

‘Select All Visible’ vs ‘Select All’ with pagination

Suppose I have a table with pagination due a lot of row entries. Should the default action for ‘Select all’ select the entries on the current page or all entries in the entire table (i.e. all pages)?

What is the intended behaviour for a user?

I am aware of the solution that Gmail has to address this where it selects the ones in view and prompts an action to select all. Just wondering where I could find some other examples of products tackling this.

Show/Hide Select All Tree Checkboxes

We are using a 3-level nav tree (Sport-Country-Competition) with nodes being selected only on the third level.

Upon running usability tests, we identified that the absence of a “select all” option is a pain point for our users. Should a user wants to select all competitions from a Sport-Country, he has to select one by one each node, which results in an unnecessary interaction cost.

We are considering replacing the country flag icon with a checkbox, should the user mouse over the country node; this way the user will check the country checkbox for all competitions to be selected.

My questions below:

  1. Revealing/Hiding the Select All checkbox will promote recall over recognition; will the impact be considerably bigger from the benefit?
  2. Should we expand the country node upon selecting it to show all children being selected instantly?
  3. Any other alternatives/suggestions we can think of?

Current State without Select All

What are alternatives to a large drop down select list?

I am adding a single selection drop-down containing a list of all countries (around 200+ items).

In Windows it works fine as it displays 10-15 countries at a time and the scroll bar shows up. On the Mac, the drop-down shows all the items at once. The users have to scroll down through all items to look for their desired item.

I am not using a text-field as it may require spell check.

What are alternatives to a large drop-down select list that will work similarly across computers?

How to select a SharePoint people picker using JQuery

Im trying to select a sharepoint peoplepicker element in my form list.

i tried:

$ ("#ctl00_ctl41_g_326d802c_cd66_443c_9312_18e0ff9786b9_ctl00_iwPermIterator_ctl19_ctl00_ctl00_ctl04_ctl00_ctl00_UserField_upLevelDiv")

But i had issues previously where the id would change, so that would not be a solution.

I also tried,

$  ("div[title='People Picker']") 

But it would select 3 people pickers in my form list

Therefore, can someone suggest a way to select this element.

Best approach to having a sort, select and set of action buttons on a table row

Okay so I have a bit of an odd table to achieve and I’m unsure on the the best UI/UX approach to go with.

I have a small table of files, usually at most there would be about 10 files, one of the files must be toggled as a primary file to be used for the category it belongs to. It’s possible to toggle old files as active so each row will have a radio select.

The files will also need to be draggable so that they can be dragged to other file categories. So i need a handle bar for dragging of some sort.

Below I have an example table row labelled to it’s content:

+-------------+--------------+--------------+-------------+ | Grab Handle | Active Radio | file details | edit delete | +-------------+--------------+--------------+-------------+ 

So I’m asking for a few reasons, first is that i know as a general rule it’s best practice to put all action buttons on the right so this may even include the active radio however I’ve seen some tables with selects on the far left except it’s odd to have a handle bar and select next to each other. So would there be a better approach to this or is this fine?