You can get most of what you want by using MIN() and MAX() with multiple arguments. With multiple arguments, these are scalar functions that operate like LEAST() and 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 JOIN:

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

Like this:

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.

Comments

• 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 Sender Recipient combinations in Direct_Messages e.g. Gary can be a Sender and Mary a Recipient in some other row so I need to find a way to group the entries together which have the same Sender Recipient or Recipient Sender values. 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