patternsqlMinor
Which is the most efficient way to run a particular select query?
Viewed 0 times
thequeryefficientwayselectwhichparticularmostrun
Problem
If I run the following code:
I get the following results:
All I really want to retrieve though is the policynumber and the maximum decpageid (which in this case would be 41) along with the Risk numbers (Which should be 1 and 2)
The query is also returning decpageid 37 even though it is not the maximum decpageid for the policynumber because it has a different risk.
The results I would like returned are:
I have figured out 2 different queries I can use to return my desired results but I don't think they are the most efficient. The queries I came up with are:
This returns the desired results but I don't want to have to specify the policy number more then once in the query. Is there a way to call the policynumber into the sub query from the outer query?
The other query I came up with was:
I like this query because I only have to specify the policynumber 1 time and I can also expand the query so I can return the info for multiple policynumbers.
What I need to know is
select PolicyNumber, MAX(decpageid) as decpageid, Risk
from StatRiskDecpages
where PolicyNumber = 'AR-0000301132-04'
group by PolicyNumber, RiskI get the following results:
PolicyNumber decpageid Risk
AR-0000301132-04 41 1
AR-0000301132-04 41 2
AR-0000301132-04 37 3All I really want to retrieve though is the policynumber and the maximum decpageid (which in this case would be 41) along with the Risk numbers (Which should be 1 and 2)
The query is also returning decpageid 37 even though it is not the maximum decpageid for the policynumber because it has a different risk.
The results I would like returned are:
PolicyNumber DecpageID Risk
AR-0000301132-04 41 1
AR-0000301132-04 41 2I have figured out 2 different queries I can use to return my desired results but I don't think they are the most efficient. The queries I came up with are:
select PolicyNumber, MAX(decpageid) as decpageid, Risk
from StatRiskDecpages
where
PolicyNumber = 'AR-0000301132-04'
and decpageid = (
select MAX(decpageid)
from StatRiskDecpages
where PolicyNumber = 'AR-0000301132-04'
)
;This returns the desired results but I don't want to have to specify the policy number more then once in the query. Is there a way to call the policynumber into the sub query from the outer query?
The other query I came up with was:
select t1.PolicyNumber,t2.DecpageID, t2.Risk
from (
select PolicyNumber, MAX(decpageid) as decpageid
from StatRiskDecpages
where PolicyNumber = 'AR-0000301132-04'
group by PolicyNumber
) as t1
left join StatRiskDecpages as t2
on t1.PolicyNumber = t2.PolicyNumber
and t1.decpageid = t2.DecpageID
;I like this query because I only have to specify the policynumber 1 time and I can also expand the query so I can return the info for multiple policynumbers.
What I need to know is
Solution
I'd probably use
Assuming the covering index on
SELECT TOP (1) WITH TIES PolicyNumber,
decpageid,
Risk
FROM StatRiskDecpages
WHERE PolicyNumber = 'AR-0000301132-04'
ORDER BY decpageid DESCAssuming the covering index on
(PolicyNumber, decpageid) INCLUDE(Risk) this will give you a plan likeCode Snippets
SELECT TOP (1) WITH TIES PolicyNumber,
decpageid,
Risk
FROM StatRiskDecpages
WHERE PolicyNumber = 'AR-0000301132-04'
ORDER BY decpageid DESCContext
StackExchange Database Administrators Q#14989, answer score: 8
Revisions (0)
No revisions yet.