Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm new in DB world, so if you can help me it would be great.

My problem:

I have filtered some data and got a table with data, but need to filter them again. Picture might help you. For me, it is pretty complicated. (I made this tables / picture just to illustrate my problem, I'm not allowed to post real data because they are business secret)

enter image description here

I have data in Table 1 (HAVE), and need to get data in table 2 (WANT). Need to take data from first table, but result is based on first appearing in table 1 based on name. When I "find" new "name" (in real life there are some other data, but it is not matter because it is also string), take that row and put it in new table. Then second name, third name ... it depends how many different names are in first table.

share|improve this question
1  
How do you know that you want to return Name = A with Value = 15 and ID = 1, is it because it has the lowest ID value? How you ordering the to determine the first one? Data in a table doesn't have an order unless you provide one, so how do you determine that Name = B and Id = 2 would be included? –  bluefeet Nov 8 '13 at 14:56
    
Oh, yes... first A will have smallest ID ... First B will have smallest ID if we look only B's... etc... Picture is wrong ... thanks for the update ... –  Boris Plavljanic Nov 8 '13 at 15:04

2 Answers 2

up vote 3 down vote accepted

If you want to return the minimum id for each name, then there are a few ways that you can get the result.

One way would be to use an aggregate function in a subquery. The subquery will return the min(id) for each name, you then use this subquery an join it back to your table on those two columns:

select t1.name,
  t1.value,
  t1.id
from yourtable t1
inner join
(
  select min(id) id, name
  from yourtable
  group by name
) t2
  on t1.id = t2.id
  and t1.name = t2.name

See SQL Fiddle with Demo

Another way you can get the result would be using the row_number() windowing function. This will allow you to create a unique identifier for each row based on the name and order the result by the id, you get the final result by returning only those rows with a sequence value of 1:

select name, value, id
from
(
  select name, value, id,
    row_number() over(partition by name
                      order by id) seq
  from yourtable
) d
where seq = 1;

See SQL Fiddle with Demo. Both will return a result:

| NAME | VALUE | ID |
|------|-------|----|
|    A |    15 |  1 |
|    B |    12 |  1 |
|    C |    12 |  1 |
share|improve this answer
    
+1 - typically the row_number() solution will scale much better than the self-join (and is highly unlikely to ever be worse). –  Aaron Bertrand Nov 9 '13 at 0:21
    
Thank you for your answer!!! :) I really appreciate this... –  Boris Plavljanic Nov 11 '13 at 7:05

As always, many ways to do this.

Logically the easiest way for me would be to use APPLY. Just select the distinct values you want, and then make a cross apply query against the same table selecting the first result, ordering it based on whichever column you use to determine which is the "first" appearance.

Such as:

SELECT CA.*
FROM
   (SELECT DISTINCT name, 
   FROM TABLE1) SRC
CROSS APPLY (SELECT TOP 1 * FROM TABLE1 WHERE name = SRC.name) CA

But as said, you can use many other means of doing the same. Sub queries where you group names with MIN(ID) values, or ROW_NUMBER / DENSE_RANK functions, and then applying the appropriate filters outside the sub query, etc. APPLY just tends to seem the least complicated. And naturally performance-wise this may not be the optimal solution, so in the long term it serves to experiment a bit to find the fastest method for the query.

share|improve this answer
    
Thank you for your answer! :) I believe there are many ways to do that, but I'm not very good with SQL, so I asked for help. It is much easier, because I've lost 4-5 hours on that and couldn't do anything, and ppl here can solve this problem in 10 min or so. –  Boris Plavljanic Nov 11 '13 at 13:39
1  
Oh of course, I didn't intend to let on that there was something wrong about asking the question. That's what everyone's here for. :) I simply meant that there are many ways to go about something like this, so you should use whichever seems like the easiest to you. And if there's a performance consideration, it pays to compare the different solutions to see which of them is the fastest. :) –  Kahn Nov 11 '13 at 13:46

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.