group by - max (position) ??

Question

group by - max (position) ??

LoosinMaMind

Hall of Fame

Points: 3366
More actions
November 30, 2010 at 9:02 am

#229694

Hi
I am trying to get the following to work so that it only selects the row with the maximum errid.
Keep getting errors, any idea how to fix
CREATE TABLE [dbo].[mfRepositoryErrors]
(
[errid] [int] NOT NULL,
[errorCode] [varchar](10) NOT NULL,
[description] [text] NULL,
[severity] [varchar](25) NULL,
[occurred] [datetime] NULL,
)
Insert into [dbo].[mfRepositoryErrors] values (1, 1003,'ERROR_LINE_MESSAGE1', 1, GETDATE ())
Insert into [dbo].[mfRepositoryErrors] values (2, 1003,'ERROR_LINE_MESSAGE1', 1, GETDATE ())
Insert into [dbo].[mfRepositoryErrors] values (3, 1003,'ERROR_LINE_MESSAGE1', 1, GETDATE ())
Insert into [dbo].[mfRepositoryErrors] values (4, 1003,'ERROR_LINE_MESSAGE1', 1, GETDATE ())
Insert into [dbo].[mfRepositoryErrors] values (5, 1003,'ERROR_LINE_MESSAGE1', 1, GETDATE ())
Insert into [dbo].[mfRepositoryErrors] values (6, 1003,'ERROR_LINE_MESSAGE1', 1, GETDATE ())
SELECT MAX(errid) AS Expr1, errorCode, severity, description, occurred
FROM mfRepositoryErrors
GROUP BY errorCode, severity, occurred
i would like to see......
6,1003,1,ERROR_LINE_MESSAGE1, GETDATE ()
thanks in advance

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

weitzera Hall of Fame Points: 3442 More actions · Answer 1

One point: Celko's noticed that your grouping set is almost assured to be distinct, so the max(id) for each set should be the max for the table. This seems to be what you wanted, but If you wanted this by grouping set you could put PARTITION BY errorCode, severity in the OVER clause.

Dan Guzman - Not the MVP (7/22/2010)
All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.'

ngreene SSC-Addicted Points: 461 More actions · Answer 2

IF all you want is the top error...

SELECT TOP 1 errid, errorCode, severity, description, occurred

FROM mfRepositoryErrors

ORDER BY errid desc

I am assuming there is a clustered index on errid, otherwise this query could perform poorly if the table is large.

sandippani SSC Eights! Points: 957 More actions · Answer 3

Can you explain your requirement clearly ...

As per my understanding I've written the below query,

select * from mfRepositoryErrors where errid in (select max(A.rn) from (select row_number() over ( partition by errorCode, severity, occurred order by errid) as Rn from mfRepositoryErrors)As A group by errorCode, severity, occurred)

Sachin Nandanwar SSCertifiable Points: 7699 More actions · Answer 4

Are you sure you are getting the same value of Getdate() for each record ?

CREATE TABLE [dbo].[mfRepositoryErrors]

(

[errid] [int] NOT NULL,

[errorCode] [varchar](10) NOT NULL,

[description] [text] NULL,

[severity] [varchar](25) NULL,

[occurred] [datetime] NULL,

)

Insert into [dbo].[mfRepositoryErrors] values (1, 1003,'ERROR_LINE_MESSAGE1', 1, GETDATE ())

Insert into [dbo].[mfRepositoryErrors] values (2, 1003,'ERROR_LINE_MESSAGE1', 1, GETDATE ())

Insert into [dbo].[mfRepositoryErrors] values (3, 1003,'ERROR_LINE_MESSAGE1', 1, GETDATE ())

Insert into [dbo].[mfRepositoryErrors] values (4, 1003,'ERROR_LINE_MESSAGE1', 1, GETDATE ())

Insert into [dbo].[mfRepositoryErrors] values (5, 1003,'ERROR_LINE_MESSAGE1', 1, GETDATE ())

Insert into [dbo].[mfRepositoryErrors] values (6, 1003,'ERROR_LINE_MESSAGE1', 1, GETDATE ())

select * from mfRepositoryErrors

drop table mfRepositoryErrors

--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server

sandippani SSC Eights! Points: 957 More actions · Answer 5

When I inserted first time it gave me the same getdate(). After your comments I tried agian and got diffrent result for getdate().

But if the requiremnet is to get the max errid details for the day we can ignore the time part and can get the group result as below

select * from mfRepositoryErrors where errid in (

select max(A.rn) from (

select row_number() over ( partition by errorCode, severity, convert(varchar,[occurred],101) order by errid) as Rn,errid,errorcode,description,severity,Convert(varchar,[occurred],101) as [occurred]

from mfRepositoryErrors)

As A group by A.errorCode, A.severity, occurred)

So far the requiremnet is not clear ....