Displaying edited articles by latest edited date 
Author Message
 Displaying edited articles by latest edited date
I need to display edited stories by there storythread number and there last
edited date. I have selected a single story using the folowing sql.

sql="SELECT * " & _
"FROM News WHERE DateModified = (SELECT MAX(DateModified) FROM News WHERE
StoryThread = " & StoryThread & ")"

But I now need to display a number of the latest articles on the same page.
Could anyone offer any suggestions on how to do this.

Thanks in advance.



Mon, 01 Mar 2004 12:03:11 GMT
 Displaying edited articles by latest edited date

select top 5 * from news by DateModified desc
    - or somesuch. -- Dan


Quote:
> I need to display edited stories by there storythread number and there
last
> edited date. I have selected a single story using the folowing sql.

> sql="SELECT * " & _
> "FROM News WHERE DateModified = (SELECT MAX(DateModified) FROM News WHERE
> StoryThread = " & StoryThread & ")"

> But I now need to display a number of the latest articles on the same
page.
> Could anyone offer any suggestions on how to do this.

> Thanks in advance.



Mon, 01 Mar 2004 12:28:33 GMT
 Displaying edited articles by latest edited date

Thanks for the reply.

But I need to display the record for the latest modified date for each different storythread value.  So I need to get multiple records from the sql query.  Doing what you suggest would only get the latest 5 records of any storythread which is not what I want.

Cheers

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Mon, 01 Mar 2004 14:59:22 GMT
 Displaying edited articles by latest edited date
David -

Include prior thread posts in your reply, please.

You said "...I now need to display a number of the latest articles on the
same page.", not "I now need to display a number of the latest articles from
the same story thread on the same page."  Your further post seems to imply
that you have:

Story 1
    Most recent article for Story 1
Story 2
    Most recent article for Story 2
Story 3
    Most recent article for Story 3

and that you want:

Story 1
    Most recent article for Story 1
    Next most recent article for Story 1
    The one before that for Story 1
Story 2
    Most recent article for Story 2
    Next most recent article for Story 2
Story 3
    Most recent article for Story 3
    Next most recent article for Story 3
    The one before that for Story 3
    The even earlier one for Story 3

Is this correct?

    - Dan


Thanks for the reply.

But I need to display the record for the latest modified date for each
different storythread value.  So I need to get multiple records from the sql
query.  Doing what you suggest would only get the latest 5 records of any
storythread which is not what I want.

Cheers

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

---------------------
Dan's reply to David
---------------------

Quote:
>select top 5 * from news by DateModified desc
>    - or somesuch. -- Dan

--------------------
David's earlier post:
--------------------
Quote:
>>I need to display edited stories by there storythread number and there
last
>>edited date. I have selected a single story using the folowing sql.

>>sql="SELECT * " & _
>>"FROM News WHERE DateModified = (SELECT MAX(DateModified) FROM News
>>WHERE StoryThread = " & StoryThread & ")"

>>But I now need to display a number of the latest articles on the same
page.
>>Could anyone offer any suggestions on how to do this.

>>Thanks in advance.



Tue, 02 Mar 2004 04:49:49 GMT
 Displaying edited articles by latest edited date
What you stated below is what I am trying to achieve.  I can get the most recent story for one article but I want to get the most recent story for multiple articles.

"Story 1
Most recent article for Story 1
Story 2
Most recent article for Story 2
Story 3
Most recent article for Story 3"

Not what is shown below:

Story 1
Most recent article for Story 1
Next most recent article for Story 1
The one before that for Story 1
Story 2
Most recent article for Story 2
Next most recent article for Story 2
Story 3
Most recent article for Story 3
Next most recent article for Story 3
The one before that for Story 3
The even earlier one for Story 3

Cheers

David -

Include prior thread posts in your reply, please.

You said "...I now need to display a number of the latest articles on the
same page.", not "I now need to display a number of the latest articles from
the same story thread on the same page." Your further post seems to imply
that you have:

Story 1
Most recent article for Story 1
Story 2
Most recent article for Story 2
Story 3
Most recent article for Story 3

and that you want:

Story 1
Most recent article for Story 1
Next most recent article for Story 1
The one before that for Story 1
Story 2
Most recent article for Story 2
Next most recent article for Story 2
Story 3
Most recent article for Story 3
Next most recent article for Story 3
The one before that for Story 3
The even earlier one for Story 3

Is this correct?

- Dan


Thanks for the reply.

But I need to display the record for the latest modified date for each
different storythread value. So I need to get multiple records from the sql
query. Doing what you suggest would only get the latest 5 records of any
storythread which is not what I want.

Cheers

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

---------------------
Dan's reply to David
---------------------

Quote:
>select top 5 * from news by DateModified desc
> - or somesuch. -- Dan

--------------------
David's earlier post:
--------------------

Quote:
>>I need to display edited stories by there storythread number and there
last
>>edited date. I have selected a single story using the folowing sql.

>>sql="SELECT * " & _
>>"FROM News WHERE DateModified = (SELECT MAX(DateModified) FROM News
>>WHERE StoryThread = " & StoryThread & ")"

>>But I now need to display a number of the latest articles on the same
page.
>>Could anyone offer any suggestions on how to do this.

>>Thanks in advance.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!


Tue, 02 Mar 2004 07:35:21 GMT
 Displaying edited articles by latest edited date
Sorry, I was flying Friday.

This will get the latest date modified for all the story threads:
    select StoryThread, max(DateModified)
    from News group by StoryThread

You may not want to display the latest story for the thread you are on:
    select StoryThread, max(DateModified)
    from News
    group by StoryThread

Hopefully, DateModified is unique.  BTW, we don't really want or need
StoryThread in the select list
    select max(DateModified) from News
    group by StoryThread

Now, select the stuff you want:
    select * from News where DateModified in (
        select max(DateModified) from News
        group by StoryThread

    )
    order by <however you want the articles listed>

If DateModified is NOT unique then this will pull the latest stories off of
their respective threads, but it might also pull a not-most-recent stories
off of other threads, as well.

If this is the case (DateModified is not unique), you need to do tricky
stuff, where you conglomerate enough data in a sortable way to uniquely
identify a record...or you have to resort to using two queries.

Here's one way of conglomerating data:  You need to comglomerate enough
fields until you have a uniqie key in there, and can still sort by date.
You might choose the date and a unique NewsID field.  Convert the date to a
decimal number, and pad it to the right with spaces, so an alpha sort works.
The do the same to the NewsID field.  Then concatenate these.  Now, your
max() function will be taking the max date, and the max ID within that:
    max(
        right(
            space(16) +
            convert(varchar,
                convert(decimal(15,5),
                    DateModified
                )
            ),
            16
        )
        + '|' +
        right(space(11) + convert(varchar,NewsID), 11)
    )
(And, of course, your main select statement needs to build the same
aggregation for the in(subquery) comparison.)

Hope this helps,    - Dan


Quote:
> What you stated below is what I am trying to achieve.  I can get the most

recent story for one article but I want to get the most recent story for
multiple articles.
Quote:

> "Story 1
> Most recent article for Story 1
> Story 2
> Most recent article for Story 2
> Story 3
> Most recent article for Story 3"

> Not what is shown below:

> Story 1
> Most recent article for Story 1
> Next most recent article for Story 1
> The one before that for Story 1
> Story 2
> Most recent article for Story 2
> Next most recent article for Story 2
> Story 3
> Most recent article for Story 3
> Next most recent article for Story 3
> The one before that for Story 3
> The even earlier one for Story 3

> Cheers

> David -

> Include prior thread posts in your reply, please.

> You said "...I now need to display a number of the latest articles on the
> same page.", not "I now need to display a number of the latest articles
from
> the same story thread on the same page." Your further post seems to imply
> that you have:

> Story 1
> Most recent article for Story 1
> Story 2
> Most recent article for Story 2
> Story 3
> Most recent article for Story 3

> and that you want:

> Story 1
> Most recent article for Story 1
> Next most recent article for Story 1
> The one before that for Story 1
> Story 2
> Most recent article for Story 2
> Next most recent article for Story 2
> Story 3
> Most recent article for Story 3
> Next most recent article for Story 3
> The one before that for Story 3
> The even earlier one for Story 3

> Is this correct?

> - Dan



> Thanks for the reply.

> But I need to display the record for the latest modified date for each
> different storythread value. So I need to get multiple records from the
sql
> query. Doing what you suggest would only get the latest 5 records of any
> storythread which is not what I want.

> Cheers

> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

> ---------------------
> Dan's reply to David
> ---------------------
> >select top 5 * from news by DateModified desc
> > - or somesuch. -- Dan

> --------------------
> David's earlier post:
> --------------------
> >>I need to display edited stories by there storythread number and there
> last
> >>edited date. I have selected a single story using the folowing sql.

> >>sql="SELECT * " & _
> >>"FROM News WHERE DateModified = (SELECT MAX(DateModified) FROM News
> >>WHERE StoryThread = " & StoryThread & ")"

> >>But I now need to display a number of the latest articles on the same
> page.
> >>Could anyone offer any suggestions on how to do this.

> >>Thanks in advance.

> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!



Sat, 06 Mar 2004 05:46:48 GMT
 
 [ 6 post ] 

 Relevant Pages 

1. Displaying dates in masked edit boxes

2. Can't display Date in an edit box

3. VFP5 - Can I Edit an Edit Box?

4. editing drop down edit list

5. edit or not edit !!! ;-)

6. D1-Oracle-Tquery-Dbgrid edit gives 'couldnt perform edit cos another user changed record

7. VFP5 - How to edit an edit box

8. Opal Edit Window-Text not displayed

9. newbie ?, display/edit a database

10. Weird size-display within edit-database-dialog ?

11. Displaying/editing data

12. Manipulating OLE - open() edit() and display as icon


 
Powered by phpBB® Forum Software