How to select DateTime , INT , FLOAT columns when we do group by in SQL Server?

Question

Microsoft SQL Server 2014 - 12.0.2000.8 (X64) Feb 20 2014 20:04:26 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor)

I have a view where I am doing group by for 4 columns.My requirement is to show the first row data after the group is done. We have used max(Column) for all VARCHAR datatype columns but this will not work for DATETIME,INT and FLOAT. In DATETIME,INT and FLOAT type columns also we need the very first row data.

Can I know how this can be done?

Example:ID(PK)  C1  C2  C3  C4  C5
           1    2   x   y   100 20
           2    2   x   q   200 50
           3    2   y   f   300 70
           4    2   y   g   500 10
           5    8   g   h   200 30
           6    8   g   j   400 60

Required O/P    1   2   x   y   100 70
                3   2   y   f   300 80
                5   8   g   h   200 90

What are the column and table names? Also, when you say you want the "first" row, how should that first row be determined (what's the sort order)? — Katerine459

Tim Biegeleisen Tim Biegeleisen · Accepted Answer · 2018-03-28T05:44:46

Your sample data would seem to imply that the following query might work:

SELECT
    ID, col1, col2, col3, col4, col5, c5_sum
FROM
(
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY ID) rn,
        SUM(col5) OVER (PARTITION BY col1) c5_sum
    FROM yourTable
) t
WHERE rn = 1;

This assumes that col1 is the grouping column, ID is used to order records within a group, and that you want to take the sum of col5.

How to select DateTime , INT , FLOAT columns when we do group by in SQL Server?

1 Answers