Stack Overflow
Stack Overflow

Questions tagged [partition-by]

266 questions
1
vote
1 answer

Identifiy all rows that make up a partion

I am using SQL Server 2019 and I need to process the latest row from a group of rows. However, I need to mark the other rows in that group so I know they where evaluated when the latest row was processed. The actual problem is complicated, with…
Don Chambers
  • 3,798
  • 9
  • 33
  • 74
1
vote
3 answers

How to get the last record from the duplicate records in SQL?

I want to get the last record from the duplicate records and want the non-duplicate records also. As depicted in the below image I want to get row number 4, 5, 7 and 9 in my output. Here, In the below image the ** Main table** was shown. From…
Khyati Mistry
  • 21
  • 1
  • 1
  • 8
1
vote
1 answer

Pyspark updating a particular partition of an external Hive table

I am trying to overwrite a particular partition of a hive table using pyspark but each time i am trying to do that, all the other partitions are getting wiped off. I went through couple of posts in here regarding this and implemented the steps but…
Kaushik Ghosh
  • 121
  • 6
1
vote
2 answers

Performing complex data partitioning when using PARTITION BY in SQL Server

I am not a SQL expert, not even close. In the below example am using the PARTITION BY clause in a PERCENT_RANK() operation (Microsoft SQL Server hosted in Azure) to group my data for ranking which works as expected: DECLARE @Dinky TABLE ( [Id]…
Geo...
  • 307
  • 2
  • 14
1
vote
2 answers

Sum a column based on a condition in another column with partitions

I'm trying to sum a column based on a condition in another column with partition by in SQL, but it's not working. So I hope somebody can help me with this. My table is like…
Artursh
  • 33
  • 4
1
vote
1 answer

How to use COUNT () OVER(Partition) along with where clause

I want to get the count of claims for each NPI with filter condition. The sample query which I've is: create or replace table Table2 as select NPI, LAST_NAME, FIRST_NAME, ADDRESS, City, State, Zip, SPECIALTY_DESCRIPTION, COUNT(DISTINCT CLAIM_ID)…
User1011
  • 143
  • 1
  • 10
1
vote
0 answers

Trying to count unique observations in SQL using Partition By

I have these two datasets: Conditions: I would like to count the number of Unique Discharge_ID as Total_Discharges in my final dataset. ICU_ID is a little bit more difficult. For PT_ID 001, what is happening is that PT 001 has 4 of the same…
Trevor M
  • 89
  • 9
1
vote
1 answer

Partition by query behaves differently with slightly different input data

Select the foo row with the highest count of bar. in the case of matching rows with the same count, select the row with the highest id. I have solved the problem, but I wish to understand why the problem existed, and how the faulty query was able to…
Matt Strom
  • 698
  • 1
  • 4
  • 23
1
vote
1 answer

Group by after a partition by in MS SQL Server

I am working on some car accident data and am stuck on how to get the data in the form I want. select sex_of_driver, accident_severity, count(accident_severity) over (partition by sex_of_driver, accident_severity) from …
LottesofCode
  • 11
  • 3
1
vote
1 answer

SQL/BigQuery: case when statement over partition by

I have a table about conversations. There are many conversation elements for unique conversation…
allamirope
  • 31
  • 4
1
vote
1 answer

Partition By Scan To Seek

I have a T1 table. For every value in column C1/C2, column C3 starts at 1. CREATE TABLE t1 ( C1 int, c2 int, c3 int, CONSTRAINT [pk_idx] PRIMARY KEY CLUSTERED ([c1] ASC, [c2] ASC, [c3] ASC) ) SELECT C1, C2, MAX(C3) AS MAX_C3…
P.Lonnie
  • 105
  • 2
  • 11
1
vote
1 answer

T-SQL "partition by" results not as expected

What I'm trying to do is get a total count of "EmailAddresses" via using partitioning logic. As you can see in the result set spreadsheet, the first record is correct - this particular email address exists 109 times. But, the second record, same…
Mike Marks
  • 10,017
  • 17
  • 69
  • 128
1
vote
0 answers

SQL - reuse partition by window function with different filter condition

I have a table orders with the following fields: order_id: varchar user_id: varchar item_id: varchar datetime: timestamp quantity: int The following query would help me find the last item by each user_id where the quantity is > 10. WITH ranked AS…
Averell
  • 793
  • 2
  • 10
  • 21
1
vote
2 answers

snowflake partition by clause issue on a sql statement

We need to convert the below query to snowflake but, getting the below error while executing: TD Query: SEL * FROM EMP_HIST N QUALIFY MAX(CAST((N.pdate (DATE, FORMAT 'YYYY-MM-DD')) || ' ' || CAST(N.ptime AS CHAR(10)) AS TIMESTAMP(0))) OVER…
Rocky1989
  • 369
  • 8
  • 28
1
vote
4 answers

Pyspark Cumulative sum within Partition for moving last 2 (N) rows

Let's say I have the following dataframe which is sorted for ease visually: How would I utilize window functions to create a new column that sums the previous row ordered by Month column within each period partition: The following is my attempt at…
ben890
  • 1,097
  • 5
  • 25
  • 56
1 2 3
…
17 18