anyone please help to write a query, urgent requirement

tanvip 

Here's the T-SQL I used to stage your data based on your previous post:

CREATE TABLE [contactCategory]
(
	[id] [int] NOT NULL
	, [type] [varchar](16) NOT NULL
)
GO

CREATE TABLE [contact]
(
	[id] [int] NOT NULL
	, [value] [varchar](16)
	, [typeId] [int] NOT NULL
)
GO

INSERT INTO
	[contactCategory]
VALUES
	(1, 'phone')
	, (2, 'mobile')
GO

INSERT INTO
	[contact]
VALUES
	(1, '97867445453', 1)
	, (1, '14343556677', 2)
	, (2, '9783673526', 1)
	, (3, null, 1)
	, (3, '1465276127', 2)
	, (4, '18397387309', 2)
	, (5, '0281938978', 2)
GO

Here's a query that does what you've asked for:

SELECT
	[data].[id]
	, [data].[value]
FROM
(
	SELECT
		c.id
		, c.value
		, ROW_NUMBER() OVER (
			PARTITION BY
				c.id
			ORDER BY
				c.typeId
		) AS [rowNumber]
	FROM
		[contact] AS c INNER JOIN
		[contactCategory] AS cc ON TRIM(c.value) <> '' AND c.typeId = cc.id
) AS [data]
WHERE
	[data].[rowNumber] = 1;

Output from the query

Cheers,

Lain