pull only 1 row for each combination of GroupName, BusinessGroup fields

Copper Contributor

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
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')

@rpreddiz123 

 

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

@LainRobertson 

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              3

 

after 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

@rpreddiz123 

 

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

LainRobertson_0-1693267791928.png

 

Cheers,

Lain

Thank you Lian, sorry - that was type - number 3 should have been 2 - Yes, thanks a lot
now i can get the row number=1

@rpreddiz123 

 

To get only the rows where the id = 1, you'd simply wrap the original example up as a nested query with the WHERE filter in the outer query:

 

Example

SELECT
	*
FROM
(
	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
) AS [data]
WHERE
	[data].[id] = 1
ORDER BY
	[data].TicketGroup;

 

Output

LainRobertson_0-1693272179544.png

 

Cheers,

Lain