Getting Latest Value - 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 2000 Forums

Transact-SQL (2000)

Getting Latest Value

Author

Topic

rauof_thameem
Starting Member

31 Posts

Posted - 2007-06-21 : 10:18:55

hi,

I have a table like the following

Declare Table sample
(
ID int identity,
col1 varchar(50),
col2 ntext,
entered_date datetime
)

data in the table like

1,Sample,Null,6/21/2007
2,test,null,6/22/2007

i want the last entered date ID=2 and entered_date= 6/22/2007,

how can i take the last entered date,when i trying to use the MAX(entered_date) in group by column it dose in allow the ntext datatype, is there any way so that i can get the last entered date.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-21 : 10:27:15

[code]SELECT *
FROM sample s
WHERE s.entered_date = (SELECT MAX(entered_date) FROM sample)[/code]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-21 : 10:40:31

If datecol is unique, then

Select top 1 columns from table
Order by datecol desc

Madhivanan

Failing to plan is Planning to fail

rauof_thameem
Starting Member

31 Posts

Posted - 2007-06-21 : 10:45:39

Actually the data is something like this

Declare Table sample
(
ID int,
col1 varchar(50),
col2 ntext,
entered_date datetime
)

1,Sample,Null,6/21/2007
1,Sample,Null,6/22/2007
1,Sample,Null,6/23/2007
2,test,null,6/22/2007
2,test,null,6/24/2007
2,test,null,6/25/2007

i want only the last entered value like this.

1,Sample,Null,6/23/2007
2,test,null,6/25/2007

so what should i do..,

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-21 : 10:50:50

[code]SELECT s.*
FROM [sample] s INNER JOIN
(
SELECT col1, entered_date = MAX(entered_date)
FROM [sample]
GROUP BY col1
) m
ON s.col1 = m.col1
AND s.entered_date = m.entered_date[/code]

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-21 : 10:54:41

Select columns from table where datecol in
(select max(datecol) from table group by id)

Madhivanan

Failing to plan is Planning to fail

rauof_thameem
Starting Member

31 Posts

Posted - 2007-06-22 : 05:26:37

Hi,

i want only the last entered record all other should be deleted

Declare Table sample
(
ID int,
col1 varchar(50),
col2 ntext,
entered_date datetime
)

1,Sample,Null,6/21/2007
1,Sample,Null,6/22/2007
1,Sample,Null,6/23/2007
2,test,null,6/22/2007
2,test,null,6/24/2007
2,test,null,6/25/2007

i want only the last entered value like this.

1,Sample,Null,6/23/2007
2,test,null,6/25/2007

and all other record should be deleted
so what should i do..,

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-22 : 05:34:33

[code]DECLARE @sample TABLE
(
ID int,
col1 varchar(50),
col2 ntext,
entered_date datetime
)

INSERT INTO @sample
SELECT 1, 'Sample', NULL, '6/21/2007' UNION ALL
SELECT 1, 'Sample', NULL, '6/22/2007' UNION ALL
SELECT 1, 'Sample', NULL, '6/23/2007' UNION ALL
SELECT 2, 'test', NULL, '6/22/2007' UNION ALL
SELECT 2, 'test', NULL, '6/24/2007' UNION ALL
SELECT 2, 'test', NULL, '6/25/2007'

DELETE s
FROM @sample s LEFT JOIN
(
SELECT col1, entered_date = MAX(entered_date)
FROM @sample
GROUP BY col1
) m
ON s.col1 = m.col1
AND s.entered_date = m.entered_date
WHERE m.col1 IS NULL

SELECT * FROM @sample
/*
ID col1 col2 entered_date
----------- -------- ---- ------------
1 Sample NULL 2007-06-23
2 test NULL 2007-06-25
*/[/code]

KH
[spoiler]Time is always against us[/spoiler]

rauof_thameem
Starting Member

31 Posts

Posted - 2007-06-22 : 05:51:10

In the delete statement why u have given the

where col1 is null

DELETE s
FROM @sample s LEFT JOIN
(
SELECT col1, entered_date = MAX(entered_date)
FROM @sample
GROUP BY col1
) m
ON s.col1 = m.col1
AND s.entered_date = m.entered_date
WHERE m.col1 IS NULL

rauof_thameem
Starting Member

31 Posts

Posted - 2007-06-22 : 05:59:18

Sorry sorry... i made mistake, i havent seen the code clearly.., thanks its working

In the delete statement why u have given the

where col1 is null

DELETE s
FROM @sample s LEFT JOIN
(
SELECT col1, entered_date = MAX(entered_date)
FROM @sample
GROUP BY col1
) m
ON s.col1 = m.col1
AND s.entered_date = m.entered_date
WHERE m.col1 IS NULL

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-22 : 06:08:14

this will give you the rows that you want to keep.

SELECT col1, entered_date = MAX(entered_date)
FROM @sample
GROUP BY col1

to remove the rest of records, you left join to the above and check for NULL

KH
[spoiler]Time is always against us[/spoiler]

rauof_thameem
Starting Member

31 Posts

Posted - 2007-06-22 : 06:53:34

if we have same record then the values are repeating

DECLARE @sample TABLE
(
ID int,
col1 varchar(50),
col2 ntext,
entered_date datetime
)

INSERT INTO @sample
SELECT 1, 'Sample', NULL, '6/21/2007' UNION ALL
SELECT 1, 'Sample', NULL, '6/22/2007' UNION ALL
SELECT 1, 'Sample', NULL, '6/23/2007' UNION ALL
SELECT 2, 'test', NULL, '6/22/2007' UNION ALL
SELECT 2, 'test', NULL, '6/22/2007' UNION ALL
SELECT 2, 'test', NULL, '6/24/2007' UNION ALL
SELECT 2, 'test', NULL, '6/25/2007'

DELETE s
FROM @sample s LEFT JOIN
(
SELECT col1, entered_date = MAX(entered_date)
FROM @sample
GROUP BY col1
) m
ON s.col1 = m.col1
AND s.entered_date = m.entered_date
WHERE m.col1 IS NULL

SELECT * FROM @sample
/*
ID col1 col2 entered_date
----------- -------- ---- ------------
1 Sample NULL 2007-06-23
2 test NULL 2007-06-25
*/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-22 : 07:24:49

if you have 2 rows of

SELECT 2, 'test', NULL, '6/25/2007'

these 2 rows will be retains

KH
[spoiler]Time is always against us[/spoiler]

rauof_thameem
Starting Member

31 Posts

Posted - 2007-06-22 : 07:29:09

No way to remove those redundant records kya.. ??

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-22 : 07:35:50

do you have a Primary Key in the table ?

KH
[spoiler]Time is always against us[/spoiler]

rauof_thameem
Starting Member

31 Posts

Posted - 2007-06-22 : 07:48:54

i dont have the primary key in the table.., actually those table are temporary table.., when ever a record is inserted or updated in the main main the row will be inserted in the temporary table these temporary table have same structure as main table only thing is primary key is removed..

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-22 : 07:55:08

quote:
actually those table are temporary table.

Since it is a temp table, surely you can add a indentity column right ?

KH
[spoiler]Time is always against us[/spoiler]

rauof_thameem
Starting Member

31 Posts

Posted - 2007-06-22 : 08:00:04

mmmmm Ya.... i can add..., so with primary key how i can remove the redundant data??

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-22 : 08:23:55

[code]DECLARE @sample TABLE
(
pk int identity(1,1),
ID int,
col1 varchar(50),
col2 ntext,
entered_date datetime
)

INSERT INTO @sample ([ID], col1, col2, entered_date)
SELECT 1, 'Sample', NULL, '6/21/2007' UNION ALL
SELECT 1, 'Sample', NULL, '6/22/2007' UNION ALL
SELECT 1, 'Sample', NULL, '6/23/2007' UNION ALL
SELECT 2, 'test', NULL, '6/22/2007' UNION ALL
SELECT 2, 'test', NULL, '6/24/2007' UNION ALL
SELECT 2, 'test', NULL, '6/25/2007' UNION ALL
SELECT 2, 'test', NULL, '6/25/2007'

DELETE s
FROM @sample s LEFT JOIN
(
SELECT col1, pk = MIN(pk)
FROM @sample x
WHERE x.entered_date = (SELECT MAX(entered_date) FROM @sample y WHERE y.col1 = x.col1)
GROUP BY col1
) m
ON s.pk = m.pk
WHERE m.col1 IS NULL

SELECT * FROM @sample[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources