LinuxQuestions.org
LinuxAnswers - the LQ Linux tutorial section.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - General
User Name
Password
Linux - General This Linux forum is for general Linux questions and discussion.
If it is Linux Related and doesn't seem to fit in any other forum then this is the place.

Notices

Reply
 
Search this Thread
Old 11-19-2007, 11:29 AM   #1
clau_bolson
Member
 
Registered: Nov 2003
Location: Argentina
Distribution: Debian Sarge
Posts: 52

Rep: Reputation: 15
Help on complex mysql query


I hope someone can help me with this:
I use mysql 3.23.58

I have this table, named "complains":

id - number - date - who - data

number can be repeated.
I want to get for each number the record that has the most recent date.

So I did:

SELECT id, number, max(date), who FROM complains GROUP BY number

And I get the latest date BUT id and who are those of the first record where number appears

How can I issue this query so that it shows the right result?

Thanks.
 
Old 11-19-2007, 11:55 AM   #2
FraGGod
Member
 
Registered: Jun 2007
Location: Yekaterinburg, RU
Distribution: gentoo
Posts: 59

Rep: Reputation: 16
"SELECT id, number, date, who FROM complains GROUP BY number ORDER BY date DESC" should probably do the trick.
 
Old 11-19-2007, 12:16 PM   #3
clau_bolson
Member
 
Registered: Nov 2003
Location: Argentina
Distribution: Debian Sarge
Posts: 52

Original Poster
Rep: Reputation: 15
Thanks FraGGod for your answer.

It doesn't.

It just shows the whole first record where number appears.
 
Old 11-19-2007, 01:13 PM   #4
FraGGod
Member
 
Registered: Jun 2007
Location: Yekaterinburg, RU
Distribution: gentoo
Posts: 59

Rep: Reputation: 16
Indeed it doesn't, my oversight.

SELECT id, date, number, who FROM complains AS c WHERE date = (SELECT MAX(date) FROM complains WHERE number = c.number)
I would've suggested solution like this but I doubt it'll work well (if it'll work at all) with MySQL 3 or 4.

So I guess you can use dirty cheat like this:
SELECT c1.id, c1.number, c1.date, c1.who FROM complains AS c1 LEFT JOIN complains AS c2 ON c1.number = c2.number AND c2.date > c1.date WHERE ISNULL(c2.date)
 
  


Reply

Tags
mysql


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
mysql use output of one query in another query secretlydead Programming 2 11-19-2007 02:25 AM
help with mysql query: return nth rows in query hawarden Programming 2 07-31-2006 07:36 PM
Mysql Error: Lost Connection to Mysql during query ramnath Programming 5 11-18-2003 01:27 PM
mysql query !!! hitesh_linux Linux - General 1 02-03-2003 04:36 AM
mysql and complex data types... gluon Programming 2 10-29-2001 01:50 PM


All times are GMT -5. The time now is 08:58 PM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration