Jump to content


Photo

2 Field Max


  • Please log in to reply
3 replies to this topic

#1 justravis

justravis
  • Members
  • PipPipPip
  • Advanced Member
  • 115 posts
  • LocationSan Diego, CA

Posted 15 January 2003 - 01:11 AM

I have a table of years and numbers that refer to seasons in another table.

It looks something like this:
yr number
2002 2
2003 1

I need to create a query that selects the latest yrnumber COMBO. In this case, it would be 2003, 1.

Here is what I have tried:

SELECT MAX(yr), MAX(number) FROM issue
returned 2003, 2

SELECT MAX(yr), MAX(number) FROM issue WHERE yr=MAX(yr)

SELECT yr, number FROM issue WHERE yr=MAX(yr) AND number=MAX(number)

Both returned error \"Invalid use of group function\"

Any other ideas? I know mysql does not support sub-queries, so do I have to use two?

Sorry to bug you on a seemingly trivial issue, but I TRY to always learn how to do things the in the best and most efficient manner.
PHP version 5.2.6 | MySQL version 5.0.51a-community

#2 Uranium-235

Uranium-235
  • Members
  • PipPip
  • Member
  • 21 posts

Posted 15 January 2003 - 01:32 AM

I\'m taking a shot in the dark here but why not...


SELECT yr, number FROM issue WHERE (MAX(yr) AND MAX(number))


I always like to put () around any AND/OR statements just to be safe. I think it will work without it though
Random Quote:[br][br][url=http://www.paintbug.com/scripts/quote_source.phps]

#3 effigy

effigy
  • Staff Alumni
  • Advanced Member
  • 3,600 posts
  • LocationIL

Posted 15 January 2003 - 01:51 AM

or:

SELECT yr, number FROM issue ORDER BY yr DESC LIMIT 1
Regexp | Unicode Article | Letter Database
/\A(e)?((1)?ff(?:(?:ig)?y)?|f(?:ig)?)\z/

#4 justravis

justravis
  • Members
  • PipPipPip
  • Advanced Member
  • 115 posts
  • LocationSan Diego, CA

Posted 15 January 2003 - 02:37 AM

Why didnt\'t I think of that? first sugestion didnt work..2nd one did...thanx
PHP version 5.2.6 | MySQL version 5.0.51a-community




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users