[SOLVED] ORDER BY Help :)

[kickstart]

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

[jackpf]

Hmm...I think I understand.

 

How do you reckon I could get around this then?

 

Thanks for your help.

[kickstart]

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

[fenway]

Hmm...I think I understand.

 

How do you reckon I could get around this then?

 

Thanks for your help.

You'll have to join it back to the main table.

[jackpf]

You beauty kickstart

 

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.

[jackpf]

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

[jackpf]

Whoah, that's weird.

 

Just noticed, if the thread has no replies, it doesn't show up.

 

Wouldn't happen to know how to solve this would you?

Cheers,

jack.

[jackpf]

Oh, how weird. It was the inner joing causing it. Left join, like you originally suggested works fine. Meh, I guess that's the difference

 

Ignore my babbling...

[Ken2k7]

At the bottom, there should be a link to solve the topic.

[fenway]

Except that F.* with a group by will produce meaningless garbage... so it's not "solved", it just looks like it's working.

[jackpf]

Hmm...

I'm not the most knowledgeable of chaps about JOINS and stuff.

 

Would you care to enlgihten me as to how I can improve it?

 

*unsolves topic*

[Ken2k7]

I think the GROUP BY is useless if you're grouping by F.ID unless it's not a PK, in which case ... I rather not get there.

[jackpf]

ID is a primary key. Hence why it's called ID

[fenway]

I think I made a comment to this effect earlier... can you post the "working" query and then I'll modify it accordingly?

[jackpf]

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.

[kickstart]

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

[jackpf]

Oh right, so since ID is primary, it doesn't matter then.

 

And right...so an inner join is like a right outer join?

 

Cheers.

[kickstart]

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

[jackpf]

Ahh I think I've got it now.

 

Thanks for your help.

 

You are a knowledgeable chap

[jackpf]

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.