Get Unique Results in a query.

Question

Hi guys! Im sure this will be pretty simple for a t-sql guru.

I have the following result from a table

idMain  IdSecondary  TextValue
1       1            text1
1       2            text2
2       5            text3
2       6            text5

And I want to obtain the first occurence of the idMain only.

The result should be like this.

idMain  idSecondary  TextValue
1       1            text1
2       5            text3

How can I achieve this?

Answer 1

You should be able to join with a sub query, as in the following example. This assumes that by "first" you mean the lowest idSecondary:

SELECT t.idMain, sub_t.minSecondary, t.textValue
FROM   your_table t
JOIN   (SELECT   idMain, MIN(idSecondary) minSecondary
        FROM     your_table
        GROUP BY idMain) sub_t ON (sub_t.idMain = t.idMain);

Answer 2

depends on what you mean by "first". Normally this word would imply datetime or clock ordering, but your table does not have a date or datetime field in it...

So you have to clarify what you mean by "first"

If you have not stored in the table some value that can tell the query processor how to order any two records to distinquish between whatever you mean by the "first" one from the other one, then you're out of luck....

assuming that you do have such a field, (let's say it's IdSecondary for the sake of an example - since your sample results look like that is the case), then what you need to do is

Select * From Table a
Where IdSecondary= (Select Min(IdSecondary)
                   From Table
                   Where idMain = a.idMain)