2

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.

0

2 Answers 2

3

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 |
1
  • +1 - typically the row_number() solution will scale much better than the self-join (and is highly unlikely to ever be worse). Nov 9, 2013 at 0:21
1

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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