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.
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 maxrecords like in the reuslts BSELECT orderNo, MAX(DeliveryDate) as DeliveryDate,MAX(DateChange) as DateChange,DateScheduleFROM sales GROUP BY orderNo,DateScheduleorder by orderNoResults A: OrderNo DeliveryDate DateChange DateSchedule222 2012-12-14 2012-12-04 2005-12-16555 2006-07-23 2011-10-19 2001-01-01444 2007-01-26 2007-01-23 2006-09-08444 2007-01-26 2007-01-23 2007-01-15444 2007-01-26 2007-01-24 2001-01-23666 2006-11-30 2006-10-23 2006-09-07666 2006-10-05 2006-01-23 2006-10-26Results B: OrderNo DeliveryDate DateChange DateSchedule222 2012-12-14 2012-12-04 2005-12-16555 2006-07-23 2011-10-19 2001-01-01444 2007-01-26 2007-01-24 2001-01-23666 2006-11-30 2006-10-23 2006-09-07thanks |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-01 : 13:12:53
|
[code]SELECT OrderNo, DeliveryDate, DateChange, DateScheduleFROM(SELECT *,ROW_NUMBER() OVER (PARTITION BY OrderNo ORDER BY DeliveryDate DESC,DateChange DESC) AS SeqFROM sales)tWHERE Seq=1[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://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_statsWHERE 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 insteadSELECT OrderNo, DeliveryDate, DateChange, DateScheduleFROM(SELECT *,ROW_NUMBER() OVER (PARTITION BY OrderNo ORDER BY DeliveryDate DESC,DateChange DESC) AS SeqFROM sales)tWHERE Seq=1 ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|