I have a table like this (unsorted): risk category Low A Medium B High C Medium A Low B High A Low C Low E Low D High B I need to sort rows by category, but first based on the value of risk. The desired result should look like this (sorted): risk category Low A Low B Low C
Tag: postgresql
Postgresql query: update status of limit number of records based on group size
I have a postgresql table contains a list of email addresses. The table has three columns, Email, EmailServer (e.g., gmail.com, outlook.com, msn.com, and yahoo.com.ca etc.), and Valid (boolean). Now, I want to group those emails by EmailServer and then update the first 3 records of each large group (count >…
SQL SELECT statement with a many-to-many extra table
I have a table that links users. Consider the following: The user_links table can have 0 rows for a particular user_id, given the user doesn’t have linked users, so a select statement can return either a row or NULL. The approach with left join user_links ul on ul.user_id = contracts.user_id OR ul.linke…
ORDER BY DESC with LIMIT doesn’t return the newest rows, unless I ORDER BY twice
I am writing a chat app and want to get a list of recent contacts to show in the sidebar. My table has the standard chat fields (sender, receiver, msg, date). I’m currently getting a list of recent contacts by getting a list of messages with distinct (sender, receiver) pair: The result returned is wrong…
I want to sum the lengths when PROPRIETAI = ‘PRIVE’ in the same line
resultat of the code I want to sum the lengths when PROPRIETAI = ‘PRIVE’ in the same line with sql and this is my code : and I want this table resultat I try to make some changes but it’s not working Answer Replace all values of type_struc with NULL when proprietai = ‘PRIVE’ That…
Postgresql find by two columns
I have join table tags_videos How can I select distinct video_id who have two specific tag_id For example my tag_ids is 1195 and 1198, i should get video_ids 15033 and 15036 (who have 1195 and 1198 tag_id) Answer Extract the unique (tag_id, video_id) pairs for the two tags in t CTE and select these video_id&#…
PostgreSQL – I have a Syntax error in my SUBSTRING query
I’m trying to make use of the SUBSTRING() function to extract a substring from vm.location_path, starting at the second character and ending at the position of the ‘]’ character, minus two. I want to extract the text between the square brackets ([]) in vm.location_path but I’m hitting …
How do I correctly map letters in the database?
I have two tables. One table with the letters of different countries and a second table with a mapping of these letters to each other. I need to make a query to get the mapped letters of the two languages. Can you tell me how this can be done optimally? The letter table id letter language 1 A en 2
What is a runtime complexity of this sql query?
I would like to know how fast is SELECT * FROM user_table WHERE email = ‘test@gmail.com’ is this O(1) or O(n)? how does sql search for a particular row? Answer If there is no index on “email” column, the search complexity is O(N). If there was hash-based index on an “email”…
How to do conditional aggregate based on values of another column?
I have a table called device_data that looks like below It basically stores the packet drops, jitter, latency on a minute basis. Now there is another column called alert that holds the value HIGH, MEDIUM and LOW based on a threshold and holds empty string if the threshold is not met. Now I do an hourly averag…