CODEWITHSUNDEEP
sqlsql-serverdistinctaggregate-functions
Thomas Langston
Thomas Langston

Reputation: 3735

How can I combine sql aggregate functions with distinct more cleanly?

I've been running into the following case repeatedly lately, either I need to apply MAX() or SUM() to one column on a table, but I need DISTINCT sets of values for other columns.

For example consider the following table and associated columns representing details of a login to StackOverflow.

SoUserLogins (OpenIdToken, Name, IpAdress, LoginTimeStamp, QuestionsAsked)

I might want a result set containing users and their last login.

SELECT DISTINCT
  OpenIdToken,
  MAX(LoginTimeStamp)
INTO #tmpLastLogin
FROM SoUserLogins
GROUP BY OpenIdToken

But I need distinct values from the other columns. I'm going to wrap this in a common table expression (CTE) because I'm going to use it later and don't want to have to clean up another temporary table.

;WITH tmpLastLogin_CTE AS (
SELECT DISTINCT
  SOL.OpenIdToken, SOL.Name, SOL.IpAdress, SOL.QuestionsAsked
  TTL.LastLogin
FROM SoUserLogins SOL
JOIN #tmpLastLogin TLL ON SOL.OpenIdToken = TLL.OpenIdToken
)
--Extra SQL using tmpLastLogin_CTE goes here

You could change the MAX(LoginTimeStamp) to SUM(QuestionsAsked) in the code above with a few extra tweaks to see a similar example.

My question, is there a cleaner or more elegant way to deal with these situations?

I am using SQL Server.

Upvotes: 2

Views: 5750

Answers (2)

rasata
rasata

Reputation: 429

If you are under Oracle Database you should consider using Custom *Oracle Analytical Function* which let you agreggate your data by row depending on a column.

As I was writing this, Martin did answer.

But some documentation could be found here (in french)

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453910

Do you mean this?

SELECT DISTINCT
  SOL.OpenIdToken, SOL.Name, SOL.IpAdress, SOL.QuestionsAsked,
  MAX(LoginTimeStamp) OVER (PARTITION BY OpenIdToken) AS LastLogin
FROM SoUserLogins SOL

So you will have multiple rows per OpenIdToken and the same LastLogin value will be repeated for all rows within a group?

Upvotes: 10

Related Questions