• 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: 10763
148
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: 10763
148
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
 
What are you doing? You are supposed to be reading this tiny ad!
the new thread boost feature brings a LOT of attention to your favorite threads
https://coderanch.com/t/674455/Thread-Boost-feature
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!