How to group rows by either permutation of a two column composite key
on which columns you should create indexes in oracle
types of indexes in oracle 11g with example
how to use index in select query in oracle
bitmap index in oracle
b-tree index in oracle
composite index in oracle
index on multiple columns oracle
Not sure the wording for the question is as clear as it should be (it's the best I could come up with) but here's an example to clear things up. I have a view,
Chats, which is supposed to summarize the conversation history between two people. The view comprises of the following columns:
Chats view's columns are all derived from a table,
Direct_Messages which stores details about individual chat messages exchanged between users of the system. Here are its columns:
TimeRead (is null if message hasn't been read by recipient). The view's
LatestMessage columns have the values of the latest direct message between the two participants (latest by
The problem is really stemming from the fact that only one permutation of the
Recipient composite columns should exist in the
Chats view i.e. that of the latest exchange between the two participants. For example, if Gary sent a 'Hi' message to Barry, then Barry replied with 'Hello'--the only entry in
Chats between these two guys should have the
Sender as 'Barry', the
Recipient as 'Gary', the
Timestamp as the timestamp of Barry's reply, the
LatestMessage as 'Hello' and the UnreadMessageCount as the number of messages the
Recipient hasn't read.
I have tried to use
GROUP BY "Sender", "Recipient" OR "Recipient", "Sender" but it just returns two columns: one grouped by Barry, Gary; and the other grouped by Gary, Barry
Here's my code:
SELECT Sender AS Sender, Recipient AS Recipient, Timestamp AS Timestamp, Body AS LatestMessage, (SUM(CASE WHEN TimeRead IS NULL THEN 1 ELSE 0 END) ) AS UnreadMessageCount FROM Direct_Messages GROUP BY Sender, Recipient OR Recipient, Sender ORDER BY Timestamp DESC
EDIT: Here's the sample data in the
Direct_Messages table and the corresponding output in the
ID Sender Recipient Body Timestamp TimeRead 148567984 Gary Barry Hi 2018-12-12 23:53:39.487 2018-12-12 23:55:45 1668701120 Barry Gary Hello 2018-12-12 23:54:49.326 NULL
Sender Recipient Timestamp LatestMessage UnreadMessageCount Gary Barry 2018-12-12 23:53:39.487 Hi 0 Barry Gary 2018-12-12 23:54:49.326 Hello 1
You can get most of what you want by using
MAX() with multiple arguments. With multiple arguments, these are scalar functions that operate like
GREATEST() in other databases:
SELECT MIN(Sender, Recipient) AS u1, MAX(Sender, Recipient) AS u2, MAX(Timestamp) AS Timestamp, -- Body AS LatestMessage, (COUNT(*) - COUNT(TimeRead)) as UnreadMessageCount FROM Direct_Messages_cooked GROUP BY u1, u2 ORDER BY MAX(Timestamp) DESC
The challenge is getting the latest method. You can get this with conditional aggregation and an additional
SELECT MIN(dmc.Sender, dmc.Recipient) AS u1, MAX(dmc.Sender, dmc.Recipient) AS u2, MAX(dmc.Timestamp) AS Timestamp, MAX(CASE WHEN dmc.Timestamp = dmc2.Timestamp THEN Body END) AS LatestMessage, (COUNT(*) - COUNT(dmc.TimeRead)) as UnreadMessageCount FROM Direct_Messages_cooked dmc JOIN (SELECT MIN(Sender, Recipient) AS u1, MAX(Sender, Recipient) AS u2, MAX(Timestamp) AS Timestamp FROM Direct_Messages_cooked GROUP BY u1, u2 ) dmc2 ON dmc2.u1 = MIN(dmc.Sender, dmc.Recipient) AND dmc2.u2 = MAX(dmc.Sender, dmc.Recipient) GROUP BY u1, u2 ORDER BY dmc2.Timestamp DESC
Questions, How to group rows by either permutation of a two column composite key · sql database sqlite group-by · Jan 16 '19 at 18:11 Garikai. 0. 0 You can supply the data, which does not have to be extensive, merely representative, in one of two ways: 1. You can post rows from each table here as comma or tab delimited text, with the column names as the first line, terminating each line with a carriage return, which can be copied and imported into a table. 2.
You can 'pre-coock' your data in order to have always messages in same direction from every combination of users.
Sample, if your data is:
Sender Recipient A ---> B B ---> A
You change it to:
U1 U2 B ---> A (changed) B ---> A
SELECT (case when Sender > Recipient then Sender else Recipient end) AS u1, (case when Sender > Recipient then Recipient else Sender end) AS u2, Timestamp AS Timestamp, Body AS LatestMessage, (SUM(CASE WHEN TimeRead IS NULL THEN 1 ELSE 0 END) ) AS UnreadMessageCount FROM Direct_Messages_cooked GROUP BY (case when Sender > Recipient then Sender else Recipient end), (case when Sender > Recipient then Recipient else Sender end) ORDER BY Timestamp DESC
Notice: Be careful with performance ( I guess it is not important because you are tagged question as sqlite )
You can use CTE to pre-coock your data and get a more readable query:
with Direct_Messages_coocked as ( select (case when Sender > Recipient then Sender else Recipient end) AS U1, (case when Sender > Recipient then Recipient else Sender end) AS U2, * from Direct_Messages ) SELECT U1 AS U1, U2 AS U2, Timestamp AS Timestamp, Body AS LatestMessage, (SUM(CASE WHEN TimeRead IS NULL THEN 1 ELSE 0 END) ) AS UnreadMessageCount FROM Direct_Messages_coocked GROUP BY U1, U2 ORDER BY Timestamp DESC
MySQL composite key - unique combinations, Suppose there is table with columns a and b , and with composite unique key from a,b pair. And there is row a=1, b=2 . So I can not insert another To do this, you pass the column names you wish to group by as a list: # Group by two columns df = tips.groupby (['smoker','time']).mean () df Looking at the above, you may be able to see that both 'smoker' and 'time' are indices of df.
Building on @Gordon Linoff's and @dani herrera's insightful answers I managed to tweak about and come up with a succinct solution to my specific problem, though in the broader context of my initial question, @Gordon's answer seems, from my observation, to address the problem more fully. Here's what I managed to come up with:
SELECT Sender AS Sender, Recipient AS Recipient, Timestamp AS Timestamp, Body AS LatestMessage, (COUNT( * ) - COUNT(TimeRead) ) AS UnreadMessageCount FROM Direct_Messages GROUP BY ( SELECT MAX(Sender, Recipient) ), ( SELECT MIN(Sender, Recipient) ) ORDER BY Timestamp DESC
Advances in Cryptology -- CRYPTO 2012: 32nd Annual Cryptology , The security of multiple-encryption schemes had been analyzed for more than 30 most of the published papers had dealt with either double or triple encryption (which from the next group to the accumulated sum computed in the previous row. in the various columns of this Efficient Dissection of Composite Problems 721. Learn How to Combine Data with a CROSS JOIN A cross join is used when you wish to create a combination of every row from two tables. All row combinations are included in the result; this is commonly called cross product join.
Indexes and Index-Organized Tables, Multiple indexes can exist for the same table if the permutation of columns differs Oracle Database does not index table rows in which all key columns are null, except are either M or F . Assume that a composite index exists on the columns In general, index keys have two pieces, a grouping piece and a unique piece. Let's say I have a table with a two-part composite key, and 4 records, like the following: KEY_PART_1 KEY_PART_2 A 1 B 1 C 2 C 3 I want to write some dynamic SQL to select only the records B,1 and C,2 using a "WHERE IN" clause, without selecting A,1 or C,3. Is there some way to do this without a temp table?
Microsoft Encarta College Dictionary: The First Dictionary For The , col-umn inch n an area on a page one column wide and one inch deep, used to n either of two great circles on the celestial sphere that intersect at the celestial poles, with a row of long thin teeth, used to arrange hair 2 FASTENING FOR HAIR a piece to search an area thoroughly o We combed the house for his keys. The find on which records are sorted is called the composite primary key. False A column whose value uniquely identifies a given row in the table is the secondary key.
SQL for Aggregation in Data Warehouses, Dropping and Creating Attributes with Columns · Using Multiple Hierarchies While Creating Joins About Composite Columns and Grouping One of the key concepts in decision support systems is multidimensional analysis: Values from the database will be either a real value such as "Internet" or a stored NULL . Entity integrity is the condition in which each row (entity instance) in the table has its own unique identity. To ensure entity integrity, the primary key has two requirements: (1) all of the values in the primary key must be unique. (2) no key attribute in the primary key can contain a null.
- That GROUP BY makes no sense at all... Show us some sample table data and the expected result - all as formatted text, not images.
- You typically GROUP BY the columns you select, except those who are arguments to set functions.
- @jarlh Put in some edits
- Don't you get the expected result if you skip the GROUP BY, and the SUM aggregation?
- @jarlh No, thing is there are lots of
Direct_Messagese.g. Gary can be a
Senderand Mary a
Recipientin some other row so I need to find a way to group the entries together which have the same
Sendervalues. Then as for the SUM aggregation--I need it to be able to count the NULL
TimeReads i.e. to count the unread messages
- Will have to take a minute to take all that in before I can test it--then will get back at ya. Just one question, is the whole 'cooking' thing some sort of established practice for this kind of problem or something?
- Will have to test it out and mentally digest the code, will feedback when that's done