Selecing a group of two DISTINCT columns?

jghill

Is it possible to select a group of two distinct columns, and then order by a third column?

Here is my problem....

I have a forum, where each board has a number. Within each board are a number of threads - each thread has it's own number, starting at 0 for the first thread of each board.

Each posting has a timestamp.

Here is a sample of the data column headings....

So - basically, there could be

10 | 1 | 1 | subject here | another message | 20040505191159

10 | 2 | 1 | subject here | another message | 20040505191160

10 | 1 | 2 | | another message | 20040505191160

So - as you can see, I cannot order by timestamp alone - as I would be left with showing postings within threads, rather than just the latest threads that have been posted to.

I cannot order by "thread", as board 10 will have a thread number 1 - as will board 20 have thread number 1

I cannot order by "board" as a board has many threads.

Really - If I could select the rows where "thread" AND "board" were both distinct, and then order by the time, that would be great.

Any ideas, anyone?

PS - MySQL 4.0.18

Joe

leatherback

Hi,

Since you would probably want to seperate message boards in some way, I would also seperate the queries, since it makes life easier:

select distinct board from forums

Then, while you are retrieving those results you query the database for each board:

select * from forums 
  where board=$rpw[board] 
  order by thread time

Or you could try for the whole query:

Select * from forums 
  group by board thread depth 
  order by time

KPIZME

Saying you want to select the distinct cominations of Cols A and B, and then sort on Col C...assuming all three columns are on the same table...I would do something like this.

select distinct (colA||col😎,colC from TABLE order by colC
(the double pipe combines A and B into one field)

Not sure if it will work in mySQl like it does for SQL itself.

jghill

hey, thanks

"select distinct (colA||col😎,colC from TABLE order by colC "

was brilliant, as it allowed me to show which boards had been posted to recently. brilliant

HOWEVER, I also need to display the subjects in this list

simply adding colD (the message subject) to the select, naturally, didnt show the subject, as only the first post in each conversation has the subject.

I can show the subject using two SQL queries - i.e. finding the most recent, then for each, finding the first of that board/thread combo and using that subject -

BUT that wouldnt be very effeficent

Do you know how I could find what you've shown me AND the subject of the first post to each conversation in one SQL query?

joe

KPIZME

assuming you have a key that can relate the two tables, join them in a manner like this.

select distinct (a.colA||a.col😎, a.colC, b.subject
from a.TABLE, b.TABLE
where a.key=b.key
order by a.colC