Order By

the master
10-23-2004, 08:17 AM
hi, got another big prob. its with my forums database again:p

rite, i need to load a list of threads in a certain forum

"SELECT * FROM ThreadData WHERE threaddata.inforum=" & forumid

ok, that works fine but the threads are in the wrong order. i need to link it to the posts table, get the last post in that thread then order the threads by that date/time

(posts has a posts.inthread and .ID thingy)

EDIT: i have managed it but it displays all the posts records too. any way round that? can i simply hide record that are from a certain table?

MKoslof
10-23-2004, 10:00 AM
"SELECT <FIELDS> FROM <Table Name> WHERE <Field> = <MyCondtion> ORDER BY <Field> DESC"

the master
10-23-2004, 10:02 AM
i know how to do that but i want just the records in 1 table ordering by values in a diff table. i need to get the date of the last post in each thread then order the threads by that

MKoslof
10-23-2004, 10:04 AM
So order by your date field :). You know the structure required, so now just piece together the appropriate query. The only way you can order by the values in a different table is if you create a query that contains both tables. You need to write a JOIN and then order by one of the fields returned.

the master
10-23-2004, 10:12 AM
So order by your date field :). You know the structure required, so now just piece together the appropriate query. The only way you can order by the values in a different table is if you create a query that contains both tables. You need to write a JOIN and then order by one of the fields returned.

i already did that but i keep getting blank records because i think its displaying a seperate record for each post in each thread in that table

Dennis DVR
10-23-2004, 10:13 AM
i know how to do that but i want just the records in 1 table ordering by values in a diff table. i need to get the date of the last post in each thread then order the threads by that

in addition to what has been said, you can use the MAX aggregate function in your date of post field together with the group by clause in the join field.

the master
10-23-2004, 10:14 AM
in addition to what has been said, you can use the MAX aggregate function in your date of post field together with the group by clause in the join field.

erm, whats that? sounds like another language to me:p hehe. i bet you can tell ive not been doing databases too long

MKoslof
10-23-2004, 10:16 AM
Well you are going to have to show us the entire query or we can not be of more help. If you attempted a JOIN, show us that query...we'll fix it.

the master
10-23-2004, 10:19 AM
well, i started with a join type thingy but it got confusing and i ended up just putting everything in the WHERE part. could that be it? this is how it looks

"SELECT * FROM ThreadData, UserData, PostData WHERE PostData.InThread=ThreadData.ThreadID AND UserData.UserID=ThreadData.Starter AND ThreadData.InForum=" & ID & "ORDER BY PostData.EditDate Desc"

MKoslof
10-23-2004, 10:29 AM
You are attempting a natural join, or inner join. This query design would basically return the same results as an INNER JOIN. The problem is not the order by, it is the returned records..obviously you don't want to do an inner join here. Based on the records returned what is not correct? If you need to restrict your records in a specific manner, then the query needs to be appropriate. Is .InForum a string or numeric type. Are you looking to return unique records and you are getting duplicates returned? Are you going to have to give us more information :)

the master
10-23-2004, 10:36 AM
You are attempting a natural join, or inner join. This query design would basically return the same results as an INNER JOIN. The problem is not the order by, it is the returned records..obviously you don't want to do an inner join here. Based on the records returned what is not correct? If you need to restrict your records in a specific manner, then the query needs to be appropriate. Is .InForum a string or numeric type. Are you looking to return unique records and you are getting duplicates returned? Are you going to have to give us more information :)

lol, my explanations arnt the world best but ill try and tell you everything i can.

i have 3 tables in that query. ThreadData, PostData and UserData. i want to load those 3 so it displays all the threads

EG "SELECT * FROM ThreadData WHERE ThreadData.InForum=1"

that should return all threads that were started in the forum with ID 1. (InForum is a numeric value and the forum ID is an autonumber PK)

now i need to put them in the right order. i have the posts table which stores the date each post was posted and what thread its in (Date and InThread)

i need it to order the threads by the date of the last post that was posted in it. the user data is because the field Starter is numeric and stores a user ID number. the users part seems to be working ok. i only want to display 1 record per thread BTW.

to see it messing up, go here Http://www.spooktech.org/text/forums/showforum.spk?ID=8

MKoslof
10-23-2004, 10:50 AM
OK, yes this is a correct statement:



EG "SELECT * FROM ThreadData WHERE ThreadData.InForum=1"

that should return all threads that were started in the forum with ID 1. (InForum is a numeric value and the forum ID is an autonumber PK)



When dealing with this single table, that query should return all records from the ThreadData table where the ID is 1.

Now is where we find the problem. You want to compare this to the Posts table. You can JOIN on ID number if that is your foreign key relationship. BUT..you say you want to display unique records. So if you want ONLY the MAX() date and the unique records that go with it, you should conduct a MAX() aggregate query (as already mentioned and group by the field in question). So, say I want to get the Max() date for the Field1 Field, I would group by Field1 in DESC or ASC order.

I am still not clear exactly what fields you want to return, but the structure would be something like:



SELECT * FROM ThreadData as T
INNER JOIN PostData as P
ON T.ID = P.ID
WHERE P.DateField IN
(SELECT MAX(P2.DateField) FROM PostData P2 WHERE P2.ID = T.ID)

the master
10-23-2004, 10:54 AM
so whats going off there then? is that basicly the same as filtering everything then filtering the filtered stuff? and hows the max thing work exactly. theres just a big statement there and im all confused

MKoslof
10-23-2004, 10:57 AM
OK, I really suggest you review some Sql tutorials and native MS Access websites. A MAX() sql method is an aggregate function that returns the highest value, typically used for dates and numeric types. Here is a good overall sql tutorial that can be intergrated into basic Access syntax, it focuses on SQL Server, but the ANSI standards still apply in Access:

http://www.databasejournal.com/sqletc/article.php/1402351

the master
10-23-2004, 10:59 AM
thanx, i will have a read through that and try to fix this thing ASAP

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum