Firebird: Only selecting differenct records

[ItsGreg]

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

[Fumigator]

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.

[ItsGreg]

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.

[Fumigator]

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:


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

[Old Pedant]

Ummm...yes, and no.

Assuming that he also wants to get the information *per* professor and *per* day, I'd do this:

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.

[ItsGreg]

Ummm...yes, and no.

Assuming that he also wants to get the information *per* professor and *per* day, I'd do this:

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...

[guelphdad]

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.

[Old Pedant]

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.

[ItsGreg]

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.