SQL query

[warhead2020]

Hi, i got one problem. Here is the problem.

 

-----------------------------

name  | date      | time  | status

----------------------------

one    | 1/1/2008 | 1.12  |  A

two    | 1/1/2008 | 1.13  |  A

one    | 1/1/2008 | 2.20  |  B

two    | 1/1/2008 | 3.00  |  B

one    | 1/1/2008 | 14.20  |  A

one    | 1/1/2008 | 17.20  |  A

 

My goal now is,how to do a query to get name with the lastest status. The result should be, if i want the name with "A" status, the answer will be " one    | 1/1/2008 | 17.20  |  A ".

I hope someone out there can help me. Thanks in advance.

 

[corbin]

Date should be a date column (as in the type).  Also, date is a reserved MySQL word, so you should name it something else to avoid having to use `` all the time.

 

Anyway, once it's date type, you can just do:

 

SELECT * FROM table WHERE status = 'A' ORDER BY DateColName LIMIT 1;

 

 

You would probably want an index on DateColName if you plan on having a lot of rows.

[warhead2020]

Date should be a date column (as in the type).  Also, date is a reserved MySQL word, so you should name it something else to avoid having to use `` all the time.

 

Thanks for ur reply. Actually that is not the real column name. I just want to give an example.

 

 

SELECT * FROM table WHERE status = 'A' ORDER BY DateColName LIMIT 1;

 

refering to ur sql, logically,the result returned is

 

one    | 1/1/2008 | 14.20  |  A

one    | 1/1/2008 | 17.20  |  A

 

my goal is, i want

one    | 1/1/2008 | 17.20  |  A

coz  that is the latest status for ONE. I hope my question is clear. Thanks in advance.

[revraz]

Date is not a reserved word.

 

Date should be a date column (as in the type).  Also, date is a reserved MySQL word, so you should name it something else to avoid having to use `` all the time.

 

 

[corbin]

It's not?  Hrmm, could've sworn it was x.x.  Hehe ;p.

 

 

 

Anyway, sorry, I did not see the time column.  I suggest changing your date and time columns to be 1 column of the datetime type.  That's much more efficient for MySQL and much easier to manage.

 

 

Anyway, this should work:

 

 

SELECT * FROM table ORDER BY date DESC, time DESC LIMIT 1;

 

 

 

If you merged to 1 column:

 

SELECT * FROM table ORDER BY column DESC LIMIT 1;

[warhead2020]

thanks Corbin..really appreciate it.

[fenway]

Date is not a reserved word.

Yes, it is.

[PFMaBiSmAd]

Actually date is a data type and since it is only used when creating or altering columns it is permitted to be used for table and column names without any special treatment (tested.) It does not appear in the reserved words table, but it is listed in the following -

MySQL allows some keywords to be used as unquoted identifiers because many people previously used them. Examples are those in the following list:

 

    *      ACTION

    *      BIT

    *      DATE

... 

[fenway]

It gets even more complicated since it's also a function.  It's just a bad idea to use it.

[corbin]

So it's not a reserved word, but only because people commonly use it.

 

 

So does that paragraph imply that there is specially written code in the MySQL parsing parts to see how "date" is being used?  If so, ewwwwwww.

[fenway]

There's a lot of junk in the server code.

[corbin]

Lame.....  I would rather there not be a performance hit on every single query because some people like to use the column name date.  I guess it would be bad for PR to break a ton of people's code.  lol.