T-SQL Help Please

[Domo]

I have a table with 3 columns.
ID, EMail, Company

Example data
1,[email protected],BBC
1,[email protected],British Broadcasting
1,[email protected],Beeb

I only want the first record returned. Using Distinct on ID doesn't seem to work when I include Company in the select statement because the values are different. I just want to discard the subsequent records.

Can someone help me please

 

[Domo]

SELECT TOP 1 * FROM Table ORDER BY FieldName

As redeye says, if you're got duplicated IDs you need to restructure your table first.

OK. I've pulled in an extra (and obviously vital!) field which includes a unique ID number for each record.

So now looks like,
1203,1,[email protected],BBC
1204,1,[email protected],British Broadcasting
1205,1,[email protected],Beeb

Same thing applies - I only need the first or even last record. So I'm now thinking using max or min via a nested query?

 

[Domo]

Figured it out guys:
select f.abID, f.ID, f.Email, f.Company
from (
select ID, min(abID) as minabID
from <table> group by ID
) as x inner join <table> as f on f.ID = x.ID and f.abID = x.minabID;

 

[Domo]

Why didnt you use the query Spunkey posted?

I think the user probably has several sets of ID/e-mail combos with different Company names and needs this to work for all sets of records. The code would only give back the first record in the table, which looks like what the user may require from the first post . I am assuming the user has say 3 records for each person and 30 in the table, so he wants to return 10 unique rows for example, so some sort of grouping is required.

In fact it's a massive table. To be fair the scenario I provided you guys was very misleading - sorry about that.

But thanks for the suggestions!

 

[Domo]

If you don't mind which company name you get out then you can do this using the MIN/MAX functions on the first table.

SELECT ID, email, MIN(Company) AS CompanyName
FROM <TABLE>
GROUP BY ID, email

No need for nested queries and joins, may be useful for future reference.

Will store this - thanks