How to order and partition by various columns?
I am deploying PostgreSQL. There is a table having 2 columns int he figure below:
I need to include a new column having a distinct id respective to partitions by name and category. Then, I need a random sample selecting 2 distinct ids since under every id, there will be a huge amount of historical data. I want to know how to correct my mistakes.
SELECT
dense_rank() over (partition by name, category order by category) as unique_id,
*
FROM
example_table
After this, presumably I'll have to use RAND() somewhere but how do I do this?
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_idFROM
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 = 1When 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.