Jump to content

[SOLVED] SELECT DISTINCT How to Use?


dabip

Recommended Posts

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. :)

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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 

Link to comment
Share on other sites

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;

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • 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.