Need Help in Product counts in a particular category and top 1 product details row wise

Need Help in Product counts in a particular category and top 1 product details row wise for that particular category.

Below is the 2 table query from which i need desired results.

USE [Master]
GO
/****** Object: Table [dbo].[product_subcategory] Script Date: 08-05-2018 06:00:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[product_subcategory](
[Prd_subcategory_id] [bigint] IDENTITY(1,1) NOT NULL,
[Prd_category_id] [int] NULL,
[name] nvarchar NULL,
[sort] [bigint] NULL,
[IsActive] [smallint] NULL,
[UpBy] nvarchar NULL,
[UpDates] [datetime] NULL,
[CrBy] nvarchar NULL,
[CrDates] [datetime] NULL CONSTRAINT [DF_AddOns_CrDates] DEFAULT (getdate()),
[isdel] [bit] NULL CONSTRAINT [DF_AddOns_isdel] DEFAULT ((0)),
CONSTRAINT [PK_AddOns] PRIMARY KEY CLUSTERED
(
[Prd_subcategory_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object: Table [dbo].[tbl_xv_product] Script Date: 08-05-2018 06:00:44 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbl_xv_product](
[xv_productid] [bigint] IDENTITY(1,1) NOT NULL,
[xv_sectionid] nvarchar NULL,
[xv_maincatid] [bigint] NULL,
[xv_subacatid] [bigint] NULL,
[xv_brandid] nvarchar NULL,
[xv_productname] nvarchar NULL,
[xv_productcode] nvarchar NULL,
[xv_color] nvarchar NULL,
[xv_size] nvarchar NULL,
[xv_weight] nvarchar NULL,
[xv_description] nvarchar NULL,
[xv_productnumber] nvarchar NULL,
[xv_makeflag] [bit] NULL,
[xv_productfinishedgoodflag] [bit] NULL,
[xv_prddpcost] [int] NULL,
[xv_stockid] [bigint] NULL,
[xv_gst] [int] NULL,
[xv_lines_per_board] [smallint] NULL,
[xv_prdshippingcost] [int] NULL,
[xv_prdsellingprice] [int] NULL,
[xv_isDiscounted] [bit] NULL,
[xv_discount] [int] NULL,
[xv_prddiscountprice] [int] NULL,
[xv_sellstartdate] [datetime] NULL,
[xv_prdimage] nvarchar NULL,
[xv_positioning] [int] NULL,
[IsActive] [smallint] NULL,
[xv_is_can_enable_buy1_take1] [bit] NULL,
[sort] [bigint] NULL,
[UpBy] nvarchar NULL,
[UpDates] [datetime] NULL,
[CrBy] nvarchar NULL,
[tagsid] nvarchar NULL,
[xv_noshipping] nvarchar NULL,
[CrDates] [datetime] NULL CONSTRAINT [DF_tbl_xv_product_CrDates] DEFAULT ([dbo].datefunction),
[isdel] [bit] NULL,
CONSTRAINT [PK__tbl_xv_p__69B423B045F365D3] PRIMARY KEY CLUSTERED
(
[xv_productid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET IDENTITY_INSERT [dbo].[product_subcategory] ON

GO
INSERT [dbo].[product_subcategory] ([Prd_subcategory_id], [Prd_category_id], [name], [sort], [IsActive], [UpBy], [UpDates], [CrBy], [CrDates], [isdel]) VALUES (1, 3, N'Acrylic Name Plates', 1, 1, N'1', CAST(N'2018-04-23 22:32:02.167' AS DateTime), N'1', CAST(N'2018-04-21 22:22:49.063' AS DateTime), 0)
GO
INSERT [dbo].[product_subcategory] ([Prd_subcategory_id], [Prd_category_id], [name], [sort], [IsActive], [UpBy], [UpDates], [CrBy], [CrDates], [isdel]) VALUES (2, 3, N'Brass Name Plates', 2, 1, N'1', CAST(N'2018-04-22 00:20:01.487' AS DateTime), N'1', CAST(N'2018-04-22 00:09:07.980' AS DateTime), 0)
GO
INSERT [dbo].[product_subcategory] ([Prd_subcategory_id], [Prd_category_id], [name], [sort], [IsActive], [UpBy], [UpDates], [CrBy], [CrDates], [isdel]) VALUES (3, 3, N'Bungalow Name Plates', 3, 1, N'1', CAST(N'2018-04-22 00:20:01.487' AS DateTime), N'1', CAST(N'2018-04-22 00:09:28.950' AS DateTime), 0)
GO
INSERT [dbo].[product_subcategory] ([Prd_subcategory_id], [Prd_category_id], [name], [sort], [IsActive], [UpBy], [UpDates], [CrBy], [CrDates], [isdel]) VALUES (4, 3, N'Ceramic Name Plates', 4, 1, N'1', CAST(N'2018-04-22 00:20:01.487' AS DateTime), N'1', CAST(N'2018-04-22 00:09:51.717' AS DateTime), 0)
GO
INSERT [dbo].[product_subcategory] ([Prd_subcategory_id], [Prd_category_id], [name], [sort], [IsActive], [UpBy], [UpDates], [CrBy], [CrDates], [isdel]) VALUES (5, 3, N'Decorative Name Plate', 5, 1, N'1', CAST(N'2018-04-22 00:21:08.630' AS DateTime), N'1', CAST(N'2018-04-22 00:20:01.487' AS DateTime), 0)
GO
INSERT [dbo].[product_subcategory] ([Prd_subcategory_id], [Prd_category_id], [name], [sort], [IsActive], [UpBy], [UpDates], [CrBy], [CrDates], [isdel]) VALUES (6, 3, N'PlatesDesk Name Plates', 6, 1, NULL, NULL, N'1', CAST(N'2018-04-23 21:45:32.043' AS DateTime), 0)
GO
INSERT [dbo].[product_subcategory] ([Prd_subcategory_id], [Prd_category_id], [name], [sort], [IsActive], [UpBy], [UpDates], [CrBy], [CrDates], [isdel]) VALUES (7, 3, N'Engraved Slate Rock Name Plates', 7, 1, NULL, NULL, N'1', CAST(N'2018-04-23 21:45:52.920' AS DateTime), 0)
GO
INSERT [dbo].[product_subcategory] ([Prd_subcategory_id], [Prd_category_id], [name], [sort], [IsActive], [UpBy], [UpDates], [CrBy], [CrDates], [isdel]) VALUES (8, 3, N'Glass Name Plates', 8, 1, NULL, NULL, N'1', CAST(N'2018-04-23 21:46:41.327' AS DateTime), 0)
GO
INSERT [dbo].[product_subcategory] ([Prd_subcategory_id], [Prd_category_id], [name], [sort], [IsActive], [UpBy], [UpDates], [CrBy], [CrDates], [isdel]) VALUES (9, 3, N'Granite Name Plates', 9, 1, NULL, NULL, N'1', CAST(N'2018-04-23 21:46:54.847' AS DateTime), 0)
GO
INSERT [dbo].[product_subcategory] ([Prd_subcategory_id], [Prd_category_id], [name], [sort], [IsActive], [UpBy], [UpDates], [CrBy], [CrDates], [isdel]) VALUES (10, 3, N'Metal Name Plates', 10, 1, NULL, NULL, N'1', CAST(N'2018-04-23 21:47:11.423' AS DateTime), 0)
GO
INSERT [dbo].[product_subcategory] ([Prd_subcategory_id], [Prd_category_id], [name], [sort], [IsActive], [UpBy], [UpDates], [CrBy], [CrDates], [isdel]) VALUES (11, 3, N'Language Name Plates', 11, 1, NULL, NULL, N'1', CAST(N'2018-04-23 21:47:35.753' AS DateTime), 0)
GO
INSERT [dbo].[product_subcategory] ([Prd_subcategory_id], [Prd_category_id], [name], [sort], [IsActive], [UpBy], [UpDates], [CrBy], [CrDates], [isdel]) VALUES (12, 3, N'Signages & Sign Board', 12, 1, NULL, NULL, N'1', CAST(N'2018-04-23 21:47:48.050' AS DateTime), 0)
GO
INSERT [dbo].[product_subcategory] ([Prd_subcategory_id], [Prd_category_id], [name], [sort], [IsActive], [UpBy], [UpDates], [CrBy], [CrDates], [isdel]) VALUES (13, 3, N'Siporex Name Plates', 13, 1, N'1', CAST(N'2018-04-23 21:48:58.900' AS DateTime), N'1', CAST(N'2018-04-23 21:48:29.397' AS DateTime), 0)
GO
INSERT [dbo].[product_subcategory] ([Prd_subcategory_id], [Prd_category_id], [name], [sort], [IsActive], [UpBy], [UpDates], [CrBy], [CrDates], [isdel]) VALUES (14, 3, N'Sleeper Wood Name Plates', 14, 1, NULL, NULL, N'1', CAST(N'2018-04-23 21:50:37.077' AS DateTime), 0)
GO
INSERT [dbo].[product_subcategory] ([Prd_subcategory_id], [Prd_category_id], [name], [sort], [IsActive], [UpBy], [UpDates], [CrBy], [CrDates], [isdel]) VALUES (15, 3, N'Stainless Steel Name Plates', 15, 1, NULL, NULL, N'1', CAST(N'2018-04-23 21:50:41.703' AS DateTime), 0)
GO
INSERT [dbo].[product_subcategory] ([Prd_subcategory_id], [Prd_category_id], [name], [sort], [IsActive], [UpBy], [UpDates], [CrBy], [CrDates], [isdel]) VALUES (16, 3, N'tone Name Plates', 16, 1, NULL, NULL, N'1', CAST(N'2018-04-23 21:50:46.563' AS DateTime), 0)
GO
INSERT [dbo].[product_subcategory] ([Prd_subcategory_id], [Prd_category_id], [name], [sort], [IsActive], [UpBy], [UpDates], [CrBy], [CrDates], [isdel]) VALUES (17, 3, N'Teak Float Name Plates', 17, 1, NULL, NULL, N'1', CAST(N'2018-04-23 21:50:50.873' AS DateTime), 0)
GO
INSERT [dbo].[product_subcategory] ([Prd_subcategory_id], [Prd_category_id], [name], [sort], [IsActive], [UpBy], [UpDates], [CrBy], [CrDates], [isdel]) VALUES (18, 3, N'Wooden Name Plates', 18, 1, NULL, NULL, N'1', CAST(N'2018-04-23 21:50:55.437' AS DateTime), 0)
GO
INSERT [dbo].[product_subcategory] ([Prd_subcategory_id], [Prd_category_id], [name], [sort], [IsActive], [UpBy], [UpDates], [CrBy], [CrDates], [isdel]) VALUES (19, 3, N'Wrought Iron Name Plates', 19, 1, NULL, NULL, N'1', CAST(N'2018-04-23 21:51:00.187' AS DateTime), 0)
GO
INSERT [dbo].[product_subcategory] ([Prd_subcategory_id], [Prd_category_id], [name], [sort], [IsActive], [UpBy], [UpDates], [CrBy], [CrDates], [isdel]) VALUES (20, 3, N'Browse Entire Collection', 20, 1, NULL, NULL, N'1', CAST(N'2018-04-23 21:51:04.030' AS DateTime), 0)
GO
SET IDENTITY_INSERT [dbo].[product_subcategory] OFF
GO
GO
INSERT [dbo].[tbl_xv_product] ( [xv_sectionid], [xv_maincatid], [xv_subacatid], [xv_brandid], [xv_productname], [xv_productcode], [xv_color], [xv_size], [xv_weight], [xv_description], [xv_productnumber], [xv_makeflag], [xv_productfinishedgoodflag], [xv_prddpcost], [xv_stockid], [xv_gst], [xv_lines_per_board], [xv_prdshippingcost], [xv_prdsellingprice], [xv_isDiscounted], [xv_discount], [xv_prddiscountprice], [xv_sellstartdate], [xv_prdimage], [xv_positioning], [IsActive], [xv_is_can_enable_buy1_take1], [sort], [UpBy], [UpDates], [CrBy], [tagsid], [xv_noshipping], [CrDates], [isdel]) VALUES (N'0', 3, 2, N'0', N'Adani flower', N'1', N'', N'0', N'', N'', NULL, 1, 1, 0, NULL, 0, 0, 0, 3420, 1, 0, 0, CAST(N'1900-01-01 00:00:00.000' AS DateTime), N'26042018132958_Web.jpg', NULL, 1, 1, 1, N'', CAST(N'2018-04-26 21:59:55.147' AS DateTime), N'', NULL, N'', CAST(N'2018-04-26 21:47:36.937' AS DateTime), NULL)
GO
INSERT [dbo].[tbl_xv_product] ( [xv_sectionid], [xv_maincatid], [xv_subacatid], [xv_brandid], [xv_productname], [xv_productcode], [xv_color], [xv_size], [xv_weight], [xv_description], [xv_productnumber], [xv_makeflag], [xv_productfinishedgoodflag], [xv_prddpcost], [xv_stockid], [xv_gst], [xv_lines_per_board], [xv_prdshippingcost], [xv_prdsellingprice], [xv_isDiscounted], [xv_discount], [xv_prddiscountprice], [xv_sellstartdate], [xv_prdimage], [xv_positioning], [IsActive], [xv_is_can_enable_buy1_take1], [sort], [UpBy], [UpDates], [CrBy], [tagsid], [xv_noshipping], [CrDates], [isdel]) VALUES (N'0', 3, 1, N'0', N'AdaniBlack', N'2', N'', N'0', N'', N'', NULL, 1, 1, 0, NULL, 0, 0, 0, 3420, 1, 0, 0, CAST(N'1900-01-01 00:00:00.000' AS DateTime), N'26042018133306_Web.jpg', NULL, 1, 1, 0, N'', CAST(N'2018-04-26 22:03:22.087' AS DateTime), N'', NULL, N'', CAST(N'2018-04-26 22:02:26.147' AS DateTime), NULL)
GO
INSERT [dbo].[tbl_xv_product] ( [xv_sectionid], [xv_maincatid], [xv_subacatid], [xv_brandid], [xv_productname], [xv_productcode], [xv_color], [xv_size], [xv_weight], [xv_description], [xv_productnumber], [xv_makeflag], [xv_productfinishedgoodflag], [xv_prddpcost], [xv_stockid], [xv_gst], [xv_lines_per_board], [xv_prdshippingcost], [xv_prdsellingprice], [xv_isDiscounted], [xv_discount], [xv_prddiscountprice], [xv_sellstartdate], [xv_prdimage], [xv_positioning], [IsActive], [xv_is_can_enable_buy1_take1], [sort], [UpBy], [UpDates], [CrBy], [tagsid], [xv_noshipping], [CrDates], [isdel]) VALUES (N'0', 3, 1, N'0', N'AdaniWhiteBottom', N'3', N'', N'0', N'', N'', NULL, 1, 1, 0, NULL, 0, 0, 0, 3420, 1, 0, 0, CAST(N'1900-01-01 00:00:00.000' AS DateTime), N'26042018133356.jpg', NULL, 1, 1, 0, NULL, NULL, N'', NULL, N'', CAST(N'2018-04-26 22:03:53.790' AS DateTime), NULL)
GO
INSERT [dbo].[tbl_xv_product] ( [xv_sectionid], [xv_maincatid], [xv_subacatid], [xv_brandid], [xv_productname], [xv_productcode], [xv_color], [xv_size], [xv_weight], [xv_description], [xv_productnumber], [xv_makeflag], [xv_productfinishedgoodflag], [xv_prddpcost], [xv_stockid], [xv_gst], [xv_lines_per_board], [xv_prdshippingcost], [xv_prdsellingprice], [xv_isDiscounted], [xv_discount], [xv_prddiscountprice], [xv_sellstartdate], [xv_prdimage], [xv_positioning], [IsActive], [xv_is_can_enable_buy1_take1], [sort], [UpBy], [UpDates], [CrBy], [tagsid], [xv_noshipping], [CrDates], [isdel]) VALUES ( N'0', 3, 1, N'0', N'ViratWhite', N'76', N'', N'0', N'', N'', NULL, 1, 1, 0, NULL, 0, 0, 0, 2460, 1, 0, 0, CAST(N'1900-01-01 00:00:00.000' AS DateTime), N'26042018141512.jpg', NULL, 1, 1, 0, NULL, NULL, N'', NULL, N'', CAST(N'2018-04-26 22:45:08.893' AS DateTime), NULL)
GO
INSERT [dbo].[tbl_xv_product] ( [xv_sectionid], [xv_maincatid], [xv_subacatid], [xv_brandid], [xv_productname], [xv_productcode], [xv_color], [xv_size], [xv_weight], [xv_description], [xv_productnumber], [xv_makeflag], [xv_productfinishedgoodflag], [xv_prddpcost], [xv_stockid], [xv_gst], [xv_lines_per_board], [xv_prdshippingcost], [xv_prdsellingprice], [xv_isDiscounted], [xv_discount], [xv_prddiscountprice], [xv_sellstartdate], [xv_prdimage], [xv_positioning], [IsActive], [xv_is_can_enable_buy1_take1], [sort], [UpBy], [UpDates], [CrBy], [tagsid], [xv_noshipping], [CrDates], [isdel]) VALUES ( N'0', 3, 1, N'0', N'Vinay', N'68', N'', N'0', N'', N'', NULL, 1, 1, 0, NULL, 0, 0, 0, 4200, 1, 0, 0, CAST(N'1900-01-01 00:00:00.000' AS DateTime), N'26042018141521.jpg', NULL, 1, 1, 0, NULL, NULL, N'', NULL, N'', CAST(N'2018-04-26 22:45:17.830' AS DateTime), NULL)
GO
INSERT [dbo].[tbl_xv_product] ( [xv_sectionid], [xv_maincatid], [xv_subacatid], [xv_brandid], [xv_productname], [xv_productcode], [xv_color], [xv_size], [xv_weight], [xv_description], [xv_productnumber], [xv_makeflag], [xv_productfinishedgoodflag], [xv_prddpcost], [xv_stockid], [xv_gst], [xv_lines_per_board], [xv_prdshippingcost], [xv_prdsellingprice], [xv_isDiscounted], [xv_discount], [xv_prddiscountprice], [xv_sellstartdate], [xv_prdimage], [xv_positioning], [IsActive], [xv_is_can_enable_buy1_take1], [sort], [UpBy], [UpDates], [CrBy], [tagsid], [xv_noshipping], [CrDates], [isdel]) VALUES ( N'0', 3, 1, N'0', N'Virat2SidesGlossyBlack', N'71', N'', N'0', N'', N'', NULL, 1, 1, 0, NULL, 0, 0, 0, 3780, 1, 0, 0, CAST(N'1900-01-01 00:00:00.000' AS DateTime), N'26042018141547.jpg', NULL, 1, 1, 0, N'', CAST(N'2018-05-04 01:23:28.970' AS DateTime), N'', NULL, NULL, CAST(N'2018-04-26 22:45:44.313' AS DateTime), NULL)
GO

i have tried to write a sql query which gives me partly desired results

select M.Prd_subcategory_id,M.subcat_name, Count( M.Ranks) As More_products_Count
from(
select PCS.Prd_subcategory_id,PCS.name as subcat_name,
P.xv_productid,P.xv_productname ,P.xv_prdsellingprice,P.xv_prdimage ,
rank() over(order by PCS.Prd_subcategory_id) as Ranks
from tbl_xv_product P left join product_subcategory PCS on PCS.Prd_subcategory_id = P.xv_subacatid
) M group by M.Prd_subcategory_id,M.subcat_name

the above query gives me below results

|Prd_subcategory_id|subcat_name|More_products_Count|
|---|---|---|---|
|1|Acrylic Name Plates|5|
|2|Brass Name Plates|1|

What i want is

Prd_subcategory_id subcat_name More_products_Count xv_productid xv_productname xv_prdsellingprice xv_prdimage
1 Acrylic Name Plates 5 2 AdaniBlack 3420 26042018133306_Web.jpg
2 Brass Name Plates 1 1 Adani flower 3420 26042018132958_Web.jpg