Anyone really good with order by?

Anon

Hello, I been having a problem here for long time that I can't fix if anyone has a suggestion please post it.

Now let me explain what the problem.
In my PHP code I run this sql statement which suppose to retriave the most recent records in DESC order by time stamp. Here is the select statement:
"select (all my columns here) from DB order by time(timestamp) DESC limit 15".
This works and it pulls up all last 15 records. I also have a column varchar which holds company names and its called (company_name). All I need to do is make the records I pull up to be in alphabetical order by that company_name column.
This is what I tried
"select (all my columns here) from DB order by time DESC, company_name limit 15".
This doesn't do any changes that are caused by the 1st statement.

I also tried group by company_name. But it Doesn't work also.

To summirize this: All I want to do is pull up last 15 records that been entered and put them in alphabetical order.

If you can suggest something please do so.
Thank you

Vadim
icq #10363340

Anon

select (all my columns here) from DB order by time DESC, company_name limit 15

The reson this wont work is because its sorting everything by time descending, then inside those results it will sort by company name. This is only usefull if u have more than 1 company with the same time becuase it will sort those values by company name.

example:
Here is a table of records
time Name
2:00 Jim
2:00 Bob
5:00 Mary
1:00 Ryan

if you do the sql statement above, you will get a new table that looks like the following
time Name
5:00 Mary
2:00 Bob
2:00 Jim
1:00 Ryan

Notice Bob and Jim are now sorted alpabetically because they have the same time.

what i think you want is to pull the last 15 records, and then sort all those alpahbetically. I am thinking of a way to to that at the moment.

Anon

If it's just 15 records, put the incoming data into an array and then sort that right inside PHP. The only way to do it in the database itself is to save the initial results into a temp table, then query the temp table with an ORDER BY company_name. It's possible a subselect may get you what you want, depending on how much support your dbms provides for them.

Anon

Hi guys,

I see now what you mean. But I still don't see a perfect way out of it. I never created temp table. Could you explain how to do it?
Thanks a lot!

Anon

Kirk,

How would I sort it in PHP then? Is there a build in function?
- Vadim

Anon

Try selecting the time of the 15th record from your query that is sorted by time using the offset ability of a select statement. Then using that value for time, you can do a

select * from table where time > $last15tim order by name;

You would be querying twice to do that though, and it may be much easier to dump into an array and sort it in PHP if it is really for display purposes only.

Check out the "array functions" section of the PHP documentation, it has what you need to create and sort arrays. (For PHP 4.x.x that is, 3.x.x you have to do a lot yourself)

Tim Frank

Anon

Hi,
What do you mean by $last15tim ?

Thanks

Anon

I think he means puting the result of a first query like this one:

select your_timestamp from your_table order by your_timestamp limit 15,1;

in a $last15tim variable and then use the second query to get all the stuff sorted... As someone already suggested, it would be better to store the result in an array and have it sorted in PHP...

--Gildas.

Anon

Could anyone give me an example how to put the sql_result into an array.

Anon

Thanks a lot to everyone, With you help I have achived my destination. I didn't use the array method I used the timestamp methord but no matter what it works. See it for yourself at DealDatabase.com. Don't know if I can use html here but i'll try to make it linkable.
<a href="http://www.dealdatabase.com">DealDatabase.com</a>

Thanks 🙂