Constructing SQL 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

SQL Server 2005 Forums

Transact-SQL (2005)

Constructing SQL Query

Author

Topic

MrSingh
Starting Member

5 Posts

Posted - 2011-03-07 : 06:49:56

Hi I am fairly new to SQL and need a hand constructing a query.

I am trying to complie a query with the following logic: To select all columns in a database AND if a duplicate appears based on an ID to only return the one which has the latest date.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2011-03-07 : 07:06:03

Latest Date can be
1 : Modify Date
2 : Create Date

Although ObjectID of objects in sql server are created sequentially.
So greatest objectID will be latest so you can try following -


SELECT Name FROM 
(
SELECT  ROW_NUMBER() OVER ( Partition BY C.Name ORDER BY C.object_id DESC) RowNo, C.name Name
FROM    SYS.TABLES T
        INNER JOIN SYS.COLUMNS C ON T.Object_ID = C.Object_ID 
) A 
WHERE RowNo = 1

Vaibhav T

If I cant go back, I want to go fast...

MrSingh
Starting Member

5 Posts

Posted - 2011-03-07 : 07:14:55

Columns in the table that contain the duplicate code is called 'ToolCode' for each 'ToolCode' there is an associated coulmn 'LatestUploadDate'.

I need to return the entrire contents of the table AND IF a duplicate 'ToolCode' exists to only display the record that has the most recent date (LatestUploadDate)

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2011-03-07 : 07:19:38

SELECT * FROM
(
SELECT *, ROW_NUMBER() OVER( Partition BY ToolCode, ORDER BY LatestUploadDate DESC) Seq FROM YourTable
) A
WHERE Seq = 1

Vaibhav T

If I cant go back, I want to go fast...

MrSingh
Starting Member

5 Posts

Posted - 2011-03-07 : 08:53:57

Fixed!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources