SQL question. Need to grab 'newest record' via datetime column

[TechBoyJK]

Building an app and members can create 'friendships' between each other. New concept, i know. When requests are made, they are entered into a table called tbl_requests. Everytime a request is made, I want to query the table and find out the last time that user requests a friend. If the person has made a request less than 2 minutes before the current request, they are going to be given a warning and told to wait. I need to query the table for the newest entry.

tbl_requests

request_id
member_id
friend_id
datetime

Basically, using the datetime field, I need to find the newest record inserted by a certain member (match on member_id)

If the member has 30 records that match on member_id, I only want the newest record.

I'm thinking I should grab all records that match on member_id, group by datetime, sort asc, then grab the top record..

better ideas?

 

[Evadman]

You don't need all the records to answer your question, you only need the time of the most recent. The fastest is probably to order by request_id because it looks like it is an PK auto-number that is indexed. If so, both these will return the same answer. One will be faster than the other depending on index. Replace <member id> with your member id.

The 'correct' answer is to use the 2nd query as it is functionality agnostic.

select top 1 datetime from tbl_requests where member_id = <member id> order by request_id DESC

select top 1 datetime from tbl_requests where member_id = <member id> order by datetime DESC

You can also use the DB to tell you if it has been 2 minutes. dateadd here is ms sql. If any rows are returned, the user failed the wait 2 minutes check, and the field returned will be how much longer to wait.

select top 1 datediff(n,datetime,dateadd(n,-2, getdate())) as waittime from tbl_requests where member_id = <member id> and datetime > dateadd(n,-2, getdate())

 

[TechBoyJK]

wow thanks bro! let me chew on this for a bit!

I tend to let my app server do the processing if possible, even if it's more efficiently done on the db.

It's a lot cheaper for me to replicate the webserver than db server, so I lean towards put the work to the app server since i can scale it up and out easier.

 

[Evadman]

It's a lot cheaper for me to replicate the webserver than db server, so I lean towards put the work to the app server since i can scale it up and out easier.

If you want low load on the DB server above all costs, then create a view with this statement:

select member_id, max(datetime) as lastfriend from tbl_requests group by member_id

Then create an index on those 2 fields in member_id, lastfriend order. lastfriend should be in desc order in the index.

Then use this query:

select max(datetime) from tbl_requests where member_id = <member_id>

The DB server will be smart enough to use the index on the view, which should be exactly 2 I/O requests in almost all cases. I should also point out this is overkill unless your application has several million users.

 

[TechBoyJK]

I'll note this.

I'll probably just run with what works for now. I'm trying to get this app finished and up and running so I an prove the concept and launch a beta.

Once I get over that hurdle, I plan on going over the code with a fine tooth comb and finding better ways to make things work.

 

[Ka0t1x]

Oracle:

select member_id, datetime from tbl_requets order by datetime where rownum=1

 

[LokutusofBorg]

You will see a full order of magnitude performance increase if you get rid of the tbl_ in your naming.

OK, that was facetious, but seriously, I hate naming conventions like that. Not using the shift key on your keyboard is like writing "b4" and whatnot when you're not texting.

Requests

RequestID
MemberID
FriendID
DateAdded

 

[Evadman]

You will see a full order of magnitude performance increase if you get rid of the tbl_ in your naming.

OK, that was facetious, but seriously, I hate naming conventions like that.

There isn't really such a thing as a standard naming convention, but over the years, I have found that the majority of big business and development shops will use the tbl_ convention (and view_ and proc_), more than any other convention. It makes code self documenting.

Not using the shift key on your keyboard is like writing "b4" and whatnot when you're not texting.

That can be dangerous, as some programming languages are case specific. The important part is picking something consistent and sticking to it.

 

[TechBoyJK]

There isn't really such a thing as a standard naming convention, but over the years, I have found that the majority of big business and development shops will use the tbl_ convention (and view_ and proc_), more than any other convention. It makes code self documenting.



That can be dangerous, as some programming languages are case specific. The important part is picking something consistent and sticking to it.

I use it to make the table entities unique in the codebase. Like you said, it's self describing.