Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Get last row based on duplicate column Expand / Collapse
Author
Message
Posted Friday, October 26, 2012 2:24 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, April 11, 2014 7:07 AM
Points: 45, Visits: 197
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.
Post #1377845
Posted Friday, October 26, 2012 2:29 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, April 17, 2014 3:16 PM
Points: 5,986, Visits: 6,931
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
Post #1377849
Posted Friday, October 26, 2012 2:42 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, April 11, 2014 7:07 AM
Points: 45, Visits: 197
That did the trick.
Thank you very much.
Post #1377862
Posted Friday, October 26, 2012 2:44 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:21 PM
Points: 5,472, Visits: 23,525
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
Post #1377865
Posted Sunday, October 28, 2012 1:23 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, January 31, 2014 4:06 AM
Points: 83, 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





Post #1378002
Posted Sunday, October 28, 2012 2:57 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 12:40 AM
Points: 1,059, Visits: 5,758
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
Post #1378006
Posted Tuesday, October 30, 2012 6:14 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, April 17, 2014 3:16 PM
Points: 5,986, Visits: 6,931
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
Post #1379108
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse