How do I return rows on top of the search results which are defined in another table?

Question

I have two tables which contain data that should be combined. One contains contacts and the other defines lists of contacts. I would like to have the contacts of a specific list returned first, then followed by the remaining contacts.

To illustrate, here a working example:

These are the two tables:

Contacts             Lists
+--------------+     +-------------------------+
| id | name    |     | id | list | contacts_id |
+--------------+     +-------------------------+
| 1  | george  |     | 1  | 1    | 1           |
| 2  | lisa    |     | 2  | 1    | 3           |
| 3  | fred    |     | 3  | 1    | 6           |
| 4  | duncan  |     | 4  | 2    | 1           |
| 5  | edward  |     | 5  | 2    | 3           |
| 6  | sally   |     | 6  | 3    | 1           |
| 7  | alice   |     | 7  | 3    | 7           |
| 8  | chuck   |     | 8  | 1    | 2           |
+--------------+     | 9  | 5    | 2           |
                     | 10 | 5    | 8           |
                     +-------------------------+

Assuming I focus on List #1, the desired result is this:

Result

+----------------+
| list | name    |
+----------------+
| 1    | fred    |
| 1    | george  |
| 1    | lisa    |
| 1    | sally   |
| NULL | alice   |
| NULL | chuck   |
| NULL | duncan  |
| NULL | edward  |
+----------------+

To explain: The selected contacts from the list (in the example, list '1') should appear on top in alphabetical order. The other contacts - whether in a list or not - should appear below, avoiding duplicate names.

This is the statement I got so far:

SELECT
    Contacts.*,
    Lists.*
FROM
    Contacts
LEFT JOIN Lists ON
    Contacts.id = Lists.contacts_id
ORDER BY
    field(Lists.list, 1) DESC,
    ROW_NUMBER() OVER (PARTITION BY Contacts.id ORDER BY Contacts.name)

It shows contacts from list 1 on top, but it returns some names from that list again below the top results. I also tried adding GROUP BY contacts_id, but that seems to remove too many results.

How can I get the statement done in such a way that it returns every name in the contact table once and those from a specified list on top?

Tag your question with the database you are using. `FIELD()` is not a standard SQL function, for instance. — Gordon Linoff, Jul 31 '21 at 14:14
That is correct. This query runs on a MYSQL db. My fault for not mentioning that. I re-tagged the question. Thank you for pointing that out. — Charles, Jul 31 '21 at 15:14

MatBailie · Accepted Answer · 2021-07-31T15:44:32.133

2

I'm not sure what you believe field(Lists.list, 1) is doing.

I believe that what you actually want to do is "only left join on the list table when lists.list_id = 1"?

SELECT
  Contacts.*,
  Lists.*
FROM
  Contacts
LEFT JOIN
  Lists
    ON  Lists.contacts_id = Contacts.id
    AND Lists.list_id     = 1
ORDER BY
    CASE WHEN Lists.list_id IS NULL THEN 1 ELSE 0 END,
    Contacts.name

edited Jul 31 '21 at 15:44

answered Jul 31 '21 at 14:08

MatBailie

83,401
18
103
137

@Charles . . . You can simplify the first `order by` key to `(lists.id is not null) desc`, assuming you are using MySQL. – Gordon Linoff Jul 31 '21 at 14:16
@GordonLinoff Does that would in -every- dialect? *(I was trying to be cautious/defensive; as the op hadn't specified an RDBMS.)* – MatBailie Jul 31 '21 at 14:17
. . You are correct, I adjusted the comment. `FIELD()` is a MySQL function so I reacted to that. – Gordon Linoff Jul 31 '21 at 14:19
@MatBailie tried running this, but it doesn't return all contacts from list 1 on top, but returns only one contact with a list number on top. As to what I think field() is doing, among other places I got the idea from here: https://stackoverflow.com/questions/14104055/ordering-by-specific-field-value-first – Charles Jul 31 '21 at 15:11
@GordonLinoff Yes, it can be simplified. Good catch. Still doesn't return the correct result, though. ;-) – Charles Jul 31 '21 at 15:19
@charles Typo in answer corrected, demo here : [dbfiddle_example](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=bfd62cb1979a9bfa71fe0d65170811b3) – MatBailie Jul 31 '21 at 15:43
@MatBailie Just came back to let you know that your query was fine, except the typo. You were quicker. :-) Thank you. Your answer gives me ground for some more digging in the docs. :-) – Charles Aug 01 '21 at 11:22

How do I return rows on top of the search results which are defined in another table?

1 Answers1