Menu
What I have is basically a problem which is easily solved with multiple tables, but I have only a single table to do it.
Consider the following database table
UserID UserName EmailAddress Source
3K3S9 Ben [email protected] user
SF13F Harry [email protected] 3rd_party
SF13F Harry [email protected] user
76DSA Lisa [email protected] user
OL39F Nick [email protected] 3rd_party
8F66S Stan [email protected] user
I need to select all fields, but only who each user once along with one of their email addresses (the "biggest" one as determined by the MAX() function). This is the result I am after ...
UserID UserName EmailAddress Source
3K3S9 Ben [email protected] user
SF13F Harry [email protected] 3rd_party
76DSA Lisa [email protected] user
OL39F Nick [email protected] 3rd_party
8F66S Stan [email protected] user
As you can see, "Harry" is only shown once with his "highest" email address the correcponding "source"
Currently what is happening is that we are grouping on the UserID, UserName, and using MAX() for the EmailAddress and Source, but the max of those two fields dont always match up, they need to be from the same record.
I have tried another process by joining the table with itself, but I have only managed to get the correct email address but not the corresponding "source" for that address.
Any help would be appreciated as I have spent way too long trying to solve this already :)
731
The MAX() function can be used on the string column. For example, the following uses the MAX() function on the LastName column of the Employee table. It will sort the column alphabetically and the last value will be returned.
Try using this SQL SELECT statement: SELECT * FROM employees WHERE department_id=30 AND salary = (SELECT MAX(salary) FROM employees WHERE department_id=30); This will return the employee information for only the employee in department 30 that has the highest salary.
The MAX() function is used with the WHERE clause to gain further insights from our data. In SQL, the MAX() function computes the highest or maximum value of numeric values in a column.
SUM() and MAX() at the same time Notice that all aggregate functions except COUNT(*) ignore the NULL Rating for the ID=5 row. COUNT(*) counts rows, whereas COUNT(col) counts non-null values. So to answer your question, just go ahead and use SUM() and MAX() in the same query.
If you're on SQL Server 2005 or higher,
SELECT UserID, UserName, EmailAddress, Source
FROM (SELECT UserID, UserName, EmailAddress, Source,
ROW_NUMBER() OVER (PARTITION BY UserID
ORDER BY EmailAddress DESC)
AS RowNumber
FROM MyTable) AS a
WHERE a.RowNumber = 1
Of course there are ways to do the same task without the (SQL-Standard) ranking functions such as ROW_NUMBER, which SQL Server implemented only since 2005 -- including nested dependent queries and self left joins with an ON including a '>' and a WHERE ... IS NULL trick -- but the ranking functions make for code that's readable and (in theory) well optimizable by the SQL Server Engine.
Edit: this article is a nice tutorial on ranking, but it uses RANK in the examples instead of ROW_NUMBER (or the other ranking function, DENSE_RANK) -- the distinction matters when there are "ties" among grouped rows in the same partition according to the ordering criteria. this post does a good job explaining the difference.
166
select distinct * from table t1
where EmailAddress =
(select max(EmailAddress) from table t2
where t1.userId = t2.userId)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
