Questions tagged [partition-by]
266 questions
0
votes
1 answer
Select entire partition where max row in partition is greater than 1
I'm partitioning by some non unique identifier, but I'm only concerned in the partitions with at least two results. What would be the way to get out all the instances where there's exactly one of the specified identifier?
Query I'm using:
SELECT…
charles
- 547
- 1
- 3
- 11
0
votes
2 answers
ROW_NUMBER() over (Partition by....) to return specific row
My query looks like this:
with T1 as (
Select
Dept_No,
Product_No,
Order_No,
Order_Type
Row_number() over (partition by Product_ID order by Order_No desc) as "COUNT"
From Orders_Table)
Select * from T1
where ("COUNT" = '1' and "Order_Type" <>…
user3486773
- 1,174
- 3
- 25
- 50
0
votes
1 answer
How to find first IN and last OUT for a particular column?
I have a table having a column event. It has data like IN and OUT.
Now I need to create two columns having the time of the first IN for a particular ID and the last OUT for the same ID.
I need to use partition by window function with lead and…
Nancy Jean
- 59
- 1
- 7
-1
votes
1 answer
SQL with CTE that has ROW_NUMBER Over Partition remove rn column
I have a script that returns results that are including the RN = 1 column, So all rows have this value 1 with column name RN.
If I try to change select * to select my as 'column_name' I get strange results where the column names are in all the…
Dominic Legendre
- 123
- 1
- 7
-1
votes
1 answer
Use the same column in both group by and partition by in BigQuery
I have a table TAB with 5 columns,
col1: string ('AAA', 'BBB', ...)
col2: string (null, 'XYZ', ...)
col3: timestamp ('2020-01-01 00:00:00', ...)
col4: string ('A1A', 'B1B', ...)
col5: string ('A2A', null, ...)
Sample Data
WITH TAB AS
(SELECT 'AAA'…
Sam Gladio
- 89
- 1
- 1
- 4
-1
votes
2 answers
Scalar subquery producing more than 1 record with partition - SQL
I have data with two rows as follows:
group_id item_no
weoifne 1
weoifne 2
I want to retrieve the max item_no for each group_id. I'm using this query:
SELECT MAX(item_no)
OVER (PARTITION BY group_id)
FROM my_table;
I need only one…
Ricardo Francois
- 752
- 7
- 24
-1
votes
1 answer
How to exclude null value rows from the result set of CASE statement oracle SQL
I am trying to exclude the rows with null values from Units_shorted column (from the CASE clause) but not finding a way.
SELECT DISTINCT td.task_id,td.cntr_nbr,lh.dsp_locn AS pull_locn,td.orig_reqmt,td.qty_pulld,
(
CASE
WHEN
((SUM(td.qty_pulld)…
user1978900
- 9
- 5
-1
votes
1 answer
Adding max and case to data?
I'm trying to add a few things to my code here and alter this table, but I'm a bit of a greenhorn in SQL and I'm struggling to make the mental leap to find out how to connect a few things.
I'm trying to add a Partition by job to get row 1,2,3,4...…
majinvegito123
- 25
- 5
-1
votes
1 answer
Microsoft SQL Server 2016 - T-SQL puzzle - overlapping date ranges in segregated rows - 'Gaps & Islands' problem
I have asked 'Gaps and Islands' questions in the past, but this one is significantly different. I have an interesting question in Microsoft SQL Server 2016 database, T-SQL language. (Refer to image files and T-SQL scripts with temp tables)
I have a…
user3812887
- 439
- 12
- 33
-1
votes
1 answer
SQL query over partition with condition
I have a table like this, in SQL Server…
Maximiliano Vazquez
- 196
- 1
- 2
- 12
-1
votes
2 answers
Partition BY start time of day in SQL Server
Looking for ways to specify the start time of a PARTITION BY statement in SQL Server.
Partitioning a years worth of data into 60 minute segments. The data is 10 minute collections from an IOT device.
Would like the partitions to start at 6AM each…
fip
- 43
- 5
-1
votes
1 answer
partition by multiple columns in Spark SQL not working properly
I want to partition by three columns in my query :
user id
cancelation month year.
retention month year.
I used row number and partition by as follows
row_number() over (partition by user_id ,cast ( date_format(cancelation_date,'yyyyMM') as…
layal
- 27
- 6
-1
votes
1 answer
Is it possible to use group by and partition by in one query to get value from different groups?
I have a query:
select y, m, count(distinct id) as y_m_cnt, *other columns* from tab group by y, m, *other columns*
),
t2 as (
select y, count(distinct id) as y_cnt, *other columns* from tab group by y, *other columns*
)
select * t1.y, t1.m,…
Andrey
- 19
- 7
-1
votes
1 answer
SQL Help: Partition By Clause (SSMS)
I don't have much experience with using Partition By Clauses, and I'm having trouble coming up with a working query for the below data, despite digging around all of the usual places.
Please refer to the attachment link, but suffice to say I am…
KingSizeZero
- 23
- 4
-1
votes
3 answers
How to PARTITION BY to show the same value for all rows?
I have a listing of all consumer purchases where some consumers make many purchases over the time frame in scope. I'd like to populate a column with the location of each consumer's first purchase but I'm getting this error:
Error in SQL statement:…
June Smith
- 163
- 1
- 11