Get top first record from duplicate records having no unique identity

Question

I need to fetch top first row out of each duplicate set of records from table given below. I need to use this query in view

please no temp table as I have already done it by adding identity column and min function and group by. I need solution without temp table or table variable

This is just sample data. Original has 1000s of records in table and I need just result from top 1000, so I can't use distinct

I am using SQL Server 2005

Answer 1

Find all products that has been ordered 1 or more times... (kind of duplicate records)

SELECT DISTINCT * from [order_items] where productid in  (SELECT productid    FROM [order_items]   group by productid    having COUNT(*)>0) order by productid  

To select the last inserted of those...

SELECT DISTINCT productid, MAX(id) OVER (PARTITION BY productid) AS LastRowId from [order_items] where productid in  (SELECT productid    FROM [order_items]   group by productid    having COUNT(*)>0) order by productid  

Answer 2

The answer depends on specifically what you mean by the "top 1000 distinct" records.

If you mean that you want to return at most 1000 distinct records, regardless of how many duplicates are in the table, then write this:

SELECT DISTINCT TOP 1000 id, uname, tel FROM Users ORDER BY <sort_columns> 

If you only want to search the first 1000 rows in the table, and potentially return much fewer than 1000 distinct rows, then you would write it with a subquery or CTE, like this:

SELECT DISTINCT * FROM (     SELECT TOP 1000 id, uname, tel     FROM Users     ORDER BY <sort_columns> ) u 

The ORDER BY is of course optional if you don't care about which records you return.