How To Sort By the MAX value in one cal

Question

Post reply

How To Sort By the MAX value in one cal

YSLGuru

SSC-Insane

Points: 21661
More actions
February 26, 2014 at 4:57 pm

#284018

I have a very simple table (DDL below) that stores audit data that includes the WHEN (DateUsed), the WHAT (ObjectName) and the WHO (UserName). Every time a user (UserName) access something (ObjectName) the DateUsed is updated with the date and time and the ObjectName & UserName are udpated with the name of the user and what object they used.
How do I construct a query that will show the data by UserName and then ObjectName but will determine the order of UserName & ObjectName by looking at the DateUsed column in descending order. I want all rows where UserName + ObjectName are the same, but I want to sorting of thos to be detemrined by which IbjectName was used most recently which would be Max(DateUsed) GROUPED BY UserName + ObjectName.
Sorting by Max(DateUsed) will sort all the rows by just DateUsed and I want Max(DateUsed) to be used only to determine how to sort UserName + ObjectName.
I apolagize for not having any sample dat but I haven't had time to create any. If Sample data is necessary to properly convey what it is I am trying to do then let me know and I will come up withs omething later.
I'm certain thers a name or term to describe this kind of sortiing I just can't think of it.
Thanks
CREATE TABLE [dbo].[AUDITTABLE](
[ID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[UserName] [varchar](256) NOT NULL,
[DateUsed] [datetime] NOT NULL,
[ObjectName] [varchar](256) NOT NULL,
CONSTRAINT [PK_AUDITTABLE] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Kindest Regards,
Just say No to Facebook!

Viewing 4 posts - 1 through 3 (of 3 total)

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Are you looking for something like this?

ORDER BY MAX(DateUsed) OVER(PARTITION BY ObjectName, UserName)

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

YSLGuru SSC-Insane Points: 21661 More actions · Answer 2

Luis Cazares (2/26/2014)
Are you looking for something like this?
ORDER BY MAX(DateUsed) OVER(PARTITION BY ObjectName, UserName)

I believe thats exactly it. Thanks Luis!

Kindest Regards,

Just say No to Facebook!

YSLGuru SSC-Insane Points: 21661 More actions · Answer 3

YSLGuru (3/4/2014)
Luis Cazares (2/26/2014)
Are you looking for something like this?
ORDER BY MAX(DateUsed) OVER(PARTITION BY ObjectName, UserName)
I believe thats exactly it. Thanks Luis!

Looks like I spoke to soon as thats not working. I need to put together some data to include and maybe that will help.

Thanks

Kindest Regards,

Just say No to Facebook!