SQL structure question

[Daen]

Hey guys, not really familiar with complex SQL queries, so I thought I'd ask and see if you could give me a little help on this one.  I have a simple message board and I want to be able to select the post IDs of the first and last posts in the thread.  I have a column called PostOrder which is essentially a column that increments as posts are added to a thread, that way the message board can show them in the right order.  They're identified by the MIN(PostOrder) as the first post in the thread, and MAX(PostOrder) as the last in the thread. 

So, essentially I want to be able to get out the PostID of the first and last posts in the thread on one row of a result set.  But, I'm not sure how to go about doing it.  I know it's possible, but not sure how.

Essentially I want something like this:

SELECT PostID FROM Posts WHERE PostOrder=MIN(PostOrder) AND ThreadID=$threadID

and

SELECT PostID FROM Posts WHERE PostOrder=MAX(PostOrder) AND ThreadID=$threadID

in the same query so they return on the same row.  Unfortunately, I can't even get that syntax to work because I get an invalid use of group function error.

Does anyone know how I could get that information out?  I appreciate any help you could give.

[shoz]

[code]
SELECT
MAX(PostID) AS maxPostId, MIN(PostID) AS minPostId
FROM
PostOrder
WHERE
ThreadID=$threadID
[/code]

[Daen]

That didn't work.  PostOrder and PostID are fields in the table Posts. 

[pre]
---------------------------------------------------------------
|                          Posts                              |
---------------------------------------------------------------
| PostID    | PostOrder    | ThreadID      | OtherStuff...    |
---------------------------------------------------------------
|          |              |              |                  |
|          |              |              |                  |
|          |              |              |                  |
---------------------------------------------------------------
[/pre]

I suppose I could select the MIN/MAX of PostID because the PostID field is just an auto-increment as long as I have the WHERE ThreadID=$threadID, but I'd rather not because I'm not sure if that's guaranteed to be in the right order, whereas the PostOrder field does guarantee the order.

[shoz]

[quote author=Daen link=topic=109627.msg442192#msg442192 date=1159333487]
That didn't work.  PostOrder and PostID are fields in the table Posts. 

[pre]
---------------------------------------------------------------
|                          Posts                              |
---------------------------------------------------------------
| PostID    | PostOrder    | ThreadID      | OtherStuff...    |
---------------------------------------------------------------
|          |              |              |                  |
|          |              |              |                  |
|          |              |              |                  |
---------------------------------------------------------------
[/pre]

I suppose I could select the MIN/MAX of PostID because the PostID field is just an auto-increment as long as I have the WHERE ThreadID=$threadID, but I'd rather not because I'm not sure if that's guaranteed to be in the right order, whereas the PostOrder field does guarantee the order.
[/quote]

I misread your post, as can be seen by my reference to PostOrder as the table name.

[quote=Daen]
I want to be able to get out the PostID of the first and last posts in the thread on one row of a result set.
[/quote]

If you're using MYSQL 4.1 or higher then you can use the following
[code]
SELECT
(
    SELECT
    PostID
    FROM
    Posts
    WHERE
    PostOrder = (SELECT MIN(PostOrder) FROM Posts)
) AS min_id
,
(
    SELECT
    PostID
    FROM
    Posts
    WHERE
    PostOrder = (SELECT MAX(PostOrder) FROM Posts
) AS max_id
[/code]

If you don't have an INDEX on PostOrder you should add one.
[code]
ALTER TABLE Posts ADD UNIQUE(PostOrder)
[/code]

[Daen]

I'm still getting an error, but I don't understand why.  Running it through phpMyAdmin gives:

[code]#1064 - You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT
    PostID
    FROM
    Posts
    WHERE
    PostO [/code]

It seems like it should work to me... I added a couple of parenthesis after the inner 'SELECT MAX/MIN' statements in order to maintain the correct number of them, but I would think that would prevent problems rather than cause them... Any ideas on this error?  I still even get an error (same one as above) if I remove all the surrounding SQL and just run

[code]SELECT PostID FROM Posts WHERE PostOrder=(SELECT MIN(PostOrder) FROM MBPosts WHERE ThreadID=$threadID)[/code]

As to adding an index on PostOrder, it's not unique.  There are multiple threads in the table and each one's posts has PostOrder going from 1 to however many posts there are in the thread.  Is there a way to add an index to a non-unique field?

[shoz]

What version of MYSQL are you using. You can do a
[code]
SELECT VERSION()
[/code]
to find out. Remember that you'll need to be using MYSQL 4.1 or higher to use Subqueries

There is an error in my previous post however. A missing ")" after the "SELECT MAX(PostOrder)....)" and the ommision of "WHERE ThreadID = $ThreadID". The error you posted doesn't seem to be related to that but more likely to the lack of support for Subqueries.
[code]
SELECT
(
    SELECT
    PostID
    FROM
    Posts
    WHERE
    PostOrder = (SELECT MIN(PostOrder) FROM Posts WHERE ThreadID=$threadID)
    AND
    ThreadID=$threadID
) AS min_id
,
(
    SELECT
    PostID
    FROM
    Posts
    WHERE
    PostOrder = (SELECT MAX(PostOrder) FROM Posts WHERE ThreadID=$threadID
    AND
    ThreadID=$threadID
) AS max_id
[/code]

[quote author=Daen link=topic=109627.msg442441#msg442441 date=1159370905]
As to adding an index on PostOrder, it's not unique.  There are multiple threads in the table and each one's posts has PostOrder going from 1 to however many posts there are in the thread.  Is there a way to add an index to a non-unique field?
[/quote]

You can add an index by replacing the keyword "UNIQUE" with "INDEX", but a more appropriate index for the query would still be UNIQUE but instead span "ThreadID" and "PostOrder"
[code]
ALTER TABLE Posts ADD UNIQUE(ThreadID, PostOrder);
[/code]

[quote author=Daen link=topic=109627.msg442192#msg442192 date=1159333487]
I suppose I could select the MIN/MAX of PostID because the PostID field is just an auto-increment as long as I have the WHERE ThreadID=$threadID, but I'd rather not because I'm not sure if that's guaranteed to be in the right order, whereas the PostOrder field does guarantee the order.
[/quote]

I've tried to find information on the subject, but as far as I know as long as you let MYSQL handle the generation of the auto_incremented column it should always have the order that rows were inserted. At least for MYISAM and INNODB tables.

Although I've seen comments about not relying on the auto_increment column to get the order that rows were inserted. I'm not familiar with any reason not to.

The only thought I have is if there's a situation where the data needs to be combined with data from another table in which case keeping a record of the DATE and TIME of the inserts would help recreate an auto_increment column.

I'll ask the question in the forum and see if anyone has any input and I'll expand on the thought.

Note that if by in the "Right Order" you mean [1,2,3,4]. Then no it is not guaranteed to be in the right order. If you simply mean the first inserted value will have a value lower than the last, then that's what I'm discussing.

EDIT: Btw, If you don't have Subquery support you can do the following to get the Ids
[code]
//run the following query to get the minId.
SELECT
PostId
FROM
Posts
WHERE
ThreadID=$threadID
ORDER BY PostOrder ASC
LIMIT 1


//run the following to get the max
SELECT
PostID
FROM
Posts
WHERE
ThreadID=$threadID
ORDER BY PostOrder DESC
LIMIT 1
[/code]

[Daen]

Well, curses: it looks like my host is running MySQL 4.0.24.  I had thought it was 4.1, but I guess the lack of subquery support would explain some of the errors I've been getting in other queries I've tried to run.  I'll have to see if there's anyway I can get them to upgrade the version.

Yeah, with regard to using the PostID to sort them, I was talking about just one inserted first having a lower number than one inserted later-- not necessarily 1,2,3,4. 

I really appreciate the help, though.  I managed to get the last query you posted to work as expected using a UNION.

Thanks again!