select statement - 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 statement

Author

Topic

gulic
Starting Member

4 Posts

Posted - 2009-03-28 : 07:08:06

Could you help me with a select statement to obtain these results:

unique serial with the max ID

example talble:
ID Serial state
23 123 ok
25 134 no
56 123 no
46 134 ok

query results:
56 123 no
46 134 ok

thank you to much at all

cshah1
Constraint Violating Yak Guru

347 Posts

Posted - 2009-03-28 : 11:03:55

SELECT t.ID,t.serial, t.state
FROM T
inner join
(
SELECT MAX(ID) AS [ID]
from T
group by serial
) AS T1 on t.id = t1.id

gulic
Starting Member

4 Posts

Posted - 2009-03-28 : 12:01:34

doesn't work
have you any other alternatives?

gulic
Starting Member

4 Posts

Posted - 2009-03-28 : 12:21:25

Is this the right way?

SELECT dbo.TotOggiRSU.ID, dbo.TotOggiRSU.Serial, dbo.TotOggiRSU.DataOggi, dbo.TotOggiRSU.Ora, dbo.TotOggiRSU.TipoRifiuto, dbo.TotOggiRSU.Livello, dbo.TotOggiRSU.FlagError, dbo.TotOggiRSU.Batteria, dbo.TotOggiRSU.CoordX, dbo.TotOggiRSU.CoordY
FROM dbo.TotOggiRSU INNER JOIN
(SELECT MAX(ID) AS [ID]
from dbo.TotOggiRSU
group by dbo.TotOggiRSU.Serial) AS TotOggiRSU_1 ON dbo.TotOggiRSU.id = dbo.TotOggiRSU_1.id

gulic
Starting Member

4 Posts

Posted - 2009-03-28 : 12:52:50

Thank you so much
I found the solutions and now it work!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-29 : 03:25:22

if its sql 2005, this is sufficient,


SELECT ID, Serial, state
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY Serial ORDER BY ID DESC) AS Seq,
ID, Serial, state
FROM Table
)t
WHERE Seq=1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources