T-SQL Help Please

Domo · 13 Jul 2011 at 12:14

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

redeye_uk · 13 Jul 2011 at 12:19

Why have you got duplicate ID's? You should be using a link table if you want to group them to a 'type'.

GravyMonster · 13 Jul 2011 at 12:38

Code:

SELECT TOP 1 * FROM Table ORDER BY FieldName

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

Domo · 13 Jul 2011 at 13:10

Spunkey said:
Code:

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 · 13 Jul 2011 at 15:29

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;

redeye_uk · 13 Jul 2011 at 15:36

Why didnt you use the query Spunkey posted?

AshenShugar1873 · 13 Jul 2011 at 15:39

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.

AshenShugar1873 · 13 Jul 2011 at 15:46

redeye_uk said:
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.

Domo · 14 Jul 2011 at 09:36

redeye_uk said:
Why didnt you use the query Spunkey posted?

AshenShugar1873 said:
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 · 14 Jul 2011 at 09:36

AshenShugar1873 said:
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

Dj_Jestar · 14 Jul 2011 at 10:44

Do you still need min/max if using group by?

AshenShugar1873 · 14 Jul 2011 at 10:57

Dj_Jestar said:
Do you still need min/max if using group by?

You do if including the Company Field. Grouping by ID, EMail would return the correct set of results but no Company name. Once you include company name you return extra results because each Company name is different for a particular ID/EMail pair. Min/Max gets around this by returning either the first or last Company Name for each ID/email pair so you only get one row for each ID/EMail pair as required. Using group by requires all returned fields to be the same before it gathers them into one row in the result set.