Not certain how to title this question. Describing is a bit of a challenge, too. I have shaped my postgres data as follows, where a location and geo is further broken out into micro geo’s based on …
Tag: postgresql
Retrieve all records with the 5 most recent distinct dates
I need to retrieve data for a history module with pagination. The requirement is to show the records for the five most recent dates first (regardless of the number of rows) and load the remaining records in lots (next five dates, any number of rows) as requested by the user. I came up with the query below, which is extremely
Is there any way i can populate entity based on months but excluding the first month each loops?
i have a set of data sample like this | id | month | x | jan | x | feb | x | mar is there any way i can populate that sample to be like this? | id | month | number | x | jan | 1 | x | feb | …
count the 3 different values that a column can take
I’m trying to count the 3 different values that a column in a table can take, in a single SELECT. What am I doing wrong? I am using a postgresql table where the column named “tipoprova” is an integer Answer You are looking for filtered aggregation: Your query is wrong, because you have the wrong syntax for the CASE expression.
How to count number of groups?
I have a table as follow: I want to count the number of distinct g_n. (Here it’s 3.) I have tried with: But it gives me the number of items per group. How can I count the number of distinct g_n? Answer Use count(distinct): There is no need to aggregate using group by for this result. Note: This ignores NULL
using ORDER BY in SQL for chunks of data
I want to know how I sort data in a SQL query but only in certain chunks. I’ll provide an example to make it easier. In the above example, I want to do an ORDER BY height DESC, BUT only the tallest person of each rank gets ordered and everyone else in the same rank is right under that person
Postgresql select distinct Column A based on certain conditions on Column B
I have a table with data: I would like my query output to be distinct on userid but with condition that between fail and success values in status column. I would like to choose success instead (if both fail as in user_3, choose fail then). The table below shows the output that I would like to have as my result:
Rails ActiveRecord – Order query by matching number
I have a model called Location. This table has 2 fields I want to order by: rating (integer) and name (string). I want to order alphabetically by name, but if the rating is 5, I want that rating to come first. If the rating is anything else except for 5, I want them to unbiasedly show up in alphabetical order
SQL query with having function to only includes parts of another column?
I am trying to find fact_order_id’s that have beverage only items. This would make the fact_order_id not include any food items. I have used 2 separate SQL Queries below. When I go back and check my work, I notice some fact_order_id’s in the output aren’t beverage only orders. Here are the 2 separate SQL Queries that give me the same
Join on multiple columns and in one of the integer columns join by choosing minimum difference
I got table t1 and i want to join it with table t2 below on columns a, b and c +———+———+———+ |a |b |c | +———+———+———+ |473200 |1 |1.-1-…