36436

Remove distinct records from sql query output

Question:

For my following query:

select sb.case, sb.Trace, sb.Amount, sp.edge, sp.UserId from Budget sb join Sap sp on sb.Trace= sp.Trace where sb.Trace in (select Trace from Sap where sb.UserId in('R5','HB') order by sp.edge desc;

I am getting output as:

case trace amount edge userId 111243557 41432048 95.00 21631781 HB 111243557 40828826 95.00 21631781 R5 111243551 40828819 194.00 21631779 R5 111243545 40828600 71.00 21631773 R5 111243545 41431960 71.00 21631773 HB

How to remove the distinct row from the output. Like here in output edge = 21631779 is distinct and need to be removed. Can anyone please let me know?

Answer1:

If you want output of edges that occur more than once, then you can use window functions:

with cte as ( select sb.case, sb.Trace, sb.Amount, sp.edge, sp.UserId, count(*) over (partition by sp.edge) as cnt from Budget sb join Sap sp on sb.Trace = sp.Trace where sb.Trace in (select Trace from Sap where sb.UserId in ('R5', 'HB')) ) select cte.* from cte where cnt > 1 order by edge desc;

Answer2:

Change the sp.edge for edge at the order by line

order by edge desc;

Recommend

  • Haskell pair two cards
  • How to represent a dependency graph with alternative paths
  • Setting alpha of colorbar in MATLAB R2014b
  • iOS App Crashes when running by itself on device, does not crash when running through Xcode using de
  • Is this possible in SQL?
  • Flot - Is it possible to have a third y-axis or hidden data set?
  • Build object where the constructor is a string
  • hybris's maven doesn't download transitive dependencies
  • Erratic cycle counts on ARM Cortex-M0
  • What is the difference between sap.ui.core.routing.Router.navTo() and sap.m.routing.Targets.display(
  • Python list of String to SQL IN parameter
  • format JSON value with a php function and print result inside a div#id
  • Get last call details from Call Log
  • Linking To V8 Snapshot
  • Should I create an object or work with an array?
  • In explicit LINQ-to-SQL (C#) does order matter?
  • Using a join with three tables when a field might be null
  • Bokeh custom ToolTips {safe} tag displays nothing
  • How to translate SQL queries to cypher in the optimal way?
  • Sequelize mssql: order by primary key and limit
  • Sorting table with just jquery, no other plugins
  • JsonMappingException: Can not deserialize instance of java.lang.Integer out of START_OBJECT token
  • inserting items in list in mongodb document
  • VORA Tables in Zeppelin and Spark shell
  • How to concat Pandas dataframe columns
  • Oracle - Second level subquery cannot see field from main query
  • how to get username into sql trigger when multiple users signed on from asp membership
  • Time out Error in send mail
  • How to skip require in ruby?
  • ApplePay PKPaymentAuthorizationViewController always shows processing
  • Add dynamic data to line chart from mysql database with highcharts
  • Using Sax parsing to edit and write XML in VB6
  • Graphics.CopyFromScreen [Web application] + The handle is invalid
  • Problem deserializing objects from cache on MyBatis 3/Java
  • Using $this when not in object context
  • javaw.exe and eclipse startup problems
  • angularjs unit test when to use $rootScope.$new()
  • Rails 2: use form_for to build a form covering multiple objects of the same class
  • need help with bizarre java.net.HttpURLConnection behavior
  • EntityFramework adding new object to nested object collection