3

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?

Improve this question

5 Answers 5

Reset to default
6

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 |
+---------+--------+-------+
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

In this case, I'd actually recommend reforming your data; Ascending priority order, rather than descending. By having "most important" as priority = 0 you can just put the condition into a WHERE clause, and even make use of an index.
1

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%'
Improve this answer

2 Comments

+1. The code is what @Stavros want. Frendly suggestion, use JOIN's instead of WHERE
@niktrs, valid point, taken. I kind of got used to the where syntax, but you are right. JOIN is more standard
0 
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.

Improve this answer

2 Comments

This does not take priority into account
Fixed up the SQL based on Alvaro's answer.
0 
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%')            
    )
Improve this answer

4 Comments

while checking for max priority you don't have to put condition for name also.. for name only you want to get max priority .. right??
@pratik According to the question I understand that we need to filter rows by name and then leave only those rows which have a higher priority. In this scenario the name is important, because otherwise we can loose some rows.
no sir .. in this question we need to give one name for every id with higher priority for that id only... so if you will take "(name = at.name)" also in where condition while checking for "max(priority)" you will get all id and name combination.... but as per requirement solution we need only one row per id.. pelase think again for this and if you got my point please change second last line of your code as "where (id = at.id)" ... only
@pratik For me the author's question sounds different. But I think the author already found the answer he needs, so our answer is no more relevant for him :) Anyway, you forced me to give more attention into this query, so I noticed and fixed another mistake :)
0

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%'

Improve this answer

1 Comment

"first_value(name)over(partition by id order by priority desc) "... I thought you wanted to do this