This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(278.4, 23%, 36%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ED%3C/text%3E%3C/svg%3E)
I have a table of customers where they are assigned a category based on particular behavior. It is possible that a customer can be assigned multiple categories (and thus have multiple records in the table). The categories are assigned a Rank in an hierarchy. So if a customer falls into 2 categories and one is assigned Rank #1 and the other is assigned Rank #3, I only want to keep the record that has the Rank #1 category. Here is an example of data:
In the above example, the first customer was assigned two categories. I would only want to keep the second record with Rank#1 and delete the other record. How can this be accomplished?
Hi,
We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.
Still need to run some tests. Will see if I have some time today
As I understand it, you want to keep the row with the lowest rank for each customer.
; WITH CTE AS (
SELECT row_number() OVER(PARTITION BY ACCOUNTID ORDER BY rank) AS rowno
FROM tbl
)
DELETE CTE WHERE rowno > 1
I think I understand the logic, but where am I pointing to the table where I want to delete the records?
I've edited my post.
A common recommendation we have is that you should post the CREATE TABLE statement for your table(s) and INSERT statements with sample data and also the expected result given the sample. This makes it easy to copy and paste into a query window to develop a tested solution.
When you, like here, only post a verbal description and the sample data as an image, you are likely to get an untested solution. Which could have errors.
Hmm. Apologies, but I'm not sure exactly what you are asking. There is no CREATE TABLE statement - the table already exists. Records are inserted via a nightly ETL process. So there is an existing table with data as indicated in the print screen. I now need to query that table selecting only the record with the highest RANK for each ACCOUNT. I could post the table structure, but it is just three fields - an int a varchar and an int. I'm afraid I do not know how to post "sample data" to these forums. If you direct me, I will be happy to oblige. If I paste in some data, it just comes out as text as shown below. Not sure if that helps at all. Sorry I can't be more helpful, but I'm not involved in the creation of the table. I am just tasked with finding a method of de-duping it as per the outlined requirements. Please let me know what else I may be able to provide given this further explanation. Thank you.
ACCOUNTID cycle rank
108388712 ONE 2
108388712 NEW 1
108388727 TRIAL 3
108388733 TRIAL 3
108388961 TRIAL 3
108388977 ONE 2
108388990 TRIAL 3
108389240 TRIAL 3
108389498 TRIAL 3
108389502 TRIAL 3
108389728 TRIAL 3
To illustrate, the below is from another question that I answered yesterday. The poster supplied this:
CREATE TABLE [dbo].[Atable](
[Id] [int] IDENTITY(1,1) NOT NULL,
[RoomID] [int] NOT NULL,
[CheckInDate] [datetime] NULL,
[CheckOutDate] [datetime] NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Atable] ON
GO
INSERT [dbo].[Atable] ([Id], [RoomID], [CheckInDate], [CheckOutDate]) VALUES (1, 7, CAST(N'2023-09-15T00:00:00.000' AS DateTime), CAST(N'2023-09-16T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[Atable] ([Id], [RoomID], [CheckInDate], [CheckOutDate]) VALUES (2, 30, CAST(N'2023-09-19T00:00:00.000' AS DateTime), CAST(N'2023-09-20T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[Atable] ([Id], [RoomID], [CheckInDate], [CheckOutDate]) VALUES (3, 43, CAST(N'2023-09-16T00:00:00.000' AS DateTime), CAST(N'2023-09-19T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[Atable] ([Id], [RoomID], [CheckInDate], [CheckOutDate]) VALUES (4, 44, CAST(N'2023-09-19T00:00:00.000' AS DateTime), CAST(N'2023-09-21T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[Atable] ([Id], [RoomID], [CheckInDate], [CheckOutDate]) VALUES (5, 45, CAST(N'2023-09-16T00:00:00.000' AS DateTime), CAST(N'2023-09-18T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[Atable] ([Id], [RoomID], [CheckInDate], [CheckOutDate]) VALUES (6, 6, CAST(N'2023-09-17T00:00:00.000' AS DateTime), CAST(N'2023-09-18T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[Atable] ([Id], [RoomID], [CheckInDate], [CheckOutDate]) VALUES (7, 31, CAST(N'2023-09-20T00:00:00.000' AS DateTime), CAST(N'2023-09-21T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[Atable] ([Id], [RoomID], [CheckInDate], [CheckOutDate]) VALUES (8, 17, CAST(N'2023-09-15T00:00:00.000' AS DateTime), CAST(N'2023-09-18T00:00:00.000' AS DateTime))
GO
SET IDENTITY_INSERT [dbo].[Atable] OFF
Since I had this test script with table and sample data it was easy for me to test the query I developed for this question.
Note that this may not be the poster's actual table. It is likely to be a mockup to illustrate the key parts of his problem.
Now, in your case I hope that my edited query in my original post should address your problem. But if you use this style the next time, it is less likely that you will get an answer with a non-functional query.
OK. Thanks. I'll try out the CTE you modified. In the future, I can ask our IT department to make a "dummy" script of how the table would be created (a simplified version, the actual ETL is quite complicated and just loads existing tables that were created many years ago). I don't make tables myself. Thank you for the helpful feedback.
It does not have to be the actual table. Just something the emulates the real thing and which focuses on the actual problem.
Then again, you can easily script tables from Object Explorer in SSMS.