comparing data in the same field if same

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

SQL Server 2005 Forums

Transact-SQL (2005)

comparing data in the same field if same

Author

Topic

pinoyextacy
Starting Member

15 Posts

Posted - 2008-07-10 : 15:33:53

I am new to sql programming and basically teaching myself right now. I am trying to go through a table in which I am looking at the reference number, id and process date. I am looking at the reference field and id to see if there are any duplicates and if it has a duplicate to look at the process date and select the account that has the newest process date to show in my output. I guess I would need two do while loops in which to check if there is more than two accounts that have the same data and select the newest one with the process. The other would go through my list of data in my reference field and id to check for other duplicates for the rest of the data. I think I would need some if statement but I just don't know how to set them up and compare the two or three entries. Please help

Here is what I have so far:
select client, id, process_date from Inventory order by process desc

This will only show me the fields I really need to see and by ordering them by descending it will put the newest process date above the duplicate.

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-07-10 : 15:41:14

Try looking at the min() and max() operations if you are wanting to get the earliest or latest records in your select statement. Something like this:

select  client, id, max(process_date) as max_process_date
from    Inventory
group by client, id

pinoyextacy
Starting Member

15 Posts

Posted - 2008-07-10 : 16:27:41

I am curious but will that coding deal with my issue regarding duplicate records with the same reference and id

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-10 : 16:30:30

Have a look at the ROW_NUMBER() function and it's uses.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources