I have a supplier table where in it contains all the supplier specific information (total 10 columns). I am trying to group them in such a way that it should be ordered by the creation of the record i.e. DateCreated and where SupplierNumber is same and a new column should be created as Type and group that same SupplierNumber and DateCreated as Group1 and so on as shown below. Sample Table Data :

SupplierName    SupplierNumber  DateCreated
Supplier4       50006155        07/13/2022 08:09PM
Supplier1       50000253        07/18/2022 10:19PM
Supplier5       50003200        07/13/2022 08:23PM
Supplier1       50000253        07/18/2022 10:19PM
Supplier3       50005963        07/13/2022 08:06PM
Supplier2       50001781        07/20/2022 02:11PM
Supplier3       50005963        07/13/2022 08:06PM
Supplier4       50006155        07/13/2022 08:09PM
Supplier5       50003200        07/13/2022 08:23PM
Supplier2       50001781        07/20/2022 02:11PM

Example : it should be in below format:

Type        SupplierName    SupplierNumber  DateCreated
Group 1     Supplier1       50000253        07/18/2022 10:19PM
Group 1     Supplier1       50000253        07/18/2022 10:19PM
Group 2     Supplier2       50001781        07/20/2022 02:11PM
Group 2     Supplier2       50001781        07/20/2022 02:11PM
Group 3     Supplier3       50005963        07/13/2022 08:06PM
Group 3     Supplier3       50005963        07/13/2022 08:06PM
Group 4     Supplier4       50006155        07/13/2022 08:09PM
Group 4     Supplier4       50006155        07/13/2022 08:09PM
Group 5     Supplier5       50003200        07/13/2022 08:23PM
Group 5     Supplier5       50003200        07/13/2022 08:23PM

Solution Tried:

Select SupplierNumber,DateCreated  from Supplier
GROUP BY SupplierNumber,DateCreated
ORDER BY DateCreated, SupplierNumber

Thank You in Advance,

CodePudding user response：

select concat('Group ', dense_rank() over(order by SupplierName, DateCreated)) as Type
      ,*
from   t

Fiddle