Get top first record from duplicate records having no unique identity

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

Solution 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 

Solution 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.