Corresponding column values ​​in the sequential SQL query

I have a table A col1 col2 1 81,123 2 90,99,100 3 4 And table 2 col1 col2 1 23 1 123 2 90 3 4 I want to join both the tables on first matching sequence for example : for col1 value =1 : 23 does not match neither with 81 or 123 so it would move to nex

Snapshot of the row instance of the table with time intervals

I am not sure what to call this phenomenon. I have just titles it with "Snapshot of table row instance with time intervals". We have a situation where we need to create a new entity which is kind of JOIN of two tables but these two tables have h

Ranking rows in a column under conditions on a different column

I have the following dataset: id | date | state ----------------------- 1 | 01/01/17 | high 1 | 02/01/17 | high 1 | 03/01/17 | high 1 | 04/01/17 | miss 1 | 05/01/17 | high 2 | 01/01/17 | miss 2 | 02/01/17 | high 2 | 03/01/17 | high 2 | 04/01/17 | mis

improve the query below row_number () over (partition by

Is there any way to improve query below, when I just check for one record it works faster(NAME='T 104') but when for all existing (113 records) records without NAME='T 104' it takes about 5 secs: the one below takes 73 msec: select name, PRODUCTION_D

Monthly balance Postgres

I'm using postgreSQL 9.2 I have a table with transactions that affect an account startbalance and I want to create a query that generates the monthly balances. It seems simple, but I'm stuck and the closest thing I can find might be this Calculate Mo

TSQL Row_Number

This question has been covered similarly before BUT I'm struggling. I need to find top N sales based on customer buying patterns.. ideally this needs to be top N by customer by Month Period by Year but for now i'm just looking at top N over the whole

Select partitions based on matches in the other table

Having the following table (conversations): id | record_id | is_response | text | ---+------------+---------------+----------------------+ 1 | 1 | false | in text 1 | 2 | 1 | true | response text 3 | 3 | 1 | false | in text 2 | 4 | 1 | true | respons

Total Excel working / window function

Say we have some data as followed Datetime Type Sale 25/07/2016 03:21 A 12 25/07/2016 04:00 B 23 25/07/2016 15:20 B 5 26/07/2016 05:15 A 15 26/07/2016 10:20 A 3 26/07/2016 16:10 B 20 27/07/2016 10:10 B 5 ....... Question is "Find the 36-hour frame wh

Get first and last row using ROW_NUMBER and PARTITION BY

Sample Input Name | Value | Timestamp -----|-------|----------------- One | 1 | 2016-01-01 02:00 Two | 3 | 2016-01-01 03:00 One | 2 | 2016-01-02 02:00 Two | 4 | 2016-01-03 04:00 Desired Output Name | Value | EarliestTimestamp | LatestTimestamp -----|

rank () and over () in Oracle queries

I need to understand the difference between these two oracle queries specially between rank() over(order by length(cgp.group_name) desc) and rank() over(order by length(cgp.group_name), length(csc.subscriber_num) desc) ranking. I searched on google r

MIN LAG in SQL Analytic

I have a table containing employees id, year id, client id, and the number of sales. For example: -------------------------------------- id_emp | id_year | sales | client id -------------------------------------- 4 | 1 | 14 | 1 4 | 1 | 10 | 2 4 | 2 |

Select by the largest sum, but without the sum in the result

I need to select the top score of all combined attempts by a player and I need to use a WITH clause. create table scorecard( id integer primary key, player_name varchar(20)); create table scores( id integer references scorecard, attempt integer, scor

add time from a timestamp field to another field

I'm trying to add the time (hours minutes and seconds) from the user initial date to the current date but I received an errors. I don't know what I do wrong, open to other suggestions select distinct uid, min (ts) over (partition by uid order by ts r

SQL Server: Using the Windows function with an ordered set

With this sample dataset: ID POS QTY A B 32 1 50 1 2 28 2 200 1 2 12 3 300 2 2 16 4 400 1 2 98 5 500 3 1 56 6 600 2 2 33 7 700 2 2 How can i get SUM(QTY) by group. Group is based on diferent (A AND B) ORDERED BY POS !!! As: ID POS QTY A B SUM(QTY) 32

SQL mobile aggregation

Issue: For a given recordset containing a date column, an arbitrary number of category columns, and a value column, I would like to compute aggregations of value for an arbitrary date window, such as 30 days, 365 days, etc. I have looked at the windo

SQL recursion with row_numbers

I got a temporary view in PostgreSQL which offers the following data (simplified): temporary_view (sum, rw): |sum | rw| |1337 | 1| |42 | 2| |... | ...| Now I need to do a recursion on this until my condition is reached. For simplicity, let's say I wa

implement the line number ()

How can I implement row_number() window function in sql server without using the function itself, maybe with join on an index table? or more, how can I implement select ROW_NUMBER() OVER(PARTITION BY ATLAS_USER_ID ORDER BY Event_Time DESC) AS RR row

How to sum the rows according to the partition in SQL?

how can you add values within rows according to it's partition and the sum will be placed below each partition (new row/s below each partition will be the container of the sum)? use the link below: http://sqlfiddle.com/#!3/4e3e80/4 column names: Prem

Postgresql row_number not order by date

I am working on postgres 9.3.2 and I have this table: id startdate enddate no_of_days_between 1 2010-12-22 2010-12-23 1 1 2010-12-23 2010-12-24 1 1 2010-12-24 2010-12-25 1 1 2010-12-25 2010-12-26 1 1 2010-12-26 2010-12-27 1 1 2010-12-27 2010-12-28 1

While I'm trying to use Partition over Hive,

SELECT prc.user_key, percentile(P.price, 0.5) OVER (PARTITION BY P.user_key) as median_price FROM ( SELECT vfc.user_key, vfc.order_id, MIN(vddo.cc) price FROM td_b.mv_a vfc JOIN td_b.dim_deal_option vddo ON vfc.d_key = vddo.d_key WHERE vfc.action = '

PostgreSQL checks the element of a previous record

I need to check the previous record's element to make sure the date I query doesn't fall within a specific range between ending date and 7 days before starting date. I have the following code: create or replace function eight (date) returns text as $

Selection of each Nth line per user in Postgres

I was using this SQL statement: SELECT "dateId", "userId", "Salary" FROM ( SELECT *, (row_number() OVER (ORDER BY "userId", "dateId"))%2 AS rn FROM user_table ) sa WHERE sa.rn=1 AND "userId" = 78