Jump to content

[SOLVED] ORDER BY Help :)


jackpf

Recommended Posts

Hi

 

Almost there. Problem you have now is that `Subject`, `Status`, `Stats` and `Author` probably do not have consistent values for one value of thread (well, subject probably does). As such the values of these could be taken from any of the rows for that thread and are pretty meaningless.

 

All the best

 

Keith

Link to comment
Share on other sites

Hi

 

That depends on what you want those fields to contain. Do you want the author of the first post in the thread, for example?

 

If so try this (not tested):-

 

SELECT a.id as ThreadId, a.Subject, a.Status, a.Stats, a.Author, COUNT(b.id) AS PostCount
FROM Forum a
LEFT OUTER JOIN Forum b ON a.id = b.thread
WHERE a.Type = 'thread'
AND a.id = a.Thread
GROUP BY ThreadId, a.Subject, a.Status, a.Stats, a.Author
ORDER BY PostCount

 

The extra columns in the group by are possibly not required with MySQL in this situation (as they relate to a.Id / ThreadId which is being grouped on anyway), but I have put them there are some flavours of SQL do object

 

All the best

 

Keith

 

Edit - fixed a line I missed in the SQL

Link to comment
Share on other sites

You beauty kickstart :D

 

Works perfectly.

 

End product:

SELECT F.*, COUNT(P.`ID`) AS `PostCount`
FROM `Forum` F
LEFT OUTER JOIN `Forum` P ON F.`ID` = P.`Thread`
WHERE F.`Type` = 'thread'
GROUP BY F.`ID`
ORDER BY `PostCount` DESC

 

Sorry for my initial retardation lol. I appreciate your time. Cheers.

Yeah, Fenway and Ken2k7, you have my thanks as well.

Link to comment
Share on other sites

Yeah, as you may have noticed, I'm not too experienced at joins.

 

I was just wondering, would it make much difference if I used an inner join rather than left outer join? I've heard inner joins are faster..?

 

Cheers :)

Link to comment
Share on other sites

Yeah sure:

$query = "SELECT F.*, COUNT(P.`ID`)+1 AS `PostCount`
FROM `$tb_Forum` F
LEFT OUTER JOIN `$tb_Forum` P ON F.`ID`=P.`Thread`
WHERE F.`Type`='thread'
GROUP BY F.`ID`
ORDER BY IF(SUBSTRING_INDEX(F.`Status`, '.', -1)=1, 0, 1), `PostCount` DESC";

 

Just for reference the first ORDER statement is to order sticky threads at the top, the +1 on the post count is to take the original thread into account, and $tb_Forum is subtley my forum table :)

 

Cheers.

Link to comment
Share on other sites

Hi

 

Is there still a problem here?

 

While generally you should have all the non agregated returned columns (ie, in this case all except the count) in the group by clause, MySQL does allow you to not do this. However if the extra returned columns do not match the group by item then their values will be pretty meaningless. For example say you had a table of names and wanted a count of each surname:-

 

SomeNamesTable

FName, LName

John, Smith

Bill, Smith

Fred, Jones

 

SQL (dodgy) to get these counts

 

SELECT FName, LName, Count(*)

FROM SomeNamesTable

GROUP BY LName

 

Results could be:-

John, Smith, 2

Fred, Jones, 1

 

Or cound be:-

Results could be:-

Bill, Smith, 2

Fred, Jones, 1

 

The FName for the row for Smith could be from any of the original rows for Smith.

 

However in the case of your SQL I am not sure this is an issue as F.ID is the unique key so all the F.* fields should be brought back properly. Generally not best practice through as it is not really standard SQL.

 

Yeah, as you may have noticed, I'm not too experienced at joins.

 

I was just wondering, would it make much difference if I used an inner join rather than left outer join? I've heard inner joins are faster..?

 

INNER JOIN for this would cause a problem. On an inner join you only get a row returned if there is a match on both of the joined tables. Hence if you had a thread with no replies it was missing.

 

While an inner join might be faster, it is pretty useless if it doesn't return the data you need. Like buying a Ferrari as it is faster than your tractor when you only have diesel fuel available ;) .

 

All the best

 

Keith

Link to comment
Share on other sites

Hi

 

It MySQL since ID is the primary key and the other fields are from the same table it doesn't matter. Other flavours of SQL may not deal with it the same way, and as such using this loophole is not really best practice.

 

An inner join is NOT like a right outer join.

 

Imagine 2 tables:-

 

Person

PersonName

Fred

Jo

Bill

Sid

 

Pet

PetName, OwnerName

Fluff, My Anonymous

Bonzo, Bill

Shane, Sid

Sam, Sid

 

Note that one person has no pet and one pet has an owner who is not on the persons table.

 

In a normal join (ie, inner join) you would match records that are in both tables. So:-

 

SELECT * FROM Person JOIN Pet on Person.PersonName = Pet.Owner

 

would bring back:-

 

Bill, Bonzo, Bill

Sid, Shane, Sid

Sid, Sam, Sid

 

In a LEFT OUTER JOIN it will bring back all the records from the left side of the join and if found matching bits from the right, and if not found then nulls:-

 

SELECT * FROM Person LEFT OUTER JOIN Pet on Person.PersonName = Pet.Owner

 

would bring back:-

 

Fred, ,

Jo, ,

Bill, Bonzo, Bill

Sid, Shane, Sid

Sid, Sam, Sid

 

A RIGHT OUTER JOIN is much the same as a LEFT OUTER JOIN except that all the records from the right side of the join and if found matching bits from the left, and if not found then nulls, are brought back:-

 

SELECT * FROM Person RIGHT OUTER JOIN Pet on Person.PersonName = Pet.Owner

 

would bring back:-

 

, Fluff, My Anonymous

Bill, Bonzo, Bill

Sid, Shane, Sid

Sid, Sam, Sid

 

Hope that helps

 

All the best

 

Keith

Link to comment
Share on other sites

Hmm...I feel kind of cheeky for asking you guys, since you've already helped me out tremendously, but I'm having another epic failure, here.

 

You guys obviously know your stuff, so I just thought I'd plead for your help :)

 

No pressure or anything. I just thought it was worth asking.

 

Cheers, Jack.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.