SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


get the minimum value


get the minimum value

Author
Message
amit.overnet
amit.overnet
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 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
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26131 Visits: 17539
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 Modens 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)
Steven Willis
Steven Willis
SSC Eights!
SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)

Group: General Forum Members
Points: 831 Visits: 1721
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




 
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26131 Visits: 17539
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 Modens 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)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search