Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

get the minimum value Expand / Collapse
Author
Message
Posted Thursday, July 11, 2013 2:30 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 08, 2013 5:17 AM
Points: 3, Visits: 6
hi

mytable like:

matchpattern country rate
9526 USA 0.80
9111 India 0.24
9501 Brazil 2.01

i want get minimum rate behalf of mattchpattern.

i have used : select rate,min() from mytable group by m_pattern;

i want to get result like this:
matchpattern country rate
9111 India 0.24
9526 USA 0.80
9501 Brazil 2.01


but i am not getting result. above query is not suitable. please suggest me suitable query regarding my requirement please help me
Post #1472776
Posted Thursday, July 11, 2013 2:50 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:24 PM
Points: 11,990, Visits: 11,007
amit.overnet (7/11/2013)
hi

mytable like:

matchpattern country rate
9526 USA 0.80
9111 India 0.24
9501 Brazil 2.01

i want get minimum rate behalf of mattchpattern.

i have used : select rate,min() from mytable group by m_pattern;

i want to get result like this:
matchpattern country rate
9111 India 0.24
9526 USA 0.80
9501 Brazil 2.01


but i am not getting result. above query is not suitable. please suggest me suitable query regarding my requirement please help me


You have to pass a column to MIN. Otherwise how would it know which MIN you want?

select matchpattern, country, min(rate) from mytable group by matchpattern, country;



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1472788
Posted Thursday, July 11, 2013 10:41 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
For versions SQL2008 or greater you can also do this:


;WITH cteSampleData1 (matchpattern,country,rate)
AS
(
SELECT 9526,'USA',0.80 UNION ALL
SELECT 9111,'India',0.24 UNION ALL
SELECT 9111,'India',0.44 UNION ALL
SELECT 9501,'Brazil',2.21 UNION ALL
SELECT 9526,'USA',0.89 UNION ALL
SELECT 9526,'USA',0.67 UNION ALL
SELECT 9501,'Brazil',2.01
)
SELECT
r.matchpattern
,r.country
,r.MinRate
FROM
(
SELECT DISTINCT
matchpattern
,country
,MIN(rate) OVER (PARTITION BY matchpattern,country) AS MinRate
FROM
cteSampleData1
) r
ORDER BY
r.MinRate


The advantage here is you can avoid messy GROUP BY problems and easily
add multiple aggragates using different select criteria in the same select
statement such as:


;WITH cteSampleData2 (matchpattern,country,rate)
AS
(
SELECT 9526,'USA',0.80 UNION ALL
SELECT 9111,'India',0.24 UNION ALL
SELECT 9111,'India',0.44 UNION ALL
SELECT 9501,'Brazil',2.21 UNION ALL
SELECT 9526,'USA',0.89 UNION ALL
SELECT 9526,'USA',0.67 UNION ALL
SELECT 9501,'Brazil',2.01
)
SELECT
r.matchpattern
,r.country
,r.MaxRate
,r.MinRate
,r.CountryCount
FROM
(
SELECT DISTINCT
matchpattern
,country
,MAX(rate) OVER (PARTITION BY country,matchpattern) AS MaxRate
,MIN(rate) OVER (PARTITION BY matchpattern,country) AS MinRate
,COUNT(country) OVER (PARTITION BY matchpattern) AS CountryCount
FROM
cteSampleData2
) r
ORDER BY
r.MinRate


 
Post #1472853
Posted Friday, July 12, 2013 7:46 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:24 PM
Points: 11,990, Visits: 11,007
Excellent point Steven. This will work on 2005 and higher, don't need 2008 for it.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1473031
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse