This week's book giveaway is in the Cloud forum.
We're giving away four copies of The Business Blockchain and have William Mougayar on-line!
See this thread for details.
Win a copy of The Business Blockchain this week in the Cloud forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

How can I get the exact data use one sql

 
avseq anthoy
Ranch Hand
Posts: 106
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dear all

I have the data as below




If the person has multiple records, I want get the min priority record.
If there are still multiple records after i get the max priority for the persion, I want to get the min Step record.

So in the above example. I want get the data as below.



How can I use one sql statement to achieve it? I use Oracle
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Have a look at the aggregate functions.
 
avseq anthoy
Ranch Hand
Posts: 106
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I had thought about aggregate function.
If the data as below


If the statement as :


The result will be , the is the wrong result


I want the answer is




Roel De Nijs wrote:Have a look at the aggregate functions.
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I forgot you needed the step value as well

So you'll need the aggregate function and a subquery to do what you want. I don't think there's another solution if you want just 1 sql query. Something like this should do the job

Hope it helps!
Kind regards,
Roel
 
Don't get me started about those stupid light bulbs.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic