can you help me please?
table 1
id (PK) | windowID | content | dateadded | visible
I need to select from the above table the content with disticnt windowID's
with the latest dateadded where visible is True
Thanks
Andy
SELECT windowid, content
FROM Table1 AS T
WHERE visible = 'True'
AND dateadded =
(SELECT MAX(dateadded)
FROM Table1
WHERE windowid = T.windowid
AND visible = 'True')
David Portas
SQL Server MVP
|||Brilliant!
Thanks David,
it works great
Andy
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:pbudnWWRrMuKF2jcRVn-pA@.giganews.com...
> SELECT windowid, content
> FROM Table1 AS T
> WHERE visible = 'True'
> AND dateadded =
> (SELECT MAX(dateadded)
> FROM Table1
> WHERE windowid = T.windowid
> AND visible = 'True')
> --
> David Portas
> SQL Server MVP
> --
>
|||This could return multiple rows for a single windowid if the windowid's last
dateadded appears more than once in the table. Here's my stab at it:
DECLARE @.MyTable TABLE
(
WID int,
DateAdd datetime,
TableID int,
Content blah
)
--Generate the list of objects
INSERT INTO @.MyTable (WID)
SELECT DISTINCT t.windowid
FROM Table1 t
WHERE visible = 'True'
--fetch the target DateAdded
UPDATE @.MyTable
SET DateAdd =
(
SELECT Max(t.DateAdded)
FROM Table1 t
WHERE visible = 'True'
and t.windowid = mt.WID
)
FROM @.MyTable mt
--fetch ID to break ties in DateAdded by an arbitrary rule (max)
UPDATE @.MyTable
SET TableID =
(
SELECT Max(t.ID)
FROM Table1 t
WHERE visible = 'True'
and t.windowid = mt.WID
and t.DateAdded = mt.DateAdd
)
FROM @.MyTable mt
UPDATE @.MyTable
SET Content = (SELECT t.Content FROM Table1 t WHERE t.ID = mt.TableID)
FROM @.MyTable mt
SELECT WID as windowid, Content as content
FROM @.MyTable mt
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:pbudnWWRrMuKF2jcRVn-pA@.giganews.com...
> SELECT windowid, content
> FROM Table1 AS T
> WHERE visible = 'True'
> AND dateadded =
> (SELECT MAX(dateadded)
> FROM Table1
> WHERE windowid = T.windowid
> AND visible = 'True')
> --
> David Portas
> SQL Server MVP
> --
>
sql
