SQL "group by" use priority

I have a table where I can have multiple names for a given id开发者_如何转开发 like this:

a_table (id int, name varchar(100), priority int);

I need a query that will search on names but make sure it will return only 1 name for each id, and that name will be the one with the higher priority.

e.g. if my data are

1, AaaB, 2
1, AbbB, 1
1, AccB, 0
2, foo, 0
3, AddC, 0

I want my query for "A%" to return:

1, AaaB
3, AddC

I was thinking something like:

select * from a_table where name like 'A%' group by id;

But this will not guarantee that the value with the higher priority will be selected.

Any ideas?

I believe you want what the MySQL documentation calls the rows holding the group-wise maximum of a certain column:

For the task "For each article, find the dealer or dealers with the most expensive price":

SELECT article, dealer, price
FROM   shop s1
WHERE  price=(SELECT MAX(s2.price)
             FROM shop s2
             WHERE s1.article = s2.article)
ORDER BY article;

+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0001 | B      |  3.99 |
|    0002 | A      | 10.99 |
|    0003 | C      |  1.69 |
|    0004 | D      | 19.95 |
+---------+--------+-------+

You have to first get the highest priority per id and then filter on the names:

select t2.id, t2.name, t2.price 
  from (
        select id, max(priority)
          from a_table
         group by id
       ) t1,
       a_table t2
 where t1.id = t2.id
   and t1.priority = t2.priority
   and t2.name like 'A%'

Taking @niktrs's valid suggestion, this is the same above query using the standard JOIN instead of where for joining tables. This is more preferred over the previous one

select t2.id, t2.name, t2.price 
  from (
        select id, max(priority)
          from a_table
         group by id
       ) t1 JOIN a_table t2 ON t1.id = t2.id
   and t1.priority = t2.priority
   and t2.name like 'A%'

select *
from a_table t
join (
    select max(Priority) MaxPriority, Name
    from a_table a
    where name like 'A%'
    group by Name
)x where x.MaxPriority=a.Priority and x.Name=t.Name

On the basis the first column in the data example is the "Priority".

This is just the SQL linked from Alvaro's answer though.

select id, name 
from a_table at
where 
    name like 'A%' and
    priority = (
        select max(priority)
        from a_table
        where (id = at.id) and (name like 'A%')            
    )

would this work out,

select distinct id,first_value(name)over(partition by id order by name) from demo_tab t where t.name like 'A%'

Sorry pratik, it must have been

select distinct id,first_value(name)over(partition by id order by priority desc) from demo_tab where name like 'A%'