Grouping the results of an SQL Select

Question

I'm trying to achieve what I thought was a simple grouping of results from an mssql select statement as shown below;

SELECT
    DATEPART( day, Review.[LocalTimeGenerated] ) AS DayNum,
    Review.[LocalTimeGenerated] AS LocalDate,
    ( Users.[FirstName] + ' ' + Users.[SecondName] ) AS FullName,
    SUBSTRING(
        Review.[Text],
        PATINDEX(
            '%into %',
            Review.[Text]
        ) + 5,
    50
) AS LastDoorEntered                                        /* return only the door name */

FROM [LOGS].[dbo].[Review] Review

INNER JOIN [DATA].[dbo].[TUser] Users
ON Review.[Entity_2_ID] = Users.[ID]

WHERE Review.[LocalTimeGenerated] > GETDATE() - 9           /* get the last 9 days */ 
AND Review.[Entity_3_ID] = '4503603922337793'               /* id of the door? */
AND ( Users.[FirstName] + ' ' + Users.[SecondName] ) = '[username]'

ORDER BY [Fullname] ASC, [LocalDate] DESC

With the following results;

DayNum  LocalDate                       FullName     LastDoorEntered
8       2018-03-08 07:20:08.7370000     [username]   Main Office Entrance
7       2018-03-07 08:15:31.5970000     [username]   Main Office Entrance
6       2018-03-06 14:41:43.3230000     [username]   Main Office Entrance
6       2018-03-06 08:52:15.9870000     [username]   Main Office Entrance
5       2018-03-05 08:52:45.4170000     [username]   Main Office Entrance
1       2018-03-01 14:43:12.7670000     [username]   Main Office Entrance
1       2018-03-01 13:10:29.6400000     [username]   Main Office Entrance
1       2018-03-01 12:18:57.1670000     [username]   Main Office Entrance
1       2018-03-01 11:32:17.5970000     [username]   Main Office Entrance
1       2018-03-01 10:43:04.2170000     [username]   Main Office Entrance
1       2018-03-01 08:05:00.1530000     [username]   Main Office Entrance
28      2018-02-28 15:19:22.4270000     [username]   Main Office Entrance
28      2018-02-28 13:17:04.9100000     [username]   Main Office Entrance
28      2018-02-28 12:06:50.3970000     [username]   Main Office Entrance
28      2018-02-28 08:52:09.6600000     [username]   Main Office Entrance

The trouble I'm having is that I need to return a grouped result based on the day (essentially I need to see only the first entry per day). Below is what the result needs to be;

DayNum  LocalDate                       FullName     LastDoorEntered
8       2018-03-08 07:20:08.7370000     [username]   Main Office Entrance
7       2018-03-07 08:15:31.5970000     [username]   Main Office Entrance
6       2018-03-06 08:52:15.9870000     [username]   Main Office Entrance
5       2018-03-05 08:52:45.4170000     [username]   Main Office Entrance
1       2018-03-01 08:05:00.1530000     [username]   Main Office Entrance
28      2018-02-28 08:52:09.6600000     [username]   Main Office Entrance

I was hoping that I could simply use GROUP BY DayNum, but apparently you can't group by an alias.

Invalid column name 'DayNum'.

Moving the logic down to the GROUP BY clause didn't work either (...not contained in either an aggregate function or the GROUP BY clause).

I tried doing two separate SELECT's and merging them back together by an ID column (unused in the example above), with no success. No luck with using the max trick shown on Select multiple columns from table but Group By one either.

Is there a way I can get this to work, or am I stuck doing it after the fact in the front end?

score 2 · Accepted Answer · answered Mar 08 '18 at 05:24

2

You can't group by an alias, but in most SQL implementations you can group by the output of a function. Simply group by DATEPART( day, Review.[LocalTimeGenerated] ) and it should work.

You'll of course need to wrap all your other selections in aggregation functions (e.g. min(Review.[LocalTimeGenerated]) AS LocalDate)

answered Mar 08 '18 at 05:24

Pdubbs

1,967
2
11
20

Oh my god, I must have missed one of the selections when I tried this the first time, works perfectly with everything wrapped in MAX. Cheers – Beau Mar 08 '18 at 05:29

score 2 · Answer 2 · answered Mar 08 '18 at 05:28

You can use ROW_NUMBER

SELECT * FROM (
    SELECT
        DATEPART( day, Review.[LocalTimeGenerated] ) AS DayNum,
        Review.[LocalTimeGenerated] AS LocalDate,
        ( Users.[FirstName] + ' ' + Users.[SecondName] ) AS FullName,
        SUBSTRING(
            Review.[Text],
            PATINDEX(
                '%into %',
                Review.[Text]
            ) + 5,
        50
    ) AS LastDoorEntered                                        /* return only the door name */
    , ROW_NUMBER() OVER(PARTITION BY CAST(Review.[LocalTimeGenerated] AS DATE) ORDER BY Review.[LocalTimeGenerated]) RN 

    FROM [LOGS].[dbo].[Review] Review

    INNER JOIN [DATA].[dbo].[TUser] Users
    ON Review.[Entity_2_ID] = Users.[ID]

    WHERE Review.[LocalTimeGenerated] > GETDATE() - 9           /* get the last 9 days */ 
    AND Review.[Entity_3_ID] = '4503603922337793'               /* id of the door? */
    AND ( Users.[FirstName] + ' ' + Users.[SecondName] ) = '[username]'
) T
WHERE RN = 1
ORDER BY [Fullname] ASC, [LocalDate] DESC

I had a go with this one too, while it worked perfectly as well, according to client stats it takes about 3 times longer to return (265ms for the accepted answer vs 662.5ms for above) — Beau, Mar 08 '18 at 05:38
@Beau I recommend that group the data by date, not day because if the result set has different months it will return the wrong result. — Serkan Arslan, Mar 08 '18 at 06:28
This is a good suggestion if I were pulling a couple of months data, but for my purposes I'll only ever need 9 days so this won't be an issue. Might do it anyway just in-case. — Beau, Mar 08 '18 at 20:38

Grouping the results of an SQL Select

2 Answers2