How do I order by most recent addition

[leeboycymru]

I have built a cms with access, and I haven't built any time instruction to be sent to the database when an entry is uploaded.

Do i have to do this to get an order by most recent entry first to last entry last, or is there another way?

 

[leeboycymru]

This is my code:

strsql = "SELECT cat_ID, art_Title, art_Category, art_BDescription, art_ThumbFull, art_Display, art_MoonDisplay, art_Price " &_
"FROM eventDisplay WHERE art_Category='" & cat & "' " & "AND art_Display=1 " &_
"ORDER BY art_Title"

response.Write(cat_ID)

evuser.open strsql, conn

 

[Chopstik]

Depending on how your DB is constructed will answer this question. If you have an Identity (or Autonumber) primary key, you can ORDER BY DESC on the primary key. If you have a date field, you can order by the date in descending order. But without more information, it's harder to give you a more clearly defined answer.

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

 

[rac2]

Yes you need to store a value which tells that the row is the most recent. This could be a timestamp or an autonumber value. Considering that it is common to wish to report on what happened during a period of time, a timestamp would be preferred because it can be used for both sorting and selecting by date range.

Because the order of rows in a table has no meaning. You must have a column in the table which can be sorted if there is some meaningful ordering of the rows.

 

[Chopstik]

It doesn't look like you have a good field on which to order your records (and thereby pull the last record). I would try to add in a date field (or another form of PK like I described above) so that you can order by the order they were entered into your DB.

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

 

[leeboycymru]

Great thanks guys,

so basically i add another field in the add product page which automatically grabs the time and date is it?

and then i can use that to order

cheer

Lee

 

[Chopstik]

Yes, you should be able to do that. But it won't automatically *grab* it, you'll have to insert the data into the field. Otherwise, you can then use it to order the data that you're retrieving.

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

 

[leeboycymru]

If the data has to be entered into the field, maybe its best to use an autonumber which I presume would go from 1 - *, and 1 being the first entry into it and up it climbs.

I could then ask to order by highest number first to lowest last, not sure how to do that yet, but do you agree that maybe a better solution?

Cheers Chopstik

 

[leeboycymru]

I actually just check and I have got an autonumber already on cat_ID, but wheni tried to order by cat_ID it showed first = first & last = last.

Can i use that instead and somehow order it first = last and last = first

cheers again

Lee

 

[Chopstik]

What is your SQL string when you add the ORDER BY statement?

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

 

[leeboycymru]

sorry if this is wrong, but do you mean this:

strsql = "SELECT cat_ID, art_Title, art_Category, art_BDescription, art_ThumbFull, art_Display, art_MoonDisplay, art_Price " &_
"FROM eventDisplay WHERE art_Category='" & cat & "' " & "AND art_Display=1 " &_
"ORDER BY cat_ID"

this is what is displaying the data according to cat_ID

lee

 

[Chopstik]

it should read:

strsql = "SELECT cat_ID, art_Title, art_Category, art_BDescription, art_ThumbFull, art_Display, art_MoonDisplay, art_Price " &_
"FROM eventDisplay WHERE art_Category='" & cat & "' " & "AND art_Display=1 " &_
"ORDER BY cat_ID [COLOR=red]DESC[/color]"

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

 

[leeboycymru]

Thanks again Chopstik,

I'm always grateful for everybody's help on this forum.

Lee

 

[Chopstik]

Glad that you got it to work. And most people help here because they have been helped by others. As you accumulate knowledge, you will find you increasingly have the ability to help others, as well. That is why I think this site is the best of its kind on the web. So feel free to give and take as you can.

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill