You can utilize roe number () function to arrange and par the columns.
SELECT
name,
category,
ROW_NUMBER() OVER (PARTITION BY name, category ORDER BY name, category) unique_id
FROM
test;
name | category | unique_id
:--- | :------- | --------:
A | Alpha | 1
A | Alpha | 2
A | Alpha | 3
A | Beta | 1
A | Beta | 2
B | Alpha | 1
B | Alpha | 2
B | Theta | 1
db<>fiddle here
To get a unique_id for each pair of name-category, remove the PARTITION part and use only ORDER BY:
SELECT
name,
category,
DENSE_RANK() OVER (ORDER BY name, category) unique_id
FROM
test;
name | category | unique_id
:--- | :------- | --------:
A | Alpha | 1
A | Alpha | 1
A | Alpha | 1
A | Beta | 2
A | Beta | 2
B | Alpha | 3
B | Alpha | 3
B | Theta | 4
db<>fiddle here
To select two rows of each unique_id: (As far as I've not used any order it is supposed to be random)
WITH ct AS
(
SELECT
name,
category,
DENSE_RANK() OVER (ORDER BY name, category) unique_id
FROM
test
)
SELECT
a.unique_id, c.name, c.category
FROM
(SELECT DISTINCT unique_id
FROM ct) a
JOIN LATERAL (SELECT b.name, b.category
FROM ct b
WHERE b.unique_id = a.unique_id
LIMIT 2) c ON TRUE
ORDER BY
a.unique_id;
unique_id | name | category
--------: | :--- | :-------
1 | A | Alpha
1 | A | Alpha
2 | A | Beta
2 | A | Beta
3 | B | Alpha
3 | B | Alpha
4 | B | Theta
You can also try the below-mentioned code:
SELECT * FROM (
SELECT
[Code],
[Name],
[CategoryCode],
[CreatedDate],
[ModifiedDate],
[CreatedBy],
[ModifiedBy],
[IsActive],
ROW_NUMBER() OVER(PARTITION BY [Code],[Name],[CategoryCode] ORDER BY ID DESC) rownumber
FROM MasterTable
) a
WHERE rownumber = 1
When the table columns have similar data and when you directly apply row_number() and make PARTITION on column, the result in a duplicate row with row number value appears. To erase duplicate row, one more INNER query is needed from clause that removes replicate rows and gives result to the outer FROM clause where you can sue PARTITION and ROW_NUMBER ()>
The SQL Server Online Training at JanBaskTraining offers experience like offline classes, saving students from the hassle of traveling to the physical location. The training provides a total SQL Server discipline preparation by teaching the core concept and techniques that the job role demands. The training also gives extensive training to impart fundamental and advanced concepts through interesting e-tools and expert-led SQL Server classes. Furthermore, JanBask Traininghelps you get job-ready and face the tough competitive market with confidence.
