Questions tagged [partition-by]
266 questions
0
votes
1 answer
windowing functions ms access
I am working on a class scheduling database in MS Access. There are a variety of classes, each of which is taught multiple times, sometimes multiple times in a day, but not necessarily every day. Each course has a unique set of software and data…
John Mullen
- 1
- 1
- 1
0
votes
1 answer
Select Min ID in a partition - MSSQL t-SQL
I would like to select the minimum ID of a partition. Kind of like how dense_rank works I'd like a unique number associated with each paritition, but I'd like that numbber to be the minimum primary key within the partition. Here is an example of…
Arthur
- 23
- 1
- 6
0
votes
3 answers
SQL filter rows based without using Group by
I have a query which will perform joins over 6 tables and fetches various columns based on a condition. I want to add an extra filter condition which will give me only those members who have a count(distinct dateCaptured)>30. I'm able to get the…
shockwave
- 3,074
- 9
- 35
- 60
0
votes
1 answer
Removing duplicates from 'over partition by'
Im using over partition by clause to calculate peoples monthly figures.
A short example of my results:
Date Person Team Daily Figure Month To Date
24/09/17 James 2 50 200
24/09/17 …
Ryan Gadsdon
- 2,272
- 4
- 31
- 56
0
votes
0 answers
Row_number and Partition By in Access DB
I have this piece of code that I am trying to run on Access DB and getting a syntax error. I suppose Row number and Partition By does not work in Access. I was hoping I can get help in writing this in an alternate way so I can run in Access.
SELECT…
Shyama Sonti
- 321
- 1
- 5
- 16
0
votes
1 answer
SQL: How do you make a second grouping based off non-sequential dates?
I'm using SQL Server 2008 R2. I have table called Name with the following structure and sample data:
ID Date Name DOD
10001 200911 Kevin H 06/17/2000
10001 200912 Kevin 06/20/2000
10001 201001 …
Bill
- 3
- 1
0
votes
1 answer
In SQL, how do I create new column of values for each distinct values of another column?
Something like this: SQL How to create a value for a new column based on the count of an existing column by groups?
But I have more than two distinct values. I have a variable n number of distinct values, so I don't always know have many different…
Flair
- 2,609
- 1
- 29
- 41
0
votes
1 answer
Teradata Top-middle-bottom customers
I have a customer data set with the Customer ID and customer Spend. I need to split the customer dataset into 3 groups based on the customer send(high spend customers, Medium spend custs, low spend custs). I tried using RANK and Partitio by but i…
Ramya
- 51
- 2
- 10
0
votes
1 answer
SQL: Group by multiple columns
I created a table similar to this by creating a couple of temporary tables and joining them in my database:
customerid Segment Sales Country
1 1 $3 US
2 2 $4 CAN
3 3 $5 US
4 …
0
votes
1 answer
SQL: MAX of SUM in Each Group
Using the following code:
SELECT [Section]
,[Subgroup]
,[ID]
,SUM([Amount]) AS [SUM]
FROM Table
GROUP BY [Section], [Subgroup], [ID]
ORDER BY [Amount] DESC,[Section]
I was able to produce the…
Cinji18
- 619
- 8
- 22
0
votes
1 answer
Unable to use PARTITION BY with COUNT(*) when multiple columns are used
Suppose I have a table with the following columns:
TYPE
DEPARTMENT
SUPPLIER
ORDER
TOTAL
And I wanted to query this data so that I get ordered results, first grouped by TYPE. The order being the Number of Orders. Then the following Query Works Well…
Tora Tora Tora
- 973
- 3
- 18
- 33
0
votes
1 answer
SQL Server : Parent Child Hierarchy
I'm having trouble trying to create a parent and child hierarchy.
Query:
SELECT
ID,
[Manager Code],
Manager,
CASE WHEN [Manager Code] = 1 then
LAG(ID) over (order by [Manager Code] desc, ID, p_id) end as IDParentChild,
…
Nils
- 516
- 3
- 9
- 33
0
votes
1 answer
SQL analytical function: rank() over partition by not working property
CREATE TABLE customers
( customer_id number(10) not null,
customer_name varchar2(50) not null
);
INSERT INTO customers VALUES(22,'W');
INSERT INTO customers VALUES(22,'W');
INSERT INTO customers VALUES(20,'Q');
INSERT INTO customers…
Dhruv
- 10,291
- 18
- 77
- 126
0
votes
1 answer
Using Row_number() OVER(partition BY..) along with declaring local variables
I have a sql query, in this sql query I want to select distinct columns irrespective of column first. For other sql query I use Row_number() OVER(partition BY..) and I also need to use inner join. The query in which I want to use row_number and…
tiddi rastogi
- 526
- 3
- 10
- 33
0
votes
1 answer
Return the First, Second and Third Earliest Purchase Dates per Purchaser
for a list of purchaser email addresses, I am trying to return one line per purchaser that has the columns '1stOrderDate', '2ndOrderDate', '3rdOrderDate' and 'TotalNumberofOrders'
I have tried using the ROW_Number function in the WHERE clause of…
J-BAC
- 13
- 6