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

SQL Server Administration (2005)

SQL Query

Author

Topic

Pradip
Starting Member

32 Posts

Posted - 2009-11-27 : 04:55:06

Could anyone form a query on following question.

I have table
create table temp
(
ID int,
nm char(10),
amt decimal(18,2),
dt datetime
)

and data as:

1 abc 32.00 2009-01-01 00:00:00.000
1 abc 31.00 2004-01-01 00:00:00.000
2 pbc 44.00 2001-01-01 00:00:00.000
2 pbc 44.00 2005-01-01 00:00:00.000
1 abc 44.00 2005-01-01 00:00:00.000

I want last date record of each name.

expected Output

1 abc 32.00 2009-01-01 00:00:00.000
2 pbc 44.00 2005-01-01 00:00:00.000

pradipjain

Pradip
Starting Member

32 Posts

Posted - 2009-11-27 : 04:57:20

sorry for choosing wrong forum it should be in T-SQL.

pradipjain

Pradip
Starting Member

32 Posts

Posted - 2009-11-27 : 05:35:43

I write the sql in this way. Is there more simple way?

DECLARE @temp TABLE
(
id INT,
dt DATETIME
);

INSERT INTO @temp
SELECT
ID,
Max(dt)
FROM temp
Group by
ID

SELECT
A.ID,
A.Nm,
A.amt,
A.dt
FROM temp A
INNER JOIN @temp t ON A.ID =t.ID AND A.dt=t.dt

pradipjain

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-27 : 06:06:00

SELECT
ID,
Nm,
max(amt) as amt,
max(dt) as dt
FROM temp
group by
ID,
Nm

Madhivanan

Failing to plan is Planning to fail

Pradip
Starting Member

32 Posts

Posted - 2009-11-27 : 07:41:32

Madhivanan,
max(amt) as amt. is not in the expected only last dated row is expected for distinct name.

Thanks for your quick reply.

pradipjain

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-27 : 07:48:43

Have you tried the query I posted?

Madhivanan

Failing to plan is Planning to fail