select - how to find duplicates and gaps in this scenario in mysql -

-

hi have table looks like

----------------------------------------------------------- |  id  |  group_id | source_id | target_id | sortsequence | ----------------------------------------------------------- |  2   |    1      |    2      |   4       |     1        |    ----------------------------------------------------------- |  4   |    1      |    20     |   2       |     1        |    ----------------------------------------------------------- |  5   |    1      |    2      |   14      |     1        |    ----------------------------------------------------------- |  7   |    1      |    2      |   7       |     3        |    ----------------------------------------------------------- |  20  |    2      |    20     |   4       |     3        |    ----------------------------------------------------------- |  21  |    2      |    20     |   4       |     1        |    -----------------------------------------------------------

scenario

there 2 scenarios needs handled.

  1. sortsequence column value should unique against 1 source_id , group_id. example if records having group_id = 1 , source_id = 2 should have sortsequence unique. in above example records having id= , 5 having group_id = 1 , source_id = 2 have same sortsequence 1. faulty record. need find out these records.
  2. if group_id , source_id same. sortsequence columns value should continous. there should no gap. example in above table records having id = 20, 21 having same group_id , source_id , sortsequence value 3 , 1. unique there gap in sortsequence value. need find out these records.

my far effort

i have written query 

select source_id,`group_id`,group_concat(id) children      table  group source_id,   sortsequence,   `group_id`   having count(*) > 1

this query address scenario 1. how handle scenario 2? there way in same query or have write other handle second scenario.

by way query dealing million of records in table performance must good.

got answer tere j comments. following query covers above mentioned both criteria.

 select       source_id, `group_id`, group_concat(id) faultyids           table  group      source_id,group_id   having      count(distinct sortsequence) <> count(sortsequence) or count(sortsequence) <> max(sortsequence) or min(sortsequence) <> 1

may can others.


Comments

Post a Comment

Popular posts from this blog

Hatching array of circles in AutoCAD using c# -

-
i beginner in autocad plugins .i have created array of circle using following code. now, have hatch circles in array .how that??how hatch circles @ once? moreover, how can return hatch method , use hatch objects.sorry language.thanks in advance. [assembly: commandclass(typeof(beamsection.class1))] namespace beamsection { public class class1 { //class1 obj = new class1(); [commandmethod("beamatsupport")] public void addlightweightpolyline() { // current document , database document acdoc = application.documentmanager.mdiactivedocument; database accurdb = acdoc.database; promptpointresult ppr = acdoc.editor.getpoint("\nselect starting point "); var ucs = acdoc.editor.currentusercoordinatesystem; point3d startingpt = ppr.value.transformby(ucs); list<double> radius = new list<double>(); radius.add(0.5); ...
Read more

ios - UITEXTFIELD InputView Uipicker not working in swift -

-
hello trying pop uipickerview view programmatically when user clicks on textfield. have tried doesn't doing anything. isn't working. nothing happening when click textfield class userprofiletableviewcontroller: uitableviewcontroller,uitextfielddelegate,uipickerviewdelegate,uipickerviewdatasource { var itempicker: uipickerview! = uipickerview() @iboutlet weak var gendertxtfield: uitextfield! var gender = ["male","female"] override func viewdidload() { super.viewdidload() gendertxtfield.delegate = self itempicker!.delegate = self itempicker!.datasource = self itempicker!.backgroundcolor = uicolor.blackcolor() self.gendertxtfield.inputview = itempicker } func numberofcomponentsinpickerview(pickerview: uipickerview) -> int{ return 1 } // returns # of rows in each component.. func pickerview(pickerview: uipickerview, numberofrowsincomponent component:...
Read more