April 21, 2008 at 10:51 pm
Apologies if this seems a little vague, and the subject potentially on the wrong track. I'm a bit at a loss as to the best way to go about this.
I have a query against "providers". A provider may have several address records. Each address record has two flags, we'll call them flagA and flagB, and then an integer rating the validity of the address from 1 to 6 (we'll call it ratingA). I need to select one record for each provider; the record I select should be the closest to having flagA, then having flagB, then the lowest ratingA.
I realise I *could* write this query using subselects or nested derived tables. I'm wondering if there's a nicer way to do it though. The "nicer" way I'm thinking is to group the address records by provider ID, order by flagA then flagB then ratingA, and use something like TOP to select the first record per group. The statement I've tried looks a bit like:
SELECT TOP 1
providerId,
addressId
FROM addresses a
GROUP BY providerId, addressId, flagA, flagB, ratingA
ORDER BY flagA DESC, flagB DESC, ratingA ASC
Needless to say, my attempts at this have so far failed. I'm only getting the TOP record for the entire SELECT statement, not by each group.
Does what I'm asking make any sense? Am I entirely on the wrong track?
April 22, 2008 at 12:31 am
This is SQL2005 Right ?
Have a look at the rank functions that can be embedded in the sqlstatement.
Then you would only have the e.g.
Select *
from (
SELECT Rank() OVER (PARTITION BY providerId
ORDER BY flagA DESC, flagB DESC, ratingA ASC) as TheRank,
providerId,
addressId
FROM addresses ) a
Where TheRank = 1
Order BY providerId --, addressId, flagA, flagB, ratingA
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 22, 2008 at 2:08 am
Perfect, with a DISTINCT thrown in there I now have a unique set of records. Just what I'm after.
Thanks heaps.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply