Making "sticky" rows in SQL

making synonym
better word for making
meaning-making synonym
making in hindi
another word for making
how to pronounce making
where to buy making magazine
making history synonym

I have a table with users, they have a bunch of fields like name address etc.

What I need:

I need to make relationships between user records.But not the database relationships, actual relationships. For example 2 users can be in a relationship, and there can be various types of relationships for example husband/wife relationship, brother/sister, parent/child and so on. There is a search function on the website where users can be filtered by their name, address and so on. By default they are ordered alphabetically when searching. I need to keep them in an alphabetical order, but the main part is that if a user is in a relationship with another user then they need to be next to each other in the result, breaking the alphabetical order in that case which is ok, but the users who are not in a relationship they need to stay in order, only the second user from a relationship that gets moved to the first user can be out of alphabetical order.

What I have done so far:

In the users table I have added a column for the type of a relationship for each user ( ex. 1 is for no relationship, 2 is husband/wife, 3 is brother/sister etc.), and another column for id of the other user in a relationship, default is 0 for no relationship. I created everything on the website so relationships can be added to users, for each user you can go to options and add relationship with another user, and when that is done, the server updates the user table for both users setting their user ids to each other and the same relationship type. There is also a problem with filtering, filters need to be ignored in case if a user is in a relationship then their partner needs to be added in case they don't match the filter. Where I'm stuck is is that "sticky" feature where users in a relationship need to be together.

What I tried so far:

I tried adding a third column to the users table, a unique id for a relationship that gets added to both users in a relationship. And I tried filtering with help of that column. something like this:

SELECT * FROM users WHERE 'all the filters go here' ORDER BY relatioship_unique_id, name;

That did something, but not what I need. In the result the first were the first few users that are in a relationship, each user was next to the one they were in a relationship with which is ok, but they weren't mixed with the other users who had no relationship, they were just on top of the result and after them came the other users. I don't know how should I do this. Maybe those users in a relationship can be grouped, and then combined with the users that are not in a relationship. Btw. the alphabetical order for those 2 users in a relationship is not important, they just need to be globally ordered by alphabet with other users, any users name from a relationship can be used in "global" ordering ( I would use a user with a lower ID, but it doesn't matter really). I was also experimenting with the Switch() function (ORDER BY CASE in normal SQL) but it didn't help. The other solution that comes to my mind is to add another column "sorting" that would have correct values for sorting computed on the server, and it would get updated with each relationship change. But that approach is really complicated, I believe it can be done simpler with just SQL. Maybe my original approach is wrong, maybe relationships need to be in a separate table. The database is modifiable so anything that work goes.

Sorry if this is a duplicate, I tried hard to find it, but I don't really know what to look for. I don't know the name of this. I've given it a name "sticky rows". That's why the title is a bit ambiguous.

The database I'm working is an MS access database.

And an example of how the result is supposed to look like: If we have this table ( relationship type 1 is for no relationship ):

Figure 1:
user_id | user_name | rel_type | other_user_in_rel | unique_rel_id
--------------------------------------------------------------------------
1       | T_Test    | 1        | 0                 | 0 
2       | A_Test    | 4        | 3                 | 1 
3       | Z_Test    | 4        | 2                 | 1
4       | D_Test    | 1        | 0                 | 0
5       | F_Test    | 1        | 0                 | 0
6       | G_Test    | 1        | 0                 | 0
7       | H_Test    | 3        | 9                 | 2
8       | W_Test    | 1        | 0                 | 0
9       | U_Test    | 3        | 7                 | 2

So the result of a search query should be like this

Figure 2:
user_name | unique_rel_id | Comment
----------------------------------
A_Test    | 1             | -These 2 are in a relationship
Z_Test    | 1             | -
D_Test    | 0             |
F_Test    | 0             |
G_Test    | 0             |
H_Test    | 2             | -These 2 are in a relationship
U_Test    | 2             | -
T_test    | 0             |
W_Test    | 0             |

This is a simpler example without filtering

My current query, which is not working properly:

SELECT T1.*, T2.*
FROM users As T1
LEFT JOIN (
    SELECT DISTINCT  MIN(user_name) AS min_user_name, relationship_unique
    FROM users
    WHERE relationship_unique <> 0
    GROUP BY user_name, relationship_unique
    ) AS T2 ON T2.relationship_unique = T1.relationship_unique
ORDER BY T2.min_user_name, T1.user_name

The first issue is that the sorting is not working like it should, in the result it first shows the users that have no relationship and then the users with a relationship, they should be mixed and not separated. And the other issue is that I have duplicate rows of users in relationship (this is because of the left join, but I don't know how to solve it).

Here is the algorithm. Please write the query yourself. It's almost two in the morning here. Time to go to bed :-)

First get relationship pairs (lesser ID first, greater ID second):

user_name | pair
----------------
Test1     | 0-1
A_Test2   | 2-3
Z_Test3   | 2-3
D_Test1   | 0-4
F_Test2   | 0-5
G_Test3   | 0-6
H_Test4   | 7-9
W_Test5   | 0-8
L_Test6   | 7-9

Then find the min(name) per pair:

pair | min(name)
----------------
0-1  | Test1
0-4  | D_Test1
0-5  | F_Test2
0-6  | G_Test3
0-8  | W_Test5
2-3  | A_Test2
7-9  | H_Test4

Then order the users by the min(name), name:

user_name | pair | min(name)
----------------------------
A_Test2   | 2-3  | A_Test2
Z_Test3   | 2-3  | A_Test2
D_Test1   | 0-4  | D_Test1
F_Test2   | 0-5  | F_Test2
G_Test3   | 0-6  | G_Test3
L_Test6   | 7-9  | H_Test4
H_Test4   | 7-9  | H_Test4
Test1     | 0-1  | Test1
W_Test5   | 0-8  | W_Test5

Making Synonyms, Making Antonyms, the means or cause of success or advancement: to be the making of someone. Usually makings. capacity or potential: He has the makings of a first-rate officer. Synonyms for making at Thesaurus.com with free online thesaurus, antonyms, and definitions. Find descriptive alternatives for making.

Good Day,

You may have to tinker with this query a bit as I was not working on a table that matches yours but I think the principle should be here:

SELECT users.*
    ,CASE 
        WHEN (x.User_Name IS NOT NULL)
            THEN x.User_Name
        ELSE users.User_Name
        END AS OrderedNames
FROM Users
LEFT JOIN (
    SELECT TOP 1 RelationShip_Unique_Id
        ,User_Name
    FROM Users
    WHERE RelationShip_Unique_Id IS NOT NULL
    ) AS x ON x.RelationShip_Unique_Id = Users.RelationShip_Unique_Id
ORDER BY OrderedNames

The idea is if the Relationship_Unique_Id is the same we can perform a left join on an inline query and only select the top row, that way the derived name will be the same as the first name that appears with that id.

Making, NounEdit. making (countable and uncountable, plural makings). The act of forming, causing, or constituting; workmanship; construction. Process  Define making. making synonyms, making pronunciation, making translation, English dictionary definition of making. n. 1. a. The act of one that makes: the making of a

It is certainly simple to keep your current one relationship per user model, but if that changes in the future everything that you have done based on that will have to be scrapped. In any case, for your current model create a query that OUTER JOINS two copies of your user table and gets the name of the user, along with the name of the relationship user (if any), compares them and gets the min. This is your primary sort. Your secondary sort is the min (or max) of IDs between the two users in a relationship, to make sure if you have users with the same name their relationship partners will not get jumbled. Up to this point you have successfully grouped by relationship and sorted by the min name in each relationship. If you do a tertiary sort by pure user name, then the user with the min name within each relationship will come up first.

Making Magazine, Define MAKING (noun) and get synonyms. What is MAKING (noun)? MAKING (​noun) meaning, pronunciation and more by Macmillan Dictionary. noun the act of a person or thing that makes: The making of a violin requires great skill.

making, 15 Mistakes to Avoid When Making Pizza at Home. 812 views. 116. 4. Share. Save. Report. Pro Duration: 24:23 Posted: 54 minutes ago 175 synonyms of making from the Merriam-Webster Thesaurus, plus 287 related words, definitions, and antonyms. Find another word for making.

MAKING (noun) definition and synonyms, The Pennsylvania researcher "was on the verge of making very significant findings toward understanding the cellular mechanisms that underlie"  Making is a bi-annual theme based publication for knitters and makers. Making is a bi-annual theme based publication for knitters and makers.

15 Mistakes to Avoid When Making Pizza at Home, We believe the time has come to broaden the traditional approach to leadership and decision making and form a new perspective based on complexity science. Utilisez le dictionnaire Anglais-Français de Reverso pour traduire making et beaucoup d’autres mots. Vous pouvez compléter la traduction de making proposée par le dictionnaire Collins Anglais-Français en consultant d’autres dictionnaires spécialisés dans la traduction des mots et des expressions : Wikipedia, Lexilogos, Oxford, Cambridge, Chambers Harrap, Wordreference, Collins

Comments
  • Comments are not for extended discussion; this conversation has been moved to chat.
  • Hi , sorry for a late reply. I have tried this approach but I probably haven't done it right. I'm facing a few issues. The first is that the sorting is not working like it should, in the result it first shows the users that have no relationship and then the users with a relationship, they should be mixed and not separated. And the other issue is that I have duplicate rows of users in relationship (this is because of the left join, but I don't know how to solve it). I've included my current query in my original question.
  • "relationship_unique" column in my query is what "pair" column is in yours, it's a unique number representing a relationship.
  • This is not MS Access SQL.
  • Hi, I have tried to make something similar to this but it didn't really work for me. I've updated my post with my current query. I'd appreciate if you could see what's wrong with it.
  • You're sorting by two columns but if you do that one column is always going to take priority unless the values in the first column that is being compared are exactly the same. Try creating a derived column with the names in the order you want them using a CASE statement and putting a distinct in the top part of your query.
  • Hi, are you sure that outer joins would work? I don't think users in relationship would be included in that case. I've been using a left join in my query. Also I'm having a problem with sorts, they are separating users in a relationship and the ones without a relationship, I need them to be mixed together. I've updated my post with my current query. I'd appreciate if you could see what's wrong with it
  • You are going about it the wrong way. I would scrap your current query altogether and start working step by step rather than trying to jump to the finish. Create a query that joins two copies of the table and gives user_id, user_name, min(user_name) and min(user_id). Forget about all the other fields for now. Get rid of all the DISTINCTS and GROUPS, none of this is necessary or helpful. JOIN by other_user_in_rel not by unique_rel_id.