Select records using distinct and order by [modified]

[popchecker]

I am using MS Sql 2005. In my database I have a table "Products" which has following structure and content

ProductName CategoryId AddedDate

Product1 1 04/12/2010
Product2 1 04/12/2010
Product3 2 05/12/2010
Product4 1 06/12/2010

I want to fetch top 2 records order by AddedDate. To do that I can use sql

Select top 2 * From Products Order By AddedDate

This will populate first two records. Both are in CategoryId "1". I want to Select records from distinct categories. I requirment is to select records order by AddedDate with distinct CategoryId. I can do it with temporary tables. But is there any simple query to do that? Thanks and regards, Poppy

modified on Thursday, May 27, 2010 9:33 AM

[Blue_Boy]

SELECT DISTINCT TOP 2 p.CategoryId, (SELECT TOP 1 p2.AddDate FROM products p2 WHERE p2.CategoryId = p. CategoryId) AS adddate FROM products p ORDER BY p.CategoryId Hope this will help you.

---

I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post. www.aktualiteti.com

modified on Thursday, May 27, 2010 10:45 AM

[popchecker]

Please correct me if I am wrong. This will sort the results order by CategoryId not by AddedDate. But I could learn something from your SQL. Thanks for your help. Regards, Poppy

[Lost User]

Let's start out with a basic testscript that other posters can abuse to test on their machines;

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'

The testset is somewhat larger to make it somewhat easier to interpret what I'm looking at.

popchecker wrote:

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

If I understand correctly, then you want to group the data by category, sort within that group on the date, and take the top 2 records from each group? Sql Server 2005 supports "Common Table Expressions", which you could use as an alternative to a temporary table, like this;

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 :suss:

[i j russell]

Thanks for the test script.

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

[Tripathi Swati]

Hi russell , 5 from my side. This will run perfectly...

Reasons are not Important but Results are Important. http://www.sql4professional.blogspot.com Swati Tripathi

modified on Friday, May 28, 2010 1:46 AM

[Mycroft Holmes]

Learn something every day, I'd have gone with the rownumber/partition solution. I've never used cross apply!

Never underestimate the power of human stupidity RAH

[Lost User]

Cool! Thanks for updating it :)

I are Troll :suss: