select max version records - 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)

select max version records

Author

Topic

de4ever@gmail.com
Starting Member

36 Posts

Posted - 2009-04-15 : 01:24:31

Hi all,

Ihave a Table like this
ver----docid-----------owner------name
1.00 HR-0001 1 New Bitmap Image.bmp
1.10 HR-0001 1 New Bitmap Image.bmp
1.20 HR-0001 1 New Bitmap_Image.bmp
1.10 GEN-0001 1 abc.gif
1.20 GEN-0001 1 abc.gif
1.30 GEN-0001 1 abc.gif

I need the records containing max version no

ver----docid-----------owner------name
1.20 HR-0001 1 New Bitmap_Image.bmp
1.30 GEN-0001 1 abc.gif

from the table
please help me out

Thanks

mithun.gite
Starting Member

3 Posts

Posted - 2009-04-15 : 01:57:52

SELECT * FROM My Table WHERE ver IN(SELECT MAX(ver) FROM My Table)

Thanks & Regards,
Mithun Gite

Kokkula
Starting Member

41 Posts

Posted - 2009-04-15 : 02:13:36

Hello,

Try this

SELECT A.*
FROM TableName AS A
WHERE A.Ver = (SELECT MAX(B.Ver) Ver FROM TableName AS B WHERE A.DOCID = B.DOCID)

OR

SELECT A.*
FROM
(
SELECT ROW_NUMBER() OVER(Order BY Ver Desc, Partition By DocID) AS ROW, A.*
FROM TableName
) AS A
WHERE A.Row = 1

Hope helpful...

Thanks,
Pavan

thiyagu_rind
Starting Member

46 Posts

Posted - 2009-04-15 : 02:18:48

Hey,

You can use

Select Max(Ver),docid,owner,name
From TableName
Group by docid,owner,name

Regards
Thiyagarajan

de4ever@gmail.com
Starting Member

36 Posts

Posted - 2009-04-15 : 03:34:30

SELECT A.*
FROM
(
SELECT ROW_NUMBER() OVER(Order BY Ver Desc, Partition By DocID) AS ROW, A.*
FROM TableName
) AS A
WHERE A.Row = 1

throwing syntax error near Partition By

Kokkula
Starting Member

41 Posts

Posted - 2009-04-15 : 04:33:10

Hello,

Sorry it should be like

SELECT A.*
FROM
(
SELECT ROW_NUMBER() OVER( Partition By DocID Order BY Ver Desc) AS ROW, A.*
FROM TableName
) AS A
WHERE A.Row = 1

Hope helpful...

Thanks,
Pavan

de4ever@gmail.com
Starting Member

36 Posts

Posted - 2009-04-15 : 04:56:33

quote:
Originally posted by Kokkula

Hello,

Sorry it should be like

SELECT A.*
FROM
(
SELECT ROW_NUMBER() OVER( Partition By DocID Order BY Ver Desc) AS ROW, A.*
FROM TableName
) AS A
WHERE A.Row = 1

Hope helpful...

Thanks,
Pavan

Thanks Pavan ,it solved my problem with little change to the query

SELECT A.*
FROM
(
SELECT TableName.*,ROW_NUMBER() OVER( Partition By DocID Order BY Ver Desc) AS ROW
FROM TableName
) AS A
WHERE A.Row = 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources