Sql-server – T-SQL – How to filter data from table

sql server

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.

Best Answer

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 |
Related Question