'SQL Group/Orderby

I have a set of records in a db table like this. The thing that logically links these records is guid1 and guid2 but not all records in the table have a full reference to guid1 and guid2. I'd like to be able to group the records based on the guid1/guid2 link and order them by timestamp asc. So this table ...

timestamp                      guid1                                  guid2                             text
2022-05-06T10:00:31.5767324Z | cb73c58e-e36b-4fe3-8663-33027ba2afc7 | null                            | abc1  
2022-05-06T10:00:31.5767324Z | ec5d1b9395444a06a36130a9d62ae2c5     | null                            | abc2
2022-05-06T10:01:31.5767324Z | cb73c58e-e36b-4fe3-8663-33027ba2afc7 | b7ef78cde158437fb65a6878ca908751| def1
2022-05-06T10:01:31.5767324Z | ec5d1b9395444a06a36130a9d62ae2c5     | 206eb977459c4f91bafb9b798f5d60c4| def2
2022-05-06T10:02:31.5767324Z | null                                 | b7ef78cde158437fb65a6878ca908751| ghi1
2022-05-06T10:02:31.5767324Z | null                                 | 206eb977459c4f91bafb9b798f5d60c4| ghi2

... becomes this set of query results

timestamp                      guid1                                  guid2                             text
2022-05-06T10:00:31.5767324Z | cb73c58e-e36b-4fe3-8663-33027ba2afc7 | null                            | abc1  
2022-05-06T10:01:31.5767324Z | cb73c58e-e36b-4fe3-8663-33027ba2afc7 | b7ef78cde158437fb65a6878ca908751| def1
2022-05-06T10:02:31.5767324Z | null                                 | b7ef78cde158437fb65a6878ca908751| ghi1

2022-05-06T10:00:31.5767324Z | ec5d1b9395444a06a36130a9d62ae2c5     | null                            | abc2
2022-05-06T10:01:31.5767324Z | ec5d1b9395444a06a36130a9d62ae2c5     | 206eb977459c4f91bafb9b798f5d60c4| def2
2022-05-06T10:02:31.5767324Z | null                                 | 206eb977459c4f91bafb9b798f5d60c4| ghi2
sqlsql-servergroup-bysql-order-by


Solution 1:[1]

Create a temp table with not null of guid1 & guid2 and a full outer join with the table gives the result. See if this helps.

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=38398964f9c22bbb95caea17925bda3d

Solution 2:[2]

SELECT MAX(TEXT),MAX(timestamp),guid1,guid2 FROM TABLE_NAME ORDER BY timestamp

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Praveen
Solution 2 Imran Kabir

Related Questions