get max values - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

get max values

Author

Topic

kt
Yak Posting Veteran

88 Posts

Posted - 2013-03-01 : 13:00:32

I want to return the the max value, but my query below return duplicate records. How can i make this query to run only max
records like in the reuslts B

SELECT
orderNo,
MAX(DeliveryDate) as DeliveryDate,
MAX(DateChange) as DateChange,
DateSchedule
FROM sales
GROUP BY orderNo,DateSchedule
order by orderNo

Results A:
OrderNo DeliveryDate DateChange DateSchedule
222 2012-12-14 2012-12-04 2005-12-16
555 2006-07-23 2011-10-19 2001-01-01
444 2007-01-26 2007-01-23 2006-09-08
444 2007-01-26 2007-01-23 2007-01-15
444 2007-01-26 2007-01-24 2001-01-23
666 2006-11-30 2006-10-23 2006-09-07
666 2006-10-05 2006-01-23 2006-10-26

Results B:
OrderNo DeliveryDate DateChange DateSchedule
222 2012-12-14 2012-12-04 2005-12-16
555 2006-07-23 2011-10-19 2001-01-01
444 2007-01-26 2007-01-24 2001-01-23
666 2006-11-30 2006-10-23 2006-09-07

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-01 : 13:12:53

[code]
SELECT OrderNo, DeliveryDate, DateChange, DateSchedule
FROM
(
SELECT *,ROW_NUMBER() OVER (PARTITION BY OrderNo ORDER BY DeliveryDate DESC,DateChange DESC) AS Seq
FROM sales
)t
WHERE Seq=1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kt
Yak Posting Veteran

88 Posts

Posted - 2013-03-01 : 13:33:59

did not work, not give me the max date for DateChange

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-01 : 13:39:27

Logically the query is correct. So it may be that the table is large and so it takes a long time. How many rows do you have in the sales table? You can query like this:

SELECT row_count 
FROM sys.dm_db_partition_stats
WHERE OBJECT_NAME(OBJECT_ID) = 'Sales'

kt
Yak Posting Veteran

88 Posts

Posted - 2013-03-01 : 14:28:42

it finally work, but not give the me max date for DateChange field.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-02 : 02:19:55

so you want to only consider max value for DateChange? then use this instead


SELECT OrderNo, DeliveryDate, DateChange, DateSchedule
FROM
(
SELECT *,ROW_NUMBER() OVER (PARTITION BY OrderNo ORDER BY DeliveryDate DESC,DateChange DESC) AS Seq
FROM sales
)t
WHERE Seq=1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources