Find highest rate from query with UNION clause.

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

Find highest rate from query with UNION clause.

Author

Topic

hspatil31
Posting Yak Master

182 Posts

Posted - 2011-06-22 : 03:05:57

Hello All,

I am using following query and and i am getting output as follows, and i am giving deserve output also. Can any one help me how to find the highest rate from this query(Deserve Output).

Query:

SELECT RSMRate=( '1' + CAST(nrs.NPA AS VARCHAR) + CAST(nrs.NXX AS VARCHAR) + ','
+ CAST(round(max(nrs.Rate),4) AS VARCHAR))
FROM
norm nrs,
work ws,
createdet rfcd
WHERE
nrs.C_Ty_ID='INTRASTATE'
AND nrs.W_Sht_ID=ws.id
AND rfcd.id='5F4DF59A-9B90-4FF8-B095-109B64582DCA'
AND ws.rt_sht_id=rfcd.rt_sht_id
GROUP BY
nrs.npa,nrs.nxx,nrs.thousands_group
UNION
SELECT RSMRate=( '1' + CAST(nrs.NPA AS VARCHAR) + CAST(nrs.NXX AS VARCHAR) + ','
+ CAST(round(max(nrs.Rate),4) AS VARCHAR))
FROM
norm nrs,
work ws,
createdet rfcd
WHERE
nrs.C_Ty_ID='INTRASTATE'
AND nrs.W_Sht_ID=ws.id
AND rfcd.id='5F4DF59A-9B90-4FF8-B095-109B64582DCA'
AND ws.rt_sht_id=rfcd.rt_sht_id
AND nrs.thousands_group IS NULL
AND NOT EXISTS
( SELECT 'x'
FROM
norm nrs2
WHERE
nrs2.W_Sht_ID=nrs.W_Sht_ID
and nrs2.npa=nrs.npa
and nrs2.nxx=nrs.nxx
and nrs2.C_Ty_ID=nrs.C_Ty_ID
)
GROUP BY nrs.npa,nrs.nxx
ORDER BY RSMRate

Output:

1201200,0.0039
1201200,0.0049
1201202,0.0032
1201203,0.0036

Deserve Output:

1201200,0.0049
1201202,0.0032
1201203,0.0036

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-06-22 : 10:30:24

I have to ask why you are converting perfectly good columns into a concatenated mess?

But, that asside, maybe something like this wil work for you:

SELECT
    RatePart1 + ',' + MAX(RatePart2) AS RSMRate 
FROM
(    
    SELECT 
        '1' + CAST(nrs.NPA AS VARCHAR) + CAST(nrs.NXX AS VARCHAR) AS RatePart1,
        CAST(round(max(nrs.Rate),4) AS VARCHAR) AS RatePart2
    FROM 
    norm nrs, 
    work ws,
    createdet rfcd
    WHERE
    nrs.C_Ty_ID='INTRASTATE' 
    AND nrs.W_Sht_ID=ws.id
    AND rfcd.id='5F4DF59A-9B90-4FF8-B095-109B64582DCA'
    AND ws.rt_sht_id=rfcd.rt_sht_id 
    GROUP BY
    nrs.npa,nrs.nxx,nrs.thousands_group

    UNION

    SELECT 
        '1' + CAST(nrs.NPA AS VARCHAR) + CAST(nrs.NXX AS VARCHAR) AS RatePart1,
        CAST(round(max(nrs.Rate),4) AS VARCHAR) AS RatePart2
    FROM 
    norm nrs, 
    work ws,
    createdet rfcd
    WHERE
    nrs.C_Ty_ID='INTRASTATE' 
    AND nrs.W_Sht_ID=ws.id
    AND rfcd.id='5F4DF59A-9B90-4FF8-B095-109B64582DCA'
    AND ws.rt_sht_id=rfcd.rt_sht_id
    AND nrs.thousands_group IS NULL
    AND NOT EXISTS
    ( SELECT 'x'
    FROM 
    norm nrs2
    WHERE
    nrs2.W_Sht_ID=nrs.W_Sht_ID
    and nrs2.npa=nrs.npa
    and nrs2.nxx=nrs.nxx
    and nrs2.C_Ty_ID=nrs.C_Ty_ID 
    )
    GROUP BY nrs.npa,nrs.nxx
    --ORDER BY RSMRate 
) AS T
GROUP BY
    RatePart1
ORDER BY    
    RatePart1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources