sql SERVER - distinct selection based on priority columns-CodePudding

hello I would like to find a solution to solve my problem in a single request if possible. For the moment I take all the records then I go through the lines one by one to eliminate what I don't want.

I have 2 tables : first table with links

the second with the prefered label for the url

the second table must be consulted keeping only the row with maximum priority

priority rules are the current user then the user group and finally everyone.

if the hidden column is true, exclude any reference to the url

here is the expected result.

Unfortunately, I don't see any other solution than to multiply the conditions on several selects and unions.

if you have a idea to solve my problem, thank you in advance for your help

CodePudding user response：

It appears as though you can rely on pref_id for the preference ordering, correct? If so, you could try:

SELECT *
FROM table2
INNER JOIN table1 ON table2.url_id = table1.url_id
QUALIFY ROW_NUMBER() OVER (
    PARTITION BY table1.url
    ORDER BY pref_id ASC
) = 1

This will partition by the url and then provide only the one with lowest pref_id.

I didn't test this SQL as I wasn't sure which RDBMS you're running on, but I used Rasgo to translate the SQL.

CodePudding user response：

maybe of interest in this tricky query:

select so.*, table1.url from 
(select distinct t.url_id, 
  (select pref_id from table2 s where s.url_id = t.url_id order by "user" is null, "group" is null limit 1) pref_id
  from table2 t
  where not exists(select 1 from table2 s where s.hide and s.url_id = t.url_id)
) ids
join table2 so on so.pref_id = ids.pref_id
join table1 ON table1.url_id = ids.url_id
order by so.url_id;