January 11, 2012 at 9:12 am
I have yet to come up with a simple but fast way of retrieving rows from a table that have unique values in one or sometimes more than one column.
What is the best/fastest solution?
The example below retrieves one record per unique email but is very slow.
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)
)
It is fast without the
AND id = (SELECT MAX(id) AS Expr1
FROM Mainstreet.dbo.leads le
WHERE(le.email = l.email)
)
January 11, 2012 at 9:50 am
mike.barrett (1/11/2012)
I have yet to come up with a simple but fast way of retrieving rows from a table that have unique values in one or sometimes more than one column.What is the best/fastest solution?
The example below retrieves one record per unique email but is very slow.
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)
)
It is fast without the
AND id = (SELECT MAX(id) AS Expr1
FROM Mainstreet.dbo.leads le
WHERE(le.email = l.email)
)
have you tried replacing the max() with a top?
--not sure what your datatype is for id, but:
declare @userid int;
declare @email nvarchar(255);
--set @email to email you want
--then set @userid to the email (below)
set @userid = (
SELECT Top 1 id
FROM MainStreet.dbo.leads
WHERE email = @email
ORDER BY id desc
)
--then change your code at the end
AND ncb >= 1
AND ncb <= 5
AND id = @userid
January 11, 2012 at 10:02 am
Ooo. Sorry, I think I missinterpreted your requirements.
Try this instead:
SELECT qid, postcode_area, vehicle_group, premium, lic_years, ncb, proposer_age, email, created
FROM Mainstreet.dbo.leads l
---------------
--try this join
inner join (
SELECT distinct id
FROM Mainstreet.dbo.leads
) le
on l.email = le.email
---------------
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
--Remove the below AND and sub-query
--AND id = (
--SELECT MAX(id) AS Expr1
-- FROM Mainstreet.dbo.leads le
-- WHERE(le.email = l.email)
--)
January 13, 2012 at 9:12 am
stephen99999 (1/11/2012)
Ooo. Sorry, I think I missinterpreted your requirements.Try this instead:
SELECT qid, postcode_area, vehicle_group, premium, lic_years, ncb, proposer_age, email, created
FROM Mainstreet.dbo.leads l
---------------
--try this join
inner join (
SELECT distinct id
FROM Mainstreet.dbo.leads
) le
on l.email = le.email
---------------
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
--Remove the below AND and sub-query
--AND id = (
--SELECT MAX(id) AS Expr1
-- FROM Mainstreet.dbo.leads le
-- WHERE(le.email = l.email)
--)
I think this still doesn't fit the OP's requirements if there's more than one ID for each email in Mainstreet.dbo.leads.
This may speed things up:
SELECT l.qid, l.postcode_area, l.vehicle_group, l.premium, l.lic_years, l.ncb, l.proposer_age, l.email, l.created
FROM Mainstreet.dbo.leads l
CROSS APPLY (SELECT MAX(le.id) AS id
FROM Mainstreet.dbo.leads le
WHERE le.email = l.email) as le2
WHERE l.created >= '01 January 2012 00:00:00.000'
AND l.created <= '06 January 2012 23:00:00.000'
AND l.scheme_id = 'PCH3'
AND l.proposer_age BETWEEN 21 AND 29
AND l.lic_years BETWEEN 1 AND 5
AND l.ncb BETWEEN 1 AND 5
AND l.id = le2.id
If the query still runs slowly, I would suggest a closer look at the indexes on the tables. With appropriate indexes, this query should be speedy.
Jason Wolfkill
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply