Forum Discussion
pull only 1 row for each combination of GroupName, BusinessGroup fields
Hi, All, Thanks a lot for looking into this..
I have this table which gets populated from different queries - This is my final table that we use for reporting and i have this scenario - this scenario is only for the reporting that i am looking at
Ticket_Number TicketGroup BusinessEntity
C125962 C125099 FIN
D3478 C125099 HR
C128055 C125099 FIN
C111 C125098 FIN
C222 C125098 HR
C333 C125098 FIN
This is how my data is structured for the specific report that i am trying to build
Each Ticket Group can have some tickets and each ticket group is associated with some Business Entity
I would like to create a row number with the over parition using ticket group and business entity
like
Row_number() Over (partition by TicketGroup,BusinessEntitiy Order By TicketGroup Desc, BusinessEntity asc)
But this will give me the following
Ticket_Number TicketGroup BusinessEntity Row_Number
C125962 C125099 FIN 1
D3478 C125099 HR 1
C128055 C125099 FIN 3
C111 C125098 FIN 1
C222 C125098 HR 1
C333 C125098 FIN 3
since i have 2 different Business Entitites in the first ticket group C125099 i get 1 and 1 in row number
but from here what i want is i only need where the row number is 1; If i do that i will get 2 rows
i have to business entity order by asc because i want the first letter so it is HR - for the ticket group c125099 i want the rownumber=1 with business entity as HR
6 Replies
Are you able to provide an example of the output you're expecting to see, as there's a number of points that don't make sense?
Leave out any columns you don't need (if there are any) in the final output, such as Ticket_Number and/or Row_Number.
Cheers,
Lain
Thank you Robert,
Ticket_Number TicketGroup BusinessEntity Row_Number
C125962 C125099 FIN 1
D3478 C125099 HR 1
C128055 C125099 FIN 3
C111 C125098 FIN 1
C222 C125098 HR 1
C333 C125098 FIN 3after sorting the BusinessEntity in asc order i want the row number=1
from the above bold rows - I do not want to put the BusinessEnitty in partition by because it will add 1 in row number for each business entity - instead use the partition by TicketGroup but sort the business entity in asc order and flag row number as 1 then filter using the row number=1
That's the same table as before.
Your examples aren't making a lot of sense because you can't jump directly from a row numbered 1 to a row numbered 3 (row 2 has to be in there somewhere).
You also mention you want BusinessEntity to be sorted ascendingly - I'm assuming within each partition, yet you want HR to come ahead of FIN, which cannot be done unless the sort order is descending.
These sorts of things leave us wondering if we're not understanding the question or if you're leaving out important bits of information.
At this point, all I can do is provide an example based on a guess.
Example
SELECT t.Ticket_Number , t.TicketGroup , t.BusinessEntity , ROW_NUMBER() OVER ( PARTITION BY t.TicketGroup ORDER BY t.BusinessEntity DESC , t.Ticket_Number ) AS [id] FROM testing123 AS t ORDER BY t.TicketGroup;Output
Cheers,
Lain
- CREATE TABLE [dbo].[Testing123](
[Ticket_Number] varchar(25),
[TicketGroup] varchar(25) ,
[BusinessEntity] varchar(25)
)
INSERT INTO Testing123
(Ticket_Number,TicketGroup,BusinessEntity)
VALUES
('C125962','C125099','FIN'),
('D3478','C125099','HR'),
('C128055','C125099','FIN'),
('C111','C125098','FIN'),
('C222','C125098','HR'),
('C333','C125098','FIN')
