67749

Query to get the most recent record and (in case of ties) with the higher value or percentage by car

Question:

I started the question here ( <a href="https://stackoverflow.com/questions/49553679/query-to-get-the-most-recent-record-and-with-the-higher-value" rel="nofollow">Query to get the most recent record and with the higher value</a>) but I have a small change request.

I will have a column with Percentage and another column with a Char to indicate if is a value or a percentage.

I am trying to do something like this:

select card, service, max(date), case when type = 'v' then MAX(value) KEEP ( dense_rank first order by date desc ) else max(percentage) valor keep ( dense_rank first order by date desc ) end from table group by card, service;

But I am getting ORA-00979: not a GROUP BY expression.

Answer1:

You have not mentioned whether the column type varies for a given card,service pair. Assuming it is same, you should be able to get the result with a nested select, including type in the inner select and group by .

SELECT card ,service ,CASE WHEN type = 'v' THEN value ELSE perc END AS max_result FROM ( SELECT card ,service ,type ,MAX(date_t) AS Date_t ,MAX(value) KEEP ( DENSE_RANK FIRST ORDER BY date_t DESC ) AS value ,MAX(percentage) KEEP ( DENSE_RANK FIRST ORDER BY date_t DESC ) AS perc FROM yourtable GROUP BY card ,service ,type );

<a href="http://sqlfiddle.com/#!4/1b0cd/2" rel="nofollow"><strong>Demo</strong></a>

Recommend

  • Dense_Rank ordering
  • Dense_rank first Oracle to Postgresql convert
  • How to get latest two rows with certain value by date in SQL
  • getting the range of a number column(min/max) if there are missing numbers in between
  • Cannot style mat-tab without ::ng-deep and !important
  • Pyglet uses too much cpu
  • Encrypting credit card details using AngularJS in Braintree
  • How to skip require in ruby?
  • Add dynamic data to line chart from mysql database with highcharts
  • Using Sax parsing to edit and write XML in VB6
  • RxJava debounce by arbitrary value
  • D3 get axis values on zoom event
  • Specifying Castle WCF Integration Facility Endpoint Behavior per Endpoint
  • C: Incompatible pointer type initializing
  • Breeze - Deleted Items nav properties bug
  • Using $this when not in object context
  • Illegal mix of collations for operation for date/time comparison
  • javaw.exe and eclipse startup problems
  • Is there a amazon webstore API for customers?
  • Display Images one by one with next and previous functionality
  • How to make Safari send if-modified-since header?
  • Release, debug version and Authorization Google?
  • ORA-29908: missing primary invocation for ancillary operator
  • How to get next/previous record number?
  • How to pass list parameters for each object using Spring MVC?
  • Circular dependency while pushing http interceptor
  • using conditional logic : check if record exists; if it does, update it, if not, create it
  • Rails 2: use form_for to build a form covering multiple objects of the same class
  • How can I get HTML syntax highlighting in my editor for CakePHP?
  • Hits per day in Google Big Query
  • How do you join a server to an Active Directory (domain)?
  • Angular 2 constructor injection vs direct access
  • How do I configure my settings file to work with unit tests?
  • Setting background image for body element in xhtml (for different monitors and resolutions)
  • need help with bizarre java.net.HttpURLConnection behavior
  • How does Linux kernel interrupt the application?
  • IndexOutOfRangeException on multidimensional array despite using GetLength check
  • JaxB to read class hierarchy
  • Binding checkboxes to object values in AngularJs
  • Conditional In-Line CSS for IE and Others?