[SOLVED] SELECT DISTINCT How to Use?

[dabip]

Ok, I'm using a distinct to pull records off my db. Well, I have

 

id | id2 | comment | lastdate

------------------------------------------|

1 | 0  | Hello      | 2007-04-12 23:26:39  |

2 | 0  | Sup?      | 2007-04-12 23:27:50  |

1 | 1  | Hi there  | 2007-04-12 23:46:33  |

3 | 0  | Hello      | 2007-04-12 23:47:26  |

2 | 1  | Nutton    | 2007-04-12 23:50:29  |

 

Well, I want to display the newest comment for each id.  But I'm not sure how to call / work with my code to get the latest comment, but post infor for the first one.  So if comment 2,1 had the latest comment, my db would print out comment 2,0 info.  If the next newest comment was 1,1 then it would then print out the 1,0 info after the 2,0 info.

 

I've tried doing this:

 

SELECT DISTINCT `id` FROM `".$fname."` ORDER BY `lastdate` DESC

 

But it seems to order it wrong.

 

Any help is appreciated and I'll be glad to explain more if needed.

[DanDaBeginner]

try this, I hope it works, I havent try it..

 

SELECT * FROM table where id IN (SELECT max(id) FROM table GROUP BY id)

 

tell me if it works....I want to know..hehe!

[bubblegum.anarchy]

SELECT *, max(id2) FROM temp GROUP BY id ORDER BY lastdate;

[DanDaBeginner]

are you sure about that one bubblegum.anarchy? I think it will scatter the data....

[bubblegum.anarchy]

Should give you something like this... but always verify queries on test data!

 

id

id2

comment

lastdate

max(id2)

11Hi there2007-04-12 23:46:331

30Hello2007-04-12 23:47:260

21Nutton2007-04-12 23:50:291

 

sorry DanDaBeginner I did not realise you were asking the question (thought the original poster was)...

 

yeah.. that should be right, group all the records by id and return only the max id2 which is what dabip wants... then order the result by lastdate... pretty sure that is what dabip wants, but I could only understand what dabip was after with your response... still, I am not 100% sure.

[DanDaBeginner]

ok thnx 4 the info... just wait 4 dabip , hope we can resolve this thread..

[bubblegum.anarchy]

SELECT *, max(id2) FROM temp GROUP BY id ORDER BY lastdate DESC;

 

forgot the sort order.

[dabip]

It looks like those should work, but to no avail.   Really, I want to order all of the posts by the posted date, but only get info from the post of id2 = 0.  I've tried one way, but I'd have to check to to see if I already did that number.

 

Ok what I'm really doing is making a forum.  So I'm trying to order them by the last date posted in each "thread". so the id is the thread number and id2 is they post number.  So to get the newest post, I figured I'd just order the posts by the last date, if taht post in thread 203, then it will pull info for 203,0.  If I then post a new thread making it 204, then I want that post to be displayed.

 

This code works:

 

SELECT *

FROM `hi`

ORDER BY `hi`.`lastpost` DESC

LIMIT 0 , 30

 

 

But I don't want to check a list of values to see if I already printed out thread 1 already.  So I was hoping that MYSQL had an efficient way of doing this.

[bubblegum.anarchy]

This seems a little to obvious to be what you are after:

 

SELECT * # SELECT ALL THE COLUMNS

FROM temp # FROM ALL THE RECORDS

WHERE id2 = 0 # WHERE id2 = 0

ORDER BY lastdate DESC # SORTED BY lastdate DESC

 

1

0

Bye

2007-04-14 07:49:31

30Hello2007-04-12 23:47:26

20Hello2007-04-12 23:27:50

[bubblegum.anarchy]

You are right DanDaBeginner, my original query is all wrong... just happened to work.

[dabip]

But that will search only the rows with id2 of 0 for the last posted date.  I want it to search every row, but only spit out rows with id2=0.  Unless that is what it does, which I'm sure it doesn't.  I'm sure its something simple. At least I hope it is.

[bubblegum.anarchy]

saying that you only want rows where the id2 = 0 means that you want a WHERE id2 = 0 in your query...

 

correct me if I am wrong but does not max(id2) = the most recently post in a thread ?

 

 

so for each unique id you want the record with the max(id2) or for each unique id you want the record with the most recent lastdate

 

right?

[dabip]

Aww, I tested it out again, and it is doing everything right, but it seems to order the id2=0's by their lastdate instead of the id2=?'s last date.  How would I fix that?

 

 

Edit: I got it!! Finally.  Thank you a whole bunch for your help.  This is my final usage:

SELECT * , max( id2 ) , max( lastpost )
FROM `hi`
GROUP BY `id`
ORDER BY max( lastpost ) DESC 

[bubblegum.anarchy]

I am not sure what you mean and you haven't answered my original questions dabip... your answers will help me better understand what you are trying to achieve.

 

DanDaBeginner - what do you think of this query? - might be just what dabip needs.

 

SELECT * FROM (SELECT * FROM temp ORDER BY id, lastdate DESC) AS derived GROUP BY id;

[dabip]

Ok, I'll explain what it was doing and what I did to fix it.

 

SELECT * , max( id2 )

FROM `hi`

GROUP BY `id`

ORDER BY max( lastpost ) DESC

LIMIT 0 , 30

 

works.

 

You suggested

SELECT * , max( id2 )

FROM `hi`

GROUP BY `id`

ORDER BY lastpost DESC

LIMIT 0 , 30

 

Which for some reason sorted the results by the order of the id2=0 lastpost row.  Well, I needed the results to be ordered by the newest posted in order, not the order the main threads were posted in.

 

If that makes any sense.  I can't put in words what I have in my head very well... :S

[bubblegum.anarchy]

so what you actually want is simply this:

 

SELECT * FROM (SELECT * FROM temp ORDER BY lastdate DESC) AS derived GROUP BY id;

 

and you will find that DanDaBeginner was right in the first place when suggesting my initial query was erroneous

[dabip]

What does AS derived  do?

 

Will that query do the same as the one I fixed up?

[bubblegum.anarchy]

`AS derived` is an alias for the resulting recordset produced via this `(SELECT * FROM temp ORDER BY lastdate DESC)` - you can have AS any_alias_name_u_like_that_is_not_a_reserved_word

[dabip]

Hmm. WOuld it be faster then the one I posted?

[bubblegum.anarchy]

I am not so sure the query you posted provides the correct records - lets get other opinions.

[DanDaBeginner]

well ,if you want to query to get the latest thread I suggest use Id since it was autoincremented(I guess), so for the newest thread will have the highest Id.. it is more faster than querying the date...