Results 1 to 4 of 4
  1. #1
    Join Date
    May 2007
    Posts
    24

    Unanswered: Finding the max value of two columns.

    I have tried GREATEST, but it came out with an error.

    I have a time clock table.
    1. date - date
    2. time - time with out time zone
    3. punch_in - boolean

    SELECT MAX(GREATEST( MAX(date),MAX(time))) FROM timeclock;

    I get an error:
    ERROR: GREATEST/LEAST could not convert type time without time zone to date
    SQL state: 42846

    I have also tried

    SELECT MAX(date), MAX(time) FROM timeclock;
    But that did not give me the right result.

    I need the highest date, and the highest time for that date, but it just gives me the max date, and the max time.

  2. #2
    Join Date
    May 2007
    Posts
    24
    Got it.

    SELECT MAX(time), date, punch_in FROM punchin WHERE date=(select MAX(date) FROM punchin) GROUP BY date, punch_in;

  3. #3
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    BTW. Using DATE and TIME as column names is a poor practice (They are reserved words in most database server or programming language...)

    Second; why not combine the date and time in ONE field? You can easily separate the date/time as needed for display, and it's easier to perform date 'arithmetic' with a single field.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  4. #4
    Join Date
    May 2007
    Posts
    24
    Quote Originally Posted by loquin
    BTW. Using DATE and TIME as column names is a poor practice (They are reserved words in most database server or programming language...)

    Second; why not combine the date and time in ONE field? You can easily separate the date/time as needed for display, and it's easier to perform date 'arithmetic' with a single field.
    This was one of the first table I made when I first started programming. Kind of funny to see old codes from back then. Also tells me how much I have improved.

    I am going to combine the two columns, but would need to fix up a lot of codes. Initially I didn't know that the time was saved as part of the Date type, so that was why I made two columns.

    Thanks for the advice

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •