Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9
  1. #1
    New Coder
    Join Date
    Jun 2010
    Location
    Slovenia
    Posts
    39
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Firebird: Only selecting differenct records

    Hi.

    I have 2 tables: Users and Archives. What I need to do is select date from archives, but I only want to select ONE date. For example, there is about 6 records that hold the same date, but I only want to echo ONE of them.

    I hope you can understand what I'm trying to do.


    Thanks
    NULL

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    Use "GROUP BY date_column". Keep in mind this turns your query into a summary query, so if you select any column other than the date_column, you will get a random row. For example, using your example, there are 6 rows with the same date. If you GROUP BY the date, and also select lastname, and age, or whatever, then it's going to be a complete roll of the dice which of those 6 rows you get back.

  • #3
    New Coder
    Join Date
    Jun 2010
    Location
    Slovenia
    Posts
    39
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Fumigator View Post
    Use "GROUP BY date_column". Keep in mind this turns your query into a summary query, so if you select any column other than the date_column, you will get a random row. For example, using your example, there are 6 rows with the same date. If you GROUP BY the date, and also select lastname, and age, or whatever, then it's going to be a complete roll of the dice which of those 6 rows you get back.
    This is great, but now exactly what I wanted. I'm doing this project for my school. Whenever a professor comes to work, they have to check in with their card. That then stores into the archives table as a timestamp. Because most professors don't know how it works, they check in/out every time they leave/enter the building, even though they should only do it when they come and leave for work. What I have to do is from all the entries select the first and the last for each day, subtract the first from last and output how many hours they've been for work each day, for every professor.
    Problem is, that I have no idea how to get each day for each user. I've tried using LIKE, but it doesn't work.
    NULL

  • #4
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    So you're after the MIN and MAX datetime values for each day. You mentioned in your first post this is a "date", but is it actually a "datetime"? There's no way you'd be able to figure out the elapsed time without the time, so I'm going to assume you have a datetime value on each row.

    GROUP BY is your solution, though you'll need to GROUP BY date only, ignoring time, and then SELECT MIN(datetime_column) and MAX(datetime_column), or you can even do:

    Code:
    SELECT TIMEDIFF(MAX(datetime_column), MIN(datetime_column))
    FROM archive_table
    GROUP BY DATE(datetime_column)

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,561
    Thanks
    80
    Thanked 4,496 Times in 4,460 Posts
    Ummm...yes, and no.

    Assuming that he also wants to get the information *per* professor and *per* day, I'd do this:
    Code:
    SELECT professor, DATE(datetime_column) AS theDay TIMEDIFF(MAX(datetime_column), MIN(datetime_column)) AS timeSpent
    FROM archive_table
    GROUP BY professor, DATE(datetime_column)
    ORDER BY BY professor, DATE(datetime_column)
    Though it strikes me as more than strange that there would be only *ONE* datetime_column. I'd expect one for checkin and one for checkout, even given the scenario described.

    Somehow, I figure this has to be homework. And maybe not completely understood homework.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #6
    New Coder
    Join Date
    Jun 2010
    Location
    Slovenia
    Posts
    39
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    Ummm...yes, and no.

    Assuming that he also wants to get the information *per* professor and *per* day, I'd do this:
    Code:
    SELECT professor, DATE(datetime_column) AS theDay TIMEDIFF(MAX(datetime_column), MIN(datetime_column)) AS timeSpent
    FROM archive_table
    GROUP BY professor, DATE(datetime_column)
    ORDER BY BY professor, DATE(datetime_column)
    Though it strikes me as more than strange that there would be only *ONE* datetime_column. I'd expect one for checkin and one for checkout, even given the scenario described.

    Somehow, I figure this has to be homework. And maybe not completely understood homework.
    It's not homework. Homework is never a problem here. It's a project my professor suggested to me, and I accepted. I didn't design the database and I don't know who did, but I'm not allowed to change archive or users table, but I can add new tables.
    However, it seems that my professor didn't exactly told me how it is. The database is actually a Firebird database, but he told me that the only difference between the two is in PHP functions - SQL syntax is the same. But many MySQL functions that I've tried didn't work...
    NULL

  • #7
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    The database is actually a Firebird database, but he told me that the only difference between the two is in PHP functions - SQL syntax is the same. But many MySQL functions that I've tried didn't work...
    So either he doesn't know what he is talking about or he does and you didn't understand him.

    Standard SQL is the same across all databases, but every database has proprietary code so what works in some doesn't work in others. (LIMIT in mysql TOP in mssql for example).

    As you are not using MySQL this thread will be moved to the General Database forum.

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,561
    Thanks
    80
    Thanked 4,496 Times in 4,460 Posts
    Ahhh... The DATE() function is private to MySQL, so far as I know. Access uses DATEVALUE() to mean the same thing, SQL Server has no effective equivalent.

    So you have to look into the Firebird docs to find out if there is an equivalent for DATE() in that DB.

    But fair warning, even the cursory peek at them that I just did tells me that you shouldn't rely on ANYTHING from MySQL applying without checking. Firebird doesn't even support JOINs in UPDATEs, for example. *shudder* Shades of MySQL vintage 1998 or so. Firebird seems to be WAY behind the curve.

    Oh...and clearly the prof has no idea what he is talking about.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #9
    New Coder
    Join Date
    Jun 2010
    Location
    Slovenia
    Posts
    39
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Well, thanks for the help. I tried my best but I'm not in a mood to study a new database system that I will probably never again use.
    NULL


  •  

    Posting Permissions

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