MS SQL

MS SQL RSS

SQL question releated to group by

Please support our MS SQL advertiser: Intel Parallel Studio Home

Thread Solved

•

Join Date: Nov 2009

Posts: 3

Reputation: Cemils is an unknown quantity at this point

Solved Threads: 0

Cemils

Offline

Newbie Poster

SQL question releated to group by

32 Days Ago

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:

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
SELECT *, max(created) FROM case_score_t cs WHERE cs.user_id = 1449 GROUP BY cs.case_id;
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

Last edited by peter_budo; 31 Days Ago at 9:40 am. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks)

•

Join Date: Apr 2009

Posts: 220

Reputation: mail2saion is an unknown quantity at this point

Solved Threads: 33

mail2saion mail2saion is offline

Offline

Posting Whiz in Training

32 Days Ago

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:

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
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;
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;

MARK AS SOLVED if its help you.

REGARDS
MCTS - Shawpnendu bikash maloroy
http://shawpnendu.blogspot.com

•

Join Date: Nov 2009

Posts: 3

Reputation: Cemils is an unknown quantity at this point

Solved Threads: 0

Cemils

Offline

Newbie Poster

32 Days Ago

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

•

Join Date: Nov 2009

Posts: 3

Reputation: Cemils is an unknown quantity at this point

Solved Threads: 0

Cemils

Offline

Newbie Poster

31 Days Ago

I found a solution...

This was what i was looking for:
Various solutions to this problem are provided at the following links:
dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html
http://www.artfulsoftware.com/infotr...p?&bw=1179#101
jan.kneschke.de/projects/mysql/groupwise-max