sorting/grouping
I have a very simple table. commentid, commentref (each field is an INT) Each record is a comment... commentid is the ID (each new record is a higher #).. and the commentref field if the story the comment refers to. I want to be able to list the stories in order from most recent comment onward. I've tried select commentid, commentref from comments GROUP BY commentref ORDER by commentid DESC; However, the results are NOT showing me the stories in order from most active comments onward... Maybe too much soda and sugar - but any thoughts? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: sorting/grouping
try select commentid, commentref from comments ORDER by commentref ,commentid DESC; -Original Message- From: Cummings, Shawn (GNAPs) [mailto:[EMAIL PROTECTED] Sent: Thursday, October 02, 2003 2:37 PM To: [EMAIL PROTECTED] Subject: sorting/grouping I have a very simple table. commentid, commentref (each field is an INT) Each record is a comment... commentid is the ID (each new record is a higher #).. and the commentref field if the story the comment refers to. I want to be able to list the stories in order from most recent comment onward. I've tried select commentid, commentref from comments GROUP BY commentref ORDER by commentid DESC; However, the results are NOT showing me the stories in order from most active comments onward... Maybe too much soda and sugar - but any thoughts? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: sorting/grouping
This doesn't seem to be making sure that the newest commentid is the result... Seems to be random. At 09:11 AM 10/6/2003 -0400, you wrote: try select commentid, commentref from comments ORDER by commentref ,commentid DESC; -Original Message- From: Cummings, Shawn (GNAPs) [mailto:[EMAIL PROTECTED] Sent: Thursday, October 02, 2003 2:37 PM To: [EMAIL PROTECTED] Subject: sorting/grouping I have a very simple table. commentid, commentref (each field is an INT) Each record is a comment... commentid is the ID (each new record is a higher #).. and the commentref field if the story the comment refers to. I want to be able to list the stories in order from most recent comment onward. I've tried select commentid, commentref from comments GROUP BY commentref ORDER by commentid DESC; However, the results are NOT showing me the stories in order from most active comments onward... Maybe too much soda and sugar - but any thoughts? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Shawn Cummings Engineering Project Manager Global NAPs 10 Merrymount Rd Quincy, MA 02169 Desk 617-507-5150 VoIP 617-507-3550 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: sorting/grouping
For instance; mysql select commentid, commentref from comments GROUP BY commentref ORDER by commentref DESC, commentid; +---++ | commentid | commentref | +---++ |80 |188 | |73 |187 | |76 |185 | |56 |181 | |59 |180 | |60 |179 | |50 |169 | ... You can see for record 181 that 56 is the highest number after 181 is grouped. However this is not true; mysql select commentid, commentref from comments WHERE commentref=181; +---++ | commentid | commentref | +---++ |56 |181 | |57 |181 | |79 |181 | +---++ You can see above that id(79) At 09:11 AM 10/6/2003 -0400, Dan Greene wrote: try select commentid, commentref from comments ORDER by commentref ,commentid DESC; -Original Message- From: Cummings, Shawn (GNAPs) [mailto:[EMAIL PROTECTED] Sent: Thursday, October 02, 2003 2:37 PM To: [EMAIL PROTECTED] Subject: sorting/grouping I have a very simple table. commentid, commentref (each field is an INT) Each record is a comment... commentid is the ID (each new record is a higher #).. and the commentref field if the story the comment refers to. I want to be able to list the stories in order from most recent comment onward. I've tried select commentid, commentref from comments GROUP BY commentref ORDER by commentid DESC; However, the results are NOT showing me the stories in order from most active comments onward... Maybe too much soda and sugar - but any thoughts? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Shawn Cummings Engineering Project Manager Global NAPs 10 Merrymount Rd Quincy, MA 02169 Desk 617-507-5150 VoIP 617-507-3550 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sorting/grouping
Have you tried: select commentref,max(commentid) from comments group by commentref Regards, Joseph Bueno Cummings, Shawn (GNAPs) wrote: This doesn't seem to be making sure that the newest commentid is the result... Seems to be random. At 09:11 AM 10/6/2003 -0400, you wrote: try select commentid, commentref from comments ORDER by commentref ,commentid DESC; -Original Message- From: Cummings, Shawn (GNAPs) [mailto:[EMAIL PROTECTED] Sent: Thursday, October 02, 2003 2:37 PM To: [EMAIL PROTECTED] Subject: sorting/grouping I have a very simple table. commentid, commentref (each field is an INT) Each record is a comment... commentid is the ID (each new record is a higher #).. and the commentref field if the story the comment refers to. I want to be able to list the stories in order from most recent comment onward. I've tried select commentid, commentref from comments GROUP BY commentref ORDER by commentid DESC; However, the results are NOT showing me the stories in order from most active comments onward... Maybe too much soda and sugar - but any thoughts? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: sorting/grouping
At 12:01 -0400 10/6/03, Cummings, Shawn (GNAPs) wrote: This doesn't seem to be making sure that the newest commentid is the result... Seems to be random. Your requirements are unclear. If you want output sorted by descending order of commentid, then your original query should produce that. In that case, however, the GROUP BY seems irrelevant. Given that you've included a GROUP BY, it appears that what you may want is output grouped by commentref, with rows for a given commentref value sorted by descending commentid value. In that case, the query suggested by Dan appears to me to be what you want. Yet you state that neither query produces the correct result. Perhaps you could clarify what you want. Please show a sample result, indicate why it is not correct, and indicate what the correct result would look like. You might also indicate what most active in your original description has to do with the problem to be solved. There is nothing in your table structure that appears to relate to assessment of activity. At 09:11 AM 10/6/2003 -0400, you wrote: try select commentid, commentref from comments ORDER by commentref ,commentid DESC; -Original Message- From: Cummings, Shawn (GNAPs) [mailto:[EMAIL PROTECTED] Sent: Thursday, October 02, 2003 2:37 PM To: [EMAIL PROTECTED] Subject: sorting/grouping I have a very simple table. commentid, commentref (each field is an INT) Each record is a comment... commentid is the ID (each new record is a higher #).. and the commentref field if the story the comment refers to. I want to be able to list the stories in order from most recent comment onward. I've tried select commentid, commentref from comments GROUP BY commentref ORDER by commentid DESC; However, the results are NOT showing me the stories in order from most active comments onward... Maybe too much soda and sugar - but any thoughts? -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: sorting/grouping
At 12:09 -0400 10/6/03, Cummings, Shawn (GNAPs) wrote: For instance; mysql select commentid, commentref from comments GROUP BY commentref ORDER by commentref DESC, commentid; +---++ | commentid | commentref | +---++ |80 |188 | |73 |187 | |76 |185 | |56 |181 | |59 |180 | |60 |179 | |50 |169 | ... You can see for record 181 that 56 is the highest number after 181 is grouped. If you include a GROUP BY in your query the way you do without including an aggregate function in the column output list, the query will boil down the output to include one row per commentref value, and choose in an indeterminate fashion what values to display in the other output columns. That's why you're not getting the results you want. The query using max() suggested by Joseph Bueno looks closer to what you should be using, as far as I can tell. However this is not true; mysql select commentid, commentref from comments WHERE commentref=181; +---++ | commentid | commentref | +---++ |56 |181 | |57 |181 | |79 |181 | +---++ You can see above that id(79) -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: sorting/grouping
I think you want just ORDER BY commentid DESC .The previous answer orders by commentref first, which is not what I think you want. Maybe you want ORDER BY commentid, commentref DESC This doesn't seem to be making sure that the newest commentid is the result... Seems to be random. At 09:11 AM 10/6/2003 -0400, you wrote: try select commentid, commentref from comments ORDER by commentref ,commentid DESC; -Original Message- From: Cummings, Shawn (GNAPs) [mailto:[EMAIL PROTECTED] Sent: Thursday, October 02, 2003 2:37 PM To: [EMAIL PROTECTED] Subject: sorting/grouping I have a very simple table. commentid, commentref (each field is an INT) Each record is a comment... commentid is the ID (each new record is a higher #).. and the commentref field if the story the comment refers to. I want to be able to list the stories in order from most recent comment onward. I've tried select commentid, commentref from comments GROUP BY commentref ORDER by commentid DESC; However, the results are NOT showing me the stories in order from most active comments onward... Maybe too much soda and sugar - but any thoughts? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Shawn Cummings Engineering Project Manager Global NAPs 10 Merrymount Rd Quincy, MA 02169 Desk 617-507-5150 VoIP 617-507-3550 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
