CODEWITHSUNDEEP
sqlsql-server
gk1993_aal
gk1993_aal

Reputation: 41

Sorting in select as well as aggregating - SQL Server

I have used this in a NoSql database, but now I need similar in SQL Server if possible.

I have following data:

ID Code Created Comment Priority
1 'CodeA' 2021-07-11 23:02:01 343 1
2 'CodeB' 2021-07-11 23:02:01 3212 2
1 'CodeD' 2021-07-11 23:02:02 sdad3a 3
1 'CodeA' 2021-07-11 23:02:03 344 2
1 'CodeA' 2021-07-11 23:02:00 345 3
2 'CodeE' 2021-07-11 23:02:03 Null 4
1 'CodeB' 2021-07-11 23:02:03 346 3

I would like to group by ID and:

  1. have a column Date containing value from Created sorted by priority desc and Created asc but only if Code = 'A' or Code = 'CodeP'

  2. have another column Comment sorted by Priority asc, created asc only for code 'CodeB'

etc.

For example for id = 1:

Id   Date                 Comment
-------------------------------------    
1   2021-07-11 23:02:00   |346

Currently I have started to try using groupBy + case clause in select clause but I am wondering if there is better way?

I have working solution with derived queries in the select for every column but that is neither smart and the query becomes very slow due to multiple queries as well as sometimes I need to further process data which requires another query and longer time fx.

I need to process the field comment in relation to different type of values so I am not happy with this approach currently.

PS: working solution is using derived table I believe its called or subquery in select:

select 
    id, 
    (select top 1 created 
     from tableA 
     where Code = 'CodeA' or Code = 'CodeP' 
     order by Priority desc, created asc) as Date,
    (select top 1 comment 
     from tableA 
     where Code = 'CodeA' or Code = 'CodeP' 
     order by Priority asc, created desc) as Comment
etc..

Upvotes: 1

Views: 55

Answers (1)

Charlieface
Charlieface

Reputation: 71578

You can use FIRST_VALUE in a derived table, then GROUP BY on the outside.

I'm assuming here that you don't want any other results than those CodeA/CodeP rows

SELECT
    t.ID,
    [Date] = MIN([Date]),
    Comment = MIN(Comment)
FROM (
    SELECT *,
        [Date] = FIRST_VALUE(t.Created) OVER (PARTIION BY t.ID ORDER BY t.Priority DESC, t.Created ASC
                   ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLOWING),
        [Comment] = FIRST_VALUE(t.Comment) OVER (PARTIION BY t.ID ORDER BY t.Priority ASC, t.Created ASC
                   ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLOWING)
    FROM tableA t
    WHERE t.Code IN ('CodeA', 'CodeP')
) t
GROUP BY t.ID;

You can also use ROW_NUMBER to the same effect

SELECT
    t.ID,
    [Date] = MIN(CASE WHEN rnForDate = 1 THEN [Date] END),
    Comment = MIN(CASE WHEN rnForComment = 1 THEN Comment END)
FROM (
    SELECT *,
        rnForDate = ROW_NUMBER() OVER (PARTIION BY t.ID ORDER BY t.Priority DESC, t.Created ASC),
        rnForComment = ROW_NUMBER() OVER (PARTIION BY t.ID ORDER BY t.Priority ASC, t.Created ASC)
    FROM tableA t
    WHERE t.Code IN ('CodeA', 'CodeP')
) t
GROUP BY t.ID;

Upvotes: 1

Related Questions