I hope someone can help me creating a sql for the purpose i have.

I have a table named case_score_t with these columns:
id, case_id, user_id, score, created_date

I am interested in picking the rows with the greatest created_date, for a certain user_id.

I now this SQL with give me the result:

SELECT *, max(created) FROM case_score_t cs WHERE cs.user_id = 1449 GROUP BY cs.case_id;

The problem is that i have is that i get an extra column, the max(created) column back.

Is there anyway to do the same query(maybe with subselect's or something), without getting the extra column.

So basically i want something like: SELECt * FROM ...... and is should return the same result.

Thanks in advance.

Edited by peter_budo: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks)

8 Years
Discussion Span
Last Post by Cemils

Your query won't work. Cause you does not specified non aggregated columns in group by clause.

I can't understand what is you requirement. But you can try with below one:

SELECT case_id case, Count(case_id) [case count], max(created) [last update] FROM case_score_t cs WHERE cs.user_id = 1449 GROUP BY cs.case_id;

Thansk for the swift answer.

I am interested in selecting the rows in the table case_score_t with these conditions:
* I am searching for the rows with for that has a certain user_id.
* Because the same case_id can be in the table several times, i want to pick only one row pr. case_id
* But i am only interested in selecting the row with the greatest created_date, for that certain case_id.

So how will i be able to get the case_score rows, for a certain user_id, but only one row pr. case_id(which must be the row with the greatest created date).

Best Regards

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.