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 scenari...
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
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
- Thank you Lian, sorry - that was type - number 3 should have been 2 - Yes, thanks a lot
now i can get the row number=1To 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
Cheers,
Lain