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 can't use distinct

I am using SQL Server 2005

Thanks.

Answer 1

Doesn't SELECT DISTINCT help? I suppose it would return the result you want.

Answer 2

Using DISTINCT should do it:

SELECT DISTINCT id, uname, tel
FROM YourTable

Though you could really do with having a primary key on that table, a way to uniquely identify each record. I'd be considering sticking an IDENTITY column on the table

Answer 3

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.

Answer 4

YOur best bet is to fix the datbase design and add the identioty column to the table. Why do you havea table without one in the first place? Especially one with duplicate records! Clearly the database itself needs redesigning.

And why do you have to have this in a view, why isn't your solution with the temp table a valid solution? Views are not usually a really good thing to do to a perfectly nice database.