MAX of a record - 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

General SQL Server Forums

New to SQL Server Programming

MAX of a record

Author

Topic

Deon Smit
Starting Member

47 Posts

Posted - 2008-11-18 : 09:59:13

Hi.

I am trying to call the latest complete record out o a table. Example.

Tables is a follow.

Item | Date | value |
--------------------------------
1 | 2006/01/01 | 55
1 | 2007/09/09 | 15
1 | 2007/02/10 | 77
2 | 2006/05/01 | 67
2 | 2007/07/23 | 5
2 | 2007/09/10 | 34

I want this to be retunred to me.

Item | Date | value |
--------------------------------
1 | 2007/09/09 | 15
2 | 2007/09/10 | 34

This is my Script.

Select Item, Max(DATE) from Table
group by Table

And I get.
Item | Date |
----------------------
1 | 2007/09/09 |
2 | 2007/09/10 |

I can't seem to ad the value?

Regards

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-11-18 : 10:01:09

join the above query again to your base table to get value

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-11-18 : 10:06:09

[code][/code]

select
a.Item,
a.MaxD,
b.value
from
(Select Item, Max(DATE) as MaxD from [Table] group by [Table])a
join [Table] b
on a.Item=b.Item and a.MaxD=b.[DATE]

[code][/code]

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-11-18 : 10:08:23

And I think you need to group it by group by Item instead of [Table].

select
a.Item,
a.MaxD,
b.value
from
(Select Item, Max(DATE) as MaxD from [Table] group by Item)a
join [Table] b
on a.Item=b.Item and a.MaxD=b.[DATE]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-18 : 10:42:43

if sql 2005

SELECT Item, Date, value
FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY Item ORDER BY Date DESC) AS Seq,Item, Date, value
FROM Table
)t
WHERE Seq=1

Deon Smit
Starting Member

47 Posts

Posted - 2008-11-19 : 00:44:40

Thank You Sakets 2000. Works like a charm. I glad I learned something.

select a.T$ITEM,a.MaxD,b.T$PRIC from
(Select T$ITEM, MAX(T$TDAT) as MaxD from baandb.ttdsls032700 group by T$ITEM)a
join baandb.ttdsls032700 b
on a.T$ITEM=b.T$ITEM and a.MaxD=b.T$TDAT

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2008-11-19 : 03:32:44

SELECT
t.item,t.date1,t1.value
FROM
(SELECT item, MAX(date) AS 'date1' FROM tbl1 GROUP BY item) t
OUTER APPLY (select [value] FROM tbl1 WHERE date = date1) t1

Jai Krishna

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-19 : 10:24:17

quote:
Originally posted by Jai Krishna

SELECT
t.item,t.date1,t1.value
FROM
(SELECT item, MAX(date) AS 'date1' FROM tbl1 GROUP BY item) t
OUTER APPLY (select [value] FROM tbl1 WHERE date = date1) t1

Jai Krishna

wont this return value of all details from table on a date as you're not checking for item inside apply subquery?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-19 : 10:38:03

quote:
Originally posted by Deon Smit

Thank You Sakets 2000. Works like a charm. I glad I learned something.

If you have several records with same date for same customer, this will return all records for last day, not just one.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources