Retrieving rows with unique data in one field

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Retrieving rows with unique data in one field

Author

Topic

Buzz50
Starting Member

1 Post

Posted - 2012-01-11 : 10:49:22

Hi,

I am using the query below to retrieve rows where I get just the most recent row for each email field.
It is very slow!
Any ideas?
The qid is a unique identifier.

Thanks in advance!

SELECT qid, postcode_area, vehicle_group, premium, lic_years, ncb, proposer_age, email, created
FROM Mainstreet.dbo.leads l
WHERE created >= '01 January 2012 00:00:00.000'
AND created <= '06 January 2012 23:00:00.000'
AND scheme_id = 'PCH3'
AND proposer_age >= 21
AND proposer_age <= 29
AND lic_years >= 1
AND lic_years <= 5
AND ncb >= 1
AND ncb <= 5
AND id = (SELECT MAX(id) AS Expr1
FROM Mainstreet.dbo.leads le
WHERE(le.email = l.email)
)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-11 : 11:13:41

instead of subquery try using a join on MAX(id) value and check

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-11 : 11:21:20

What's the key of the table? Substitute that for the word "key" below

SELECT *
FROM Mainstreet.dbo.leads o
WHERE EXISTS (SELECT * FROM Mainstreet.dbo.leads i
WHERE o.key = i.key
GROUP BY i.key
HAVING o.created = MAX(i.created))

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources