Jump to content


Photo

Sorting/Choosing after results returned


  • Please log in to reply
4 replies to this topic

#1 rburch

rburch
  • Members
  • PipPip
  • Member
  • 10 posts

Posted 02 May 2006 - 09:01 PM

I need some experts advice on this one. I've worked on a solution for awhile and am getting no where.

I need to select the newest entry for each id from a transactions table.

Right now each id# can leave multiple records in the table and I am selecting them by "SELECT id, cb FROM $table_name WHERE date <='dateq' AND cb > '#'" (I could group them by id, but I don't see how this would help.)

Now I just need to take it one step more, because this result gives me (or could give me) multiple entries from this table for each id, but I only want to display the latest entry for each id, without knowing what the id's are. I hope that made sesne....

Is this a secondary sort that needs to be done by a function I write or can it be done some other way? If it is a function, any ideas how to write it?

Thanks in advance!!!
"...there's a difference between knowing the path and walking the path." - The Matrix

#2 phporcaffeine

phporcaffeine
  • Members
  • PipPipPip
  • Advanced Member
  • 361 posts
  • LocationOhio, USA

Posted 02 May 2006 - 10:41 PM

if the "date" column is a timestamp you could just put the results in an array and do a sort() on the timestamp element - the greatest or oldest date would be at the bottom then you could array_shift() the first element on the stack and there is the lowest or "newest" timestamp.


All assuming your using timestamps of course.
Thanks,

Ryan Huff
President & Founder, MyCodeTree
support@mycodetree.com | http://mycodetree.com

#3 rburch

rburch
  • Members
  • PipPip
  • Member
  • 10 posts

Posted 03 May 2006 - 10:02 PM

I don't think that will work, because I would need to run that "sort()" on each group of id's that are the same. Maybe this will help... My results look like this:

(id) & (cd) & (date)

12003 & 451 & 2006-05-01 12:35:00
12005 & 358 & 2006-05-02 12:45:00
12005 & 348 & 2006-05-03 14:25:00
12006 & 10 & 2006-05-03 22:30:00
12003 & 350 & 2006-05-03 9:35:00
12005 & 125 & 2006-05-04 11:05:00

I need my results to end up like this:

12006 & 10 & 2006-05-03 22:30:00
12003 & 350 & 2006-05-03 9:35:00
12005 & 125 & 2006-05-04 11:05:00

Make sense? Any new ideas?
"...there's a difference between knowing the path and walking the path." - The Matrix

#4 .josh

.josh
  • Staff Alumni
  • .josh
  • 14,871 posts

Posted 04 May 2006 - 05:02 AM

how about:

SELECT id, cb FROM $table_name ORDER BY date DESC LIMIT 1


Did I help you? Feeling generous? Buy me lunch! 
Please, take the time and do some research and find out how much it would have cost you to get your help from a decent paid-for source. A "roll-of-the-dice" freelancer will charge you $5-$15/hr. A decent entry level freelancer will charge you around $15-30/hr. A professional will charge you anywhere from $50-$100/hr. An agency will charge anywhere from $100-$250/hr. Think about all this when soliciting for help here. Think about how much money you are making from the work you are asking for help on. No, we do not expect you to pay for the help given here, but donating a few bucks is a fraction of the cost of what you would have paid, shows your appreciation, helps motivate people to keep offering help without the pricetag, and helps make this a higher quality free-help community :)

#5 rburch

rburch
  • Members
  • PipPip
  • Member
  • 10 posts

Posted 04 May 2006 - 11:17 PM

Nope. That will only give you this from my example:

12005 & 125 & 2006-05-04 11:05:00

I need the latest result for each unique id.

Anyone else?


"...there's a difference between knowing the path and walking the path." - The Matrix




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users