SEO

#1 (**permalink**) 02-28-2008, 07:45 PM

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

#2 (**permalink**) 02-28-2008, 07:45 PM

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_dt) AS hs_change_dt
FROM Sometable
GROUP BY id

--
David Portas
SQL Server MVP
--

#3 (**permalink**) 02-28-2008, 07:46 PM

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" <REMOVE_BEFORE_REPLYING_dportas@acm.org> wrote in message news:<7_2dnaQoLZ200YTdRVn-uQ@giganews.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_dt) AS hs_change_dt
> FROM Sometable
> GROUP BY id

#4 (**permalink**) 02-28-2008, 07:46 PM

> 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,108) AS hs_change_time
FROM
(SELECT id,
MAX(CAST(CONVERT(CHAR(11),hs_change_date,126)+
hs_change_time AS DATETIME))
FROM Sometable
GROUP BY id) AS T (id,hs_change_dt)

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

searching for latest date and time only