Win a copy of The Business Blockchain this week in the Cloud forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Group by and max

 
shailesh kumar
Ranch Hand
Posts: 36
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am having problem in contructing a query.
I have table where I have to retrive records which have maximum version no only. There may be records with same name but with diffrent versions.
here are the colums:
status 0 means active and 1 means obselete but I want only max version no what ever be the status
Verno/UserName /status /usergroup

1 / S1 / 1 / NL

2 / S1 / 1 / NL

3 / S1 / 0 / NL -- I shud get only this record for S1.

1 / S2 / 1 / NL

2 / S2 / 0 / NL -- I shud get only this record for S2.

1 / S3 / 0 / NL -- I shud get only this record for S3.

here is the query
select max(Verno),UserName ,status,usergroup from status group by UserName ,status,usergroup;
By using this query I am getting all the records with all the versions because i did group by. Is there a way to get only one max version record for all the users.
would appreciate any help...
 
Paul Clapham
Sheriff
Posts: 21542
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Then just omit status from your query entirely.
 
Jeff Albertson
Ranch Hand
Posts: 1780
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm not sure what your are trying to do. I think you are trying
to select all the rows which have a max verno for a given username
and usergroup.

I think a nested select, not a GROUP By query is required:
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic