GROUP BY ORDER

[jaymc]

When doing a GROUP BY how is it possible to ORDER eached GROUPED results?

 

It appears to ignore the ORDER BY and just return the first row it finds as part of the GROUP BY

[celsoendo]

Can you post your query?

[jaymc]

SELECT t.id, t.subject, t.department, tm.date
		FROM fred.client_tickets t
		INNER JOIN fred.client_ticket_messages tm
		GROUP BY t.id
		ORDER BY tm.date

 

ORDER BY tm.date becomes useless because of the GROUP BY

[Mchl]

Use MAX() or MIN() functions to select latest/earliest date.

[jaymc]

Use MAX() or MIN() functions to select latest/earliest date.

 

That works on the individual field you apply MIN() or MAX() to but it does not pull put the entire row of the MAX(date)

 

The other fields are still random..

[Mchl]

What exactly are you trying to accomplish?

[jaymc]

I want to pull out a row from a table by its primary ID, then join another table which has multiple rows with the same ID as the primary, but not unique

 

table 1:

id-1

id-2

id-3

 

table 2:

ticketID-1

ticketID-1

ticketID-1

ticketID-2

ticketID-3

ticketID-3

 

[fenway]

Is this not the same as this?

[jaymc]

Hmm yes, although trying your example with new fields,database It does not work

 

Heres what I have got so far

 

	SELECT t.id, t.subject, t.department, tm.responder, tm.date
	FROM fred.client_tickets t 
	INNER JOIN
	(
		SELECT date, responder
		FROM fred.client_ticket_messages
		GROUP BY ticketID
	)
	AS tm USING ( date, responder )

 

Error: Unknown column 'date' in from clause

[Mchl]

Is there `date` field in both tables?

 

Also note, that you should avoid using `date` as column name, as it has special meaning for MySQL.