SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Get last row based on duplicate column


Get last row based on duplicate column

Author
Message
jamie_collins
jamie_collins
SSC-Enthusiastic
SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)

Group: General Forum Members
Points: 109 Visits: 244
I have a table that contains:
userid email name
1 a@a.com Bill
2 a@a.com Billy
3 a@a.com William
4 b@b.com John
5 b@b.com Johnny
6 c@c.com Jimmy
7 c@c.com Jim

I am trying to get the output so that it prints only the last line with the matching email:

userid email name
3 a@a.com William
5 b@b.com Johnny
7 c@c.com Jim

I have this:

SELECT DISTINCT *
FROM USERS AS A
INNER JOIN (
SELECT USERID, EMAIL, NAME
FROM USERS
GROUP BY USERID, EMAIL NAME
HAVING COUNT(*) > 1) AS B
ON A.EMAIL = B.EMAIL

But I get nothing back.
What am I missing?

Thanks for all help.
Evil Kraig F
Evil Kraig F
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8779 Visits: 7660
If you setup test data this becomes easier, check out the first link in my sig for what we'd prefer when we do code assistance.

In general, what you're dealing with is getting the last detail from a logging table. This is usually done by grouping on the identifiers (in this case, email), then using MAX() on a 'last row determinator', in this case userID... then joining the result of that back to the original.

I don't have code but basically, it'll look like this:

SELECT
t.UserID,
t.Email,
t.Name
FROM
(SELECT
Email,
Max(UserID) AS MaxID
FROM
Table
GROUP BY
Email
) AS drv
JOIN
Table AS t
ON drv.Email = t.Email
AND drv.MaxID = t.UserID




- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
jamie_collins
jamie_collins
SSC-Enthusiastic
SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)

Group: General Forum Members
Points: 109 Visits: 244
That did the trick.
Thank you very much.
bitbucket-25253
bitbucket-25253
SSCrazy Eights
SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)

Group: General Forum Members
Points: 8011 Visits: 25280
This might do it for you:
CREATE TABLE #T(userid INT, emailname VARCHAR(50))
INSERT INTO #T
SELECT 1, 'a@a.com Bill' UNION ALL
SELECT 2, 'a@a.com Billy' UNION ALL
SELECT 3, 'a@a.com William' UNION ALL
SELECT 4, 'b@b.com John' UNION ALL
SELECT 5, 'b@b.com Johnny' UNION ALL
SELECT 6, 'c@c.com Jimmy' UNION ALL
SELECT 7, 'c@c.com Jim'
;WITH cte
as (select row_number() over(partition by substring(emailname,1,7) order by userid DESC) as rn,emailname
from #T)
select * from cte where rn = 1
Results:
rn emailname
1 a@a.com William
1 b@b.com Johnny
1 c@c.com Jim



If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
BriPan
BriPan
SSC-Enthusiastic
SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)

Group: General Forum Members
Points: 149 Visits: 296
just go with practical scenario it may happen that email id same but domain different

in that case Just remove substring from "bitbucket-25253" solution


declare @T TABLE
(userid INT, emailname VARCHAR(50),name varchar(10))
INSERT INTO @T
SELECT 1, 'test123test@b.com','Bill' UNION ALL
SELECT 1, 'test123test@a.com','Billiiiii' UNION ALL
SELECT 2, 'a@a.com','Billy' UNION ALL
SELECT 3, 'a@a.com','William' UNION ALL
SELECT 4, 'b@b.com','John' UNION ALL
SELECT 5, 'b@b.com','Johnny' UNION ALL
SELECT 6, 'c@c.com','Jimmy' UNION ALL
SELECT 7, 'c@c.com','Jim'

;WITH cte
as (select row_number() over(partition by emailname order by userid DESC) as rn,emailname ,name
from @T)
select * from cte where rn = 1






ChrisM@home
ChrisM@home
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2113 Visits: 10387
This has already been answered but it's interesting that the logical requirement exactly matches an existing function which isn't used in any of the solutions offered. What you're looking for - the logical requirement - is the maximum userid per partition of email, and the function is;

MAX(userid) OVER(PARTITION BY email)


Here's the query;
;WITH SampleData (userid, email, name) AS (
SELECT 1, 'a@a.com', 'Bill' UNION ALL
SELECT 2, 'a@a.com', 'Billy' UNION ALL
SELECT 3, 'a@a.com', 'William' UNION ALL
SELECT 4, 'b@b.com', 'John' UNION ALL
SELECT 5, 'b@b.com', 'Johnny' UNION ALL
SELECT 6, 'c@c.com', 'Jimmy' UNION ALL
SELECT 7, 'c@c.com', 'Jim'
),

MyQuery AS (
SELECT userid, email, name,
ChosenUserID = MAX(userid) OVER(PARTITION BY email)
FROM SampleData
)
SELECT userid, email, name
--, ChosenUserID
FROM MyQuery
WHERE userid = ChosenUserID
ORDER BY userid


It's unlikely that it will perform any different to the other solutions, but you can deduce - at a swift glance - the intent of the code.


Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Evil Kraig F
Evil Kraig F
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8779 Visits: 7660
ChrisM@home (10/28/2012)
This has already been answered but it's interesting that the logical requirement exactly matches an existing function which isn't used in any of the solutions offered. What you're looking for - the logical requirement - is the maximum userid per partition of email, and the function is;


I always seem to forget about doing that, as I've found it to be relatively non-performant in 2k5 where I do most of my work... and I'm a creature of habit, so I use the methods I've been using since 7.0 until someone can show me the other way is faster. :-)


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search