Query Help

atheimer

Hello All,

I know collate is a keyword in mysql but collate in the sense of a copy machine is what I'm trying to achieve.

Given query: 'select sourceid from houses' returns:

1
1
2
2
2
3
3
3
3
3
3

I would like to display records in this order:
1
2
3
1
2
3
2
3
3
3
3

Would anyone be able t recommend a way to do this via query or is it easier to use a php function to reconstruct the resultset?

Any help would be appreciated!

Thanks

bretticus

You didn't mention the relationship for the "collation." However, I assume that the records need to be ordered by house (let's assume "houseid.") If my assumption is correct, just order by the house before ordering by source. The following is an illustration:

SELECT `sourceid` FROM `houses` ORDER BY `houseid`, `sourceid`

atheimer

Thanks for the reply!

I'm having a hard time explaining the exact output I'm trying to get. Perhaps, think of it this way. By default if you tell a copy machine to make 2 copies of a 2 page document. The resulting output will be page1,page1, page2,page2.
This is similar to the default result order in mysql. The result I'm looking to get to
is page1,page2,page1,page2. The only way I have devised to do this would be to pull back the query results and cursor through them building a new recordset or array but that seems troublesome and inefficient.

The sources table is 20 records with a primary key int autonumber.
The houses table is a +1million record table.

bretticus

Do you not have a primary key for your houses table?

atheimer

The primary key for the houses table is houseid it too is an int autonumber column.

bretticus

atheimer;10885678 wrote:
The primary key for the houses table is houseid it too is an int autonumber column.

Looks like I guessed right.

Did you actually try ordering by houseid first and then sourceid as I suggested? Just asking because that should return your data in the order that you need because MySQL will sort by house id first and then source id. Am I correct in assuming that you are using another table to link houses to sources? If not, perhaps each house record only has one source? If the latter is true, what is the relationship between the house and the source? If I can assume less, I can help more. That depends entirely on you. 😃

atheimer

Thanks again for the help,

Ordering by sourceid doesn't work. Here are the relationships.

House table
houseid(key)
name
sourceid(fkey source.sourceid)

Source table
sourceid(key)
name

bretticus

Okay, so give me a sample of the data and how you want it ordered. Still in the dark here. Maybe by showing some context, you can explain it better.