none
SQL Server select MAX value per month

    Question

  • Good day SQL gurus,

    Please will provide SQL query for MAX value/price per month

    Ex: 

    Price   Date                                        Customer

    10      10-May-2018                              A

    20       10-May-2018                             B

    25       11-May-2018                             A

    50       30-May-2018                             C

    70       31-May-2018                             B

    100       31-May-2018                              A

    90     31-May-2018                            B

    in this case  the result set should be               100      31-May-2018     A

    Thank you



    BABA

    Friday, June 15, 2018 7:33 AM

All replies

  • like this

    SELECT *
    FROM Table t
    WHERE NOT EXISTS
    (
    SELECT 1
    FROM table
    WHERE DATEDIFF(mm,0,[Date]) = DATEDIFF(mm,0,t.[Date])
    AND Price > t.Price
    ) 


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Proposed as answer by Sarat (SS) Monday, June 18, 2018 2:19 PM
    Friday, June 15, 2018 7:35 AM
  • create table #t (price int , dt date)

    Insert into #t values (10,'20180101')
    Insert into #t values (20,'20180102')
    Insert into #t values (30,'20180102')
    Insert into #t values (70,'20180102')
    Insert into #t values (100,'20180102')
    Insert into #t values (90,'20180102')
    Insert into #t values (50,'20180202')
    Insert into #t values (90,'20180203')



    select max(price),  CONVERT(CHAR(7), dt, 120) from #t 
    GROUP BY CONVERT(CHAR(7), dt, 120);

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Proposed as answer by Sarat (SS) Monday, June 18, 2018 2:18 PM
    Sunday, June 17, 2018 5:12 AM
    Answerer
  • set statistics xml on;create table #t (price int , dt date)
    
    Insert into #t values (10,'20180101')
    Insert into #t values (20,'20180102')
    Insert into #t values (30,'20180102')
    Insert into #t values (70,'20180102')
    Insert into #t values (100,'20180102')
    Insert into #t values (90,'20180102')
    Insert into #t values (50,'20180202')
    Insert into #t values (90,'20180203')
    
    
    select price,dt from (select price,rank()over(order by price desc) as pri,dt
    from #t)pri where pri=1


    Saravanan ----------------------------------------------------------------------- Ever tried. Ever failed. No matter. Try Again. Fail again. Fail better.

    Sunday, June 17, 2018 12:13 PM
  • Hi BABA,

    Please check following query, hope it helps:

    create table #t (price int , dt date , Customer varchar(10))
    
    Insert into #t values (10,'20180510','A')
    Insert into #t values (20,'20180510','B')
    Insert into #t values (25,'20180511','A')
    Insert into #t values (50,'20180530','C')
    Insert into #t values (70,'20180531','B')
    Insert into #t values (100,'20180531','A')
    Insert into #t values (90,'20180531','B')
    
    select distinct t.*
    from #t t
    join (
    	select CONVERT(CHAR(7), dt, 120) as mth,MAX(price)Over(Partition By CONVERT(CHAR(7), dt, 120) Order By CONVERT(CHAR(7), dt, 120)) as MaxPrice
    	from #t ) t2 
    on CONVERT(CHAR(7), t.dt, 120)= t2.mth and t.price=t2.MaxPrice

    Thanks,
    Xi Jin.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, June 18, 2018 2:45 AM
    Moderator
  • Hi,

    You can try like this as well

    select * FROM (
    select price,dt,customer, Month(dt) As Mon, Year(dt) AS Yr,ROW_NUMBER() OVER(PARTITION BY Month(dt), Year(dt) ORDER BY Price DESC) As RN from #t
    )A
    where A.RN =1


    HTH,

    Ram


    Please vote as helpful or mark as answer, if it helps


    Monday, June 18, 2018 2:09 PM
  • Your query will only work if the range of dates in the table does not exceed 1 year - the danger of partitioning by month only. 
    Monday, June 18, 2018 2:45 PM
  • Nice catch scott. I have included year partition as well.

    Regards,

    Ram


    Please vote as helpful or mark as answer, if it helps

    Monday, June 18, 2018 2:56 PM