July 11, 2013 at 2:30 pm
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
July 11, 2013 at 2:50 pm
amit.overnet (7/11/2013)
himytable 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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 11, 2013 at 10:41 pm
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
July 12, 2013 at 7:46 am
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply