-2

I have the followin table structure

person_id   organization_id
1            1
1            2
1            3
2            4
2            2

I want the result set as

person_id     organization_id
1               1
2               4

means TOP1 of the person_id

cadrell0
  • 17,109
  • 5
  • 51
  • 69
user2395176
  • 195
  • 1
  • 1
  • 11
  • 2
    possible duplicate of [Get top 1 row of each group](http://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) – GSerg Jul 16 '13 at 19:57
  • 5
    whats the ordering of the table here? – Eric Petroelje Jul 16 '13 at 19:57
  • 7
    You cannot do this unless you have some other column in your table which defines the order you need. The default ordering you get without an order-by clause cannot be relied on to stay the same. "top 1" has no meaning without specifying the order. – Blorgbeard Jul 16 '13 at 20:01

1 Answers1

1

You are using SQL Server, so you can use row_number(). However, you really cannot define top without ordering -- the results are not guaranteed.

So, the following will do a top without an order by:

select person_id, min(organization_id)
from t
group by person_id;

However, I assume that you intend for the order of the rows to be the intended order. Alas, SQL tables are unordered so the ordering is not valid. You really need an id or creationdate or something to specify the order.

All that said, you can try the following:

select person_id, organization_id
from (select t.*,
             row_number() over (partition by person_id order by (select NULL)) as seqnum
      from t
     ) t
where seqnum = 1;

It is definitely not guaranteed to work. In my experience, the order by (select NULL)) returns rows in the same order as the select -- although there is no documentation to this effect (that I have found). Note that in a parallel system on a decent sized table, SQL Server return order has little to do with the order of the rows on the pages or the insert order.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786