CODEWITHSUNDEEP
sql-servermax
Ken Wagner
Ken Wagner

Reputation: 1

How to display only the MAX results from a query

I am new to writing MS SQL queries and I am trying to display only the record with the highest field named RecordVersion. Below is the query that works but displays all records:

SELECT        
    PriceCalendars.PriceProgramID, 
    PriceCalendars.EffectiveDateTime, 
    PriceSchedules.Price, 
    PriceSchedules.PLU, 
    items.Descr, 
    PriceSchedules.LastUpdate, 
    PriceSchedules.LastUpdatedBy, 
    PriceSchedules.RecordVersion, 
    PriceSchedules.PriceScheduleUniqueID
FROM            
    PriceCalendars 
    INNER JOIN PriceSchedules ON PriceCalendars.PriceProgramID = PriceSchedules.PriceProgramID 
    INNER JOIN items ON PriceSchedules.PLU = items.PLU
WHERE        
    (PriceSchedules.PLU = 'SLS10100103') 
    AND (PriceCalendars.EffectiveDateTime = '2016-03-22')

Here are the query results:

PriceProgramID  EffectiveDateTime   Price   PLU Descr   LastUpdate  LastUpdatedBy   RecordVersion   PriceScheduleUniqueID

1   2016-03-22 00:00:00.000 35.00   SLS10100103             Architecture Adult from NP POS  2015-01-22 07:53:15.000 GX70,83     9   569

1   2016-03-22 00:00:00.000 32.00   SLS10100103             Architecture Adult from NP POS  2014-02-25 16:22:46.000 GX70,83     5   86180

The first line of the results has RecordVersion being 9 and the second line results is 5, I only want the higher record displaying, the one that returned RecordVersion = 9.

Every time I try to use the MAX command I get errors or the group by and I have tried every example I could find on the web but nothing seems to work.

Using MS SQL 2012. Thanks, Ken

Upvotes: 0

Views: 374

Answers (2)

TheGameiswar
TheGameiswar

Reputation: 28900

All group by columns should be in select ,that's the rule of group by.How group by works is for every distinct combination of group by columns,arrange remaining columns into groups,so that any aggregation can be applied,in your case I am not sure what group by columns are unique with out test date.here is one version which use row number which gives you the output desired

Remember ,order by last updated date is the one which decides rows order and assign numbers

WITH CTE
AS
(    
   SELECT  PriceCalendars.PriceProgramID, 
    PriceCalendars.EffectiveDateTime, 
    PriceSchedules.Price, 
    PriceSchedules.PLU, 
    items.Descr, 
    PriceSchedules.LastUpdate, 
    PriceSchedules.LastUpdatedBy, 
    PriceSchedules.RecordVersion, 
    PriceSchedules.PriceScheduleUniqueID,
    ROW_NUMBER() OVER (PARTITION BY PriceSchedules.RecordVersion ORDER BY PriceSchedules.LastUpdatedBy) AS RN
FROM            
    PriceCalendars 
    INNER JOIN PriceSchedules ON PriceCalendars.PriceProgramID = PriceSchedules.PriceProgramID 
    INNER JOIN items ON PriceSchedules.PLU = items.PLU
WHERE        
    (PriceSchedules.PLU = 'SLS10100103') 
    AND (PriceCalendars.EffectiveDateTime = '2016-03-22')
    )
    SELECT * FROM CTE WHERE RN=1

Upvotes: 0

David Tansey
David Tansey

Reputation: 6023

Try the following query which attempts to solve your problem by ordering the returned rows by RecordVersion DESC and then SELECTs just the first row. 

SELECT TOP 1      
    PriceCalendars.PriceProgramID, 
    PriceCalendars.EffectiveDateTime, 
    PriceSchedules.Price, 
    PriceSchedules.PLU, 
    items.Descr, 
    PriceSchedules.LastUpdate, 
    PriceSchedules.LastUpdatedBy, 
    PriceSchedules.RecordVersion, 
    PriceSchedules.PriceScheduleUniqueID
FROM            
    PriceCalendars 
    INNER JOIN PriceSchedules ON PriceCalendars.PriceProgramID = PriceSchedules.PriceProgramID 
    INNER JOIN items ON PriceSchedules.PLU = items.PLU
WHERE        
    (PriceSchedules.PLU = 'SLS10100103') 
    AND (PriceCalendars.EffectiveDateTime = '2016-03-22')
ORDER BY
    RecordVersion DESC

Upvotes: 2

Related Questions