searching for latest date and time only

[jas]

> This is what I want to do with the data table below.

I only want it to show one id for each issue. ( ie, 4001 only once,
4002 only once, etc. But only the one with the latest date and time)

Ie. 4001 should only be in the table once, with the latest date -
which should be :
4001 09/12/2003 17:12:09 (as I only want to show the last time
the id was updated - do you get me now )

Ie. 4002 should show only 4002 11/12/2003 15:25:13


id hs_change_date hs_change_time
4001 27/10/2003 10:38:27
4001 09/12/2003 14:43:58
4001 09/12/2003 17:12:09
4002 27/10/2003 10:56:28
4002 09/12/2003 14:44:11
4002 11/12/2003 15:25:13
4003 27/10/2003 11:13:12
4003 09/12/2003 14:44:21
4003 10/12/2003 10:48:02
4003 10/12/2003 13:25:09
4004 27/10/2003 11:28:09
4004 09/12/2003 14:44:29
4004 09/12/2003 17:18:28
4005 27/10/2003 15:55:40
4005 28/10/2003 10:18:24
4006 27/10/2003 15:59:47
4006 28/10/2003 10:18:38
4006 09/12/2003 14:44:40
4006 09/12/2003 16:47:15

[David Portas]

What data types are you using here? SQLServer DATETIME stores both data and
time in the same column so I don't understand why you appear to have
separate columns. It helps to clarify your requirements if you include DDL
with your questions.

Assuming you define your table with a single DATETIME column you can get the
result you require quite easily:

CREATE TABLE Sometable (id INTEGER, hs_change_dt DATETIME, PRIMARY KEY /*
??? */ (id, hs_change_dt))

SELECT id, MAX(hs_change_d t) AS hs_change_dt
FROM Sometable
GROUP BY id

--
David Portas
SQL Server MVP
--

[jas]

The database is already setup with two separate columns - one for
date and one for time. see below

I only want it to show one id for each issue. ( ie, 4001 only once,
4002 only once, etc. But only the one with the latest date and time)

Ie. 4001 should only be in the table once, with the latest date -
which should be :
4001 09/12/2003 17:12:09 (as I only want to show the last time
the id was updated - do you get me now )

Ie. 4002 should show only 4002 11/12/2003 15:25:13

Do you get me?
id hs_change_date hs_change_time
4001 27/10/2003 10:38:27
4001 09/12/2003 14:43:58
4001 09/12/2003 17:12:09
4002 27/10/2003 10:56:28

"David Portas" <RE************ *************** *@acm.org> wrote in message news:<7_******* *************@g iganews.com>... What data types are you using here? SQLServer DATETIME stores both data and
time in the same column so I don't understand why you appear to have
separate columns. It helps to clarify your requirements if you include DDL
with your questions.

Assuming you define your table with a single DATETIME column you can get the
result you require quite easily:

CREATE TABLE Sometable (id INTEGER, hs_change_dt DATETIME, PRIMARY KEY /*
??? */ (id, hs_change_dt))

SELECT id, MAX(hs_change_d t) AS hs_change_dt
FROM Sometable
GROUP BY id

[David Portas]

> The database is already setup with two separate columns - one for

date and one for time. see below

OK, but what are the data types? You still haven't told us and it makes a
difference to the solution. I'll assume you have DATETIME for the date and
CHAR(8) for the time.

CREATE TABLE Sometable (id INTEGER, hs_change_date DATETIME, hs_change_time
CHAR(8) NOT NULL, PRIMARY KEY (id, hs_change_date, hs_change_time) )

SELECT id,
CAST(DATEDIFF(D ,0,hs_change_dt ) AS DATETIME) AS hs_change_date,
CONVERT(CHAR(8) ,hs_change_dt,1 08) AS hs_change_time
FROM
(SELECT id,
MAX(CAST(CONVER T(CHAR(11),hs_c hange_date,126) +
hs_change_time AS DATETIME))
FROM Sometable
GROUP BY id) AS T (id,hs_change_d t)

This seems like a pointless design that wastes at least 8 bytes of storage
per row and worst of all it makes the data very difficult to maipulate (see
above and compare it with my first answer).

--
David Portas
SQL Server MVP
--