Database

richa021 wrote:

Plz send me the syntax of it.

I are Troll

SELECT  DISTINCT TOP 2 <br />
<br />
	p.CategoryId,<br />
	(SELECT TOP 1 p2.AddDate<br />
	   FROM products p2 WHERE p2.CategoryId = p. CategoryId) AS adddate<br />
	<br />
FROM<br />
	products p<br />
ORDER BY p.CategoryId<br />

DECLARE @Product TABLE (
 [Name]		NVARCHAR(250)
,[refCategory]	BIGINT
,[Added]	DATETIME DEFAULT GETDATE()

 PRIMARY KEY ([Name])
)

INSERT INTO @Product 
--      Name		Category	 Added
SELECT 'Product1',	1,		'20101204' UNION
SELECT 'Product2',	1,		'20101206' UNION
SELECT 'Product3',	2,		'20101205' UNION
SELECT 'Product4',	1,		'20101204' UNION
SELECT 'Product5',	1,		'20101209' UNION
SELECT 'Product6',	2,		'20101206' UNION
SELECT 'Product7',	2,		'20101204' UNION
SELECT 'Service1',	3,		'20100101'

popchecker wrote:

I want to Select records from distinct categories. I requirment is to select records order by AddedDate with distinct CategoryId.

SELECT [Name], [refCategory], [Added]
  FROM(
    SELECT [refCategory],
       [Added],
       [Name],
       row_number() OVER (
           PARTITION BY [refCategory]
           ORDER BY [Added] ASC
       ) AS [catGroup]
      FROM @Product
     GROUP BY [refCategory], [Added], [Name]
   ) AS ProductCTE
WHERE [catGroup] <= 2
ORDER BY [refCategory] ASC, [Added] ASC

I are Troll

SELECT p.refCategory, t.Name, t.Added 
FROM 
(SELECT DISTINCT refCategory
	FROM @Product) p
	CROSS APPLY 
		(SELECT TOP 1 Name, Added
			FROM @Product
			WHERE refCategory = p.refCategory
			ORDER BY Added ASC) t 

SELECT * FROM   targetDatabase.INFORMATION_SCHEMA.COLUMNS