select two records with same id,different logflag

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)

select two records with same id,different logflag

Author

Topic

magikminox
Starting Member

27 Posts

Posted - 2008-06-11 : 06:37:16

Hi guys

I have a table that stores records and each receord has a default logflag value of 0.Each time any record is changed,a new record is inserted with similar vaues but logflag flag remains 0.The original record that was modified will is kept but will have its log flag set to 1.

Now i want to display the two records in a report one on top of the other so that users can clearly see what chages were made for audit purposes.

How do i do my select statement so that for each record that was modified,the original one(logflag 1) is displayed and the new modified one(logflag 0 ) is also displayed just below it.

ie

id name email address city logflag modifieddate

23 rob rob@me.com oxford lane wolverton 1 20/6/2008

23 rob robe@me.com huge road burnside 0 null

The same should be done for all the modified records.

When a record is has not been modified,it should not be displayed by the select statement.

Thanks in advance

In god we trust,everything else we test.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-11 : 07:00:01

SELECT * FROM Table ORDER BY id ASC,CAST(logflag as int) DESC