Filter Duplicate records based on priority

rayge99

Ive been racking my brain trying to figure out how to do this.

say i have 3 records with book_id 22 each of those records has a different shop_id record though lets use 1,2,3

SELECT shop_id, book_title
FROM book_table
group by book_id
order by book_title

this would work fine except it doesnt allow me to filter based on priority.

I want to filter by shop_id in a priority order such as 2,1,3
so if I hit a book id with shop_ids 1,2,3 it only grabs the one with id 2
or if it gets a book_id that has 1 and 3 it only gets the one with id 1

Hope this makes sense!
Thanks for any help

bradgrafelman

rayge99 wrote:
I want to filter by shop_id in a priority order such as 2,1,3

What does "priority order" mean? Where does the priority of each shop_id come from?

rayge99

In my example priority order was 2 then1 then 3. So 2 takes first priority if it is available and so on. As far as where to set the priority I would probably give the user the ability to set the priority order in an admin panel.

bradgrafelman

Well the "where" is what we need to know before we can offer a solution.

rayge99

OK well lets say there is a table called shop_priority with the following fields
shop_id sortorder
and the data would be
2 1
1 2
3 3

and just for good measure hopefully this will help visualize things

example data

book_id shop_id book_title
22 1 My book
22 2 My book
22 3 My book

24 1 My other book
24 3 My other book

in the first group we would take shop_id 2
in the second group we would take shop id 1
ending up with only 2 of the 5 records

Weedpacket

Try using [noparse]

...

[/noparse] tags. Then your tables won't lose their formatting.

Using b for a book_id, s for a shop_id, and p for a sortorder, I think what you're asking for is:

For a given b, select the shop_id s (if it exists) for which a pair (b,s) exists in the data table, a pair (s,p) exists in the shop_priority table, and p is as small as possible.

Select shop_id from shop_priority where sortorder = (
    Select min(sortorder) from shop_priority where shop_id in (
        Select shop_id from data where book_id = $b))

That looks horribly inefficient, and only works for one book_id at a time. But it formalises what I said in the previous paragraph, just in case that's a bit hard to follow.