Member Avatar for Cemils

Hi,

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

Edited by peter_budo because: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks)
  • 2 Contributors
  • 3 Replies
  • 69 Views
  • 7 Hours Discussion Span
  • Latest Post Latest Post by Cemils
Member Avatar for mail2saion

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;
Member Avatar for Cemils

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.