Ordering question v. MySQL

[KevySaysBeNice]

Hi all!

I've got a potentially large data set that I'm trying to work with, and I'm trying to figure out the best/most efficient way of getting data out of a DB. Here is a simplified example:

PERSON     TIME
-------------------------
2                 8:00
1                 9:30
1                 9:45
2                10:45
2                11:00
1                11:30

In this example, I might have done something like:

SELECT person, time FROM table ORDER BY time

My GOAL, is to order by time, BUT return all of the "person" values together. So, I'd LIKE to return something like this:

PERSON     TIME
-------------------------
2                  8:00
2                10:45
2                11:00
1                 9:30
1                 9:45
1                11:30

Does this make sense? So something like

SELECT person, time FROM table order by TIME, STICK THESE THINGS TOGETHER NO MATTER WHAT person

(note I made up the "STICK THESE THINGS TOGETHER NO MATTER WHAT" bit )

Thanks for any help!!!



EDIT: And for anybody that cares, the reason I care about this is because I want to get the latest data out first, and if I know the data is in the correct order coming out of the DB then I can iterate through the data (each "person" ID represents an object that I'm building as I iterate through) once and know I have everything. So if I want to get the latest updates for 3 people, I can return all of the data, then iterate through the data and move onto the next object once the "person" ID changes.

 

[Sgraffite]

Like this?

SELECT person, time FROM table ORDER BY person DESC, time ASC

 

[KevySaysBeNice]

Thanks for the reply!

I believe that would return the "person" ID "together", in order as I mentioned, however the latest time wouldn't be first.

I could get all of my data this way, but then I'd have to go through all of it (PHP) to make sure I had the people in the correct order (by the latest Time).

 

[Sgraffite]

Thanks for the reply!

I believe that would return the "person" ID "together", in order as I mentioned, however the latest time wouldn't be first.

I could get all of my data this way, but then I'd have to go through all of it (PHP) to make sure I had the people in the correct order (by the latest Time).

Maybe I'm not understanding what you want, but if you want the most recent time first, you can just switch the ORDER BY like so:

SELECT person, time FROM table ORDER BY person DESC, time DESC

Your goal example data shows oldest first with respect to person, however.

 

[mikeblas]

By the "latest data first", are you saying you want the first person to be the one with the latest data -- and all the data for that person -- then the all the data for the next latest person?

 

[pat1006]

EDIT: And for anybody that cares, the reason I care about this is because I want to get the latest data out first, and if I know the data is in the correct order coming out of the DB then I can iterate through the data (each "person" ID represents an object that I'm building as I iterate through) once and know I have everything. So if I want to get the latest updates for 3 people, I can return all of the data, then iterate through the data and move onto the next object once the "person" ID changes.

Why not build a dictionary where the key is the person id and the value is the person object? That way you can return the times in order but still interact with each person object.

 

[mikeblas]

Why not build a dictionary where the key is the person id and the value is the person object? That way you can return the times in order but still interact with each person object.

Because doing it in PHP won't scale too well.



You haven't clarified what you've wanted, Sgraffite, and you also haven't told us which DBMS you're using.

If you're using SQL Server, this script demonstrates a technique that does what I guess you're asking for in post #5. If you're running MySQL, the script might not work because MySQL has some limitations around subselects; you might ifnd that it works OK if you're using a version that's new enough.

CREATE TABLE Persons
(PersonID INT, TheTime TIME);

INSERT INTO Persons (PersonID, TheTime) VALUES
( 2, '8:00'),
( 1, '9:30'),
( 1, '9:45'),
( 2, '10:45'),
( 2, '11:00'),
( 1, '11:30');

SELECT Persons.PersonID, Persons.TheTime, X.PersonTime
FROM Persons
JOIN
(
SELECT PersonID, MIN(TheTime) AS PersonTime
  FROM Persons GROUP BY PersonID
) AS X
ON Persons.PersonID = X.PersonID
ORDER BY PersonTime ASC;

 

[Sgraffite]

You haven't clarified what you've wanted, Sgraffite, and you also haven't told us which DBMS you're using.

I think you need more coffee this morning, KevySaysBeNice is the one asking the question, and he stated MySQL in the thread title

 

[mikeblas]

Well, one out of three ain't bad. You'll need MySQL 5.0 or newer to make that work, I think.

 

[mine37388]

I created a MySQL table using mikeblas' create table and insert SQL queries. on a database server that is MySQL v.5.1.24 and tested Sgraffite's SQL query from post #2. The output is what is specified under the goal in the original post.

Note: pid = personid and times = time

 

[mikeblas]

It's what's specified, but it breaks if you add better sample data. Assuming that my guess is correct about what KevySays is really right. Thing is, Kevy hasn't bothered to come back to the thread despite our efforts to help him, so we might never know.

 

[KevySaysBeNice]

First,

I am sorry I didn't get back to this thread earlier. I honestly, truly appreciate your help on this, and considering you've gone so far out of your way to help I'm sorry I didn't get back in a more timely manner! Basically, I got started on a different angle of my project and then was at home for the holidays, and am just now getting back to this! Again though, I should have at least kept up with this thread, sorry! I normally try to be a bit better following up, especially when people have spent time helping me. Honestly, I am sorry.

Second,

Anyway, mikeblas, you're right - mine37388, thanks a ton for going through the trouble, but this only works because it just so happens that pid=2 has the smallest time. If you were to add a new pid=3 and have a greater time (say 10:00:00) and run the query, you'd end up with that PID on top despite the time being less.