Stack Overflow
Stack Overflow

Questions tagged [partition-by]

266 questions
44
votes
5 answers

How to Use Multiple Columns in Partition By And Ensure No Duplicate Row is Returned

I have used multiple columns in Partition By statement in SQL but duplicate rows are returned back. I only want distinct rows being returned back. This is what I have coded in Partition By: SELECT DATE, STATUS, TITLE, ROW_NUMBER() OVER (PARTITION BY…
Felicia Soh
  • 815
  • 3
  • 18
  • 32
22
votes
2 answers

Cannot use group by and over(partition by) in the same query?

I have a table myTable with 3 columns. col_1 is an INTEGER and the other 2 columns are DOUBLE. For example, col_1={1, 2}, col_2={0.1, 0.2, 0.3}. Each element in col_1 is composed of all the values of col_2 and col_2 has repeated values for each…
user3557405
  • 567
  • 1
  • 3
  • 19
7
votes
1 answer

Get top 1 row of each group using Kusto

I have a table which I would like to get the latest entry for each group using Kusto Query Language. Here's the…
cmomah
  • 165
  • 2
  • 9
7
votes
1 answer

Row_Number over (partition by...) all columns

I have a view with something like 150 columns and I want to add an Id column to that view. Is it possible not to write all the column names in the over (partition by... ) statment? something like this: row_number over (partition by *) As ID?
Toto88
  • 129
  • 3
  • 11
7
votes
3 answers

ROW_NUMBER with partition by returns result with duplicate rows

When I run this query I get more rows than from a similar query without the ROW_NUMBER () line: SELECT DISTINCT id, value, ROW_NUMBER () OVER (PARTITION BY (id) ORDER BY value DESC NULLS LAST ) max FROM TABLE1…
user1658192
  • 221
  • 1
  • 4
  • 10
5
votes
1 answer

SnowFlake's performance on group by vs partition on vs distinct

I have a table in Snowflake. One of the columns in the table is called obj_key(object key). The table size is very large (in TBs), so performance is desirable. Now, a new entry is added to the table everytime an update on an object is done. The…
ghost
  • 1,107
  • 3
  • 12
  • 31
5
votes
4 answers

MAX() OVER PARTITION BY in Oracle SQL

I am trying to utilize the MAX() OVER PARTITION BY function to evaluate the most recent receipt for a specific part that my company has bought. Below is an example table of the information for a few parts from the last year: | VEND_NUM | VEND_NAME …
artemis
  • 6,857
  • 11
  • 46
  • 99
4
votes
3 answers

First time group meets condition in pandas DataFrame

I have the following pandas.DataFrame: id year x 0 01001 2015 0 1 01001 2016 0.5 2 01001 2017 0 3 01001 2018 0 4 01002 2015 0 5 01002 2016 0 6 01002 2017 0.0667525 7 01002 2018 0.133505 My goal is to create a new column…
Arturo Sbr
  • 5,567
  • 4
  • 38
  • 76
4
votes
2 answers

What is the role of ORDER BY in the PARTITION BY function?

I have a table with data follow, ID SEQ EFFDAT ------- --------- ----------------------- 1024 1 01/07/2010 12:00:00 AM 1024 3 18/04/2017 12:00:00 AM 1024 2 01/08/2017…
Karthik
  • 99
  • 1
  • 8
4
votes
1 answer

TSql Return column based on partition and rownumber

I have a SQL server table where I'm attempting to get a computed column - MyPartition - indicating the number of the partition based on a variable @segment. For example, if @segment = 3 then the following output would be true. RowID | RowName |…
Breaker
  • 319
  • 2
  • 11
4
votes
3 answers

MySQL query to update all "duplicate" rows except the latest one

SO... I have a table (let's call it Data Location) like... Data Location ID Data ID Location Type Location URL Status Date 1 1 Foo foo/3 Valid 10-21-2014 2 1 …
Lane
  • 685
  • 2
  • 10
  • 25
4
votes
1 answer

Find the maximum value in a column for each partition

I have table structure like: CREATE TABLE new_test ( col1 NUMBER(2) NOT NULL, col2 VARCHAR2(50) NOT NULL, col3 VARCHAR2(50) NOT NULL, col4 VARCHAR2(50) NOT NULL ); It has data: col1 col2 col3 col4 0 A B X 1 …
user613114
  • 2,731
  • 11
  • 47
  • 73
3
votes
1 answer

Avoid subqueries when grouping over a part of a string

I have a list of products and components, that have different prices. Here is a simplified example. drop table if exists #group_test Create Table #group_test ( ID Integer , Hierarchy Nvarchar(200) , Price Integer ) Insert Into…
Merlin Nestler
  • 262
  • 11
3
votes
2 answers

Modifying records in one column of a table conditioning on the date ranges in the other table

I am currently trying to combine Left join with window function (e.g. partition by class_id order by date_to desc) to solve the following problem in SQL. It is quite a difficult problem due to a bunch of tricky constraints that come into play, and…
user177196
  • 738
  • 1
  • 8
  • 16
3
votes
2 answers

Can I use one PARTITION definition for multiple window function calls?

This is my query. I use the same window function sum() with the same partition definition for 14 result columns: select id,weekly, sum(totalsteps) OVER (PARTITION BY id, weekly) as total_steps, sum(totaldistance) OVER (PARTITION BY id, weekly)…
Jade Young
  • 99
  • 1
  • 7
1
2 3
…
17 18