need help in writing a query - 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

need help in writing a query

Author

Topic

anjali5
Posting Yak Master

121 Posts

Posted - 2011-11-11 : 22:16:37

I have this table

number UID updates Message_date InitialDate category

2004061 21449 0 2004-10-06 1900-01-01 1
2004061 21449 1 2004-10-06 1900-01-01 6
2004061 21449 2 2004-10-06 1900-01-01 7
2009345 21344 1 2010-11-09 2011-01-01 1
2009345 21344 1 2010-11-09 2011-01-01 2
2009345 21344 1 2010-11-09 2011-01-01 3
2008988 21333 1 2010-11-09 2011-01-01 3
2008988 21333 1 2010-11-09 2011-01-01 3
2008988 21333 1 2011-11-09 2011-01-01 3

for the above table, if the number and UID are same then if the updates are same and message dates(per row) are same then I need to merge that record
so from the above table

I need to merge the record that has number 2009345 and UID 21344 to only one record

so i will get. the number of records will be reduced from 3 to 1 for number 2009345 and UID 21344, other records will be deleted

2009345 21344 1 2010-11-09 2011-01-01 3

if the updates and mdate are different then I have to go with the higheset category

so from the above table, i will get only one record other records will be deleted

2004061 21449 1 2004-10-06 1900-01-01 6

If the category is same then I will go with the higheset message date and drop other records. This is for number 2008988 and UID 21333

2008988 21333 1 2011-11-09 2011-01-01 3

other records will be deleted.

How can I achieve this

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-12 : 05:15:04

sorry you explanation and output doesnot always make sense
you've said as below
if the updates and mdate are different then I have to go with the higheset category
and then output shows record with category 6 which is not highest(7 is highest category) can you explain this difference?

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources