0

Table1 

ID  fromdate   todate

001 23/02/2012 27/02/2012
002 23/02/2012 27/02/2012
003 28/02/2012 09/09/2013
....

Condition: I don't want to show duplicate values, so from date validate with other from date, if it's equal then it should not repeat, to date validate with other to date, if it's equal then to date should not repeat,

Expected Output

ID  fromdate   todate

001 23/02/2012 27/02/2012
003 28/02/2012 09/09/2013
....

From the above output, 002 is not showing because fromdate and todate is same with 001, so from and to date comparison should not repeat.

How to write a query for the above condition?

JetJack
  • 978
  • 8
  • 26
  • 51
  • from and to date comparision should not repeat.... – JetJack Feb 27 '12 at 08:39
  • you should explain more clearly what you mean by duplicate. may be you need to add more sample data and expected output. you should also add what query you have tried. – dejjub-AIS Feb 27 '12 at 08:40
  • 1
    MicSim and my queries are same based on what sample output you gave. They are the answers to be accepted. If they are not the answers then you need to provide more input (sample data) and output (expected output) – dejjub-AIS Feb 27 '12 at 08:57
  • Sounds like you want to avoid overlapping periods. Are you looking for a database constraint (i.e. stop bad data getting into the database) or a query to scrub data (i.e. to cleanse bad data already in the database)? – onedaywhen Feb 27 '12 at 10:29

4 Answers4

4

Try this:

SELECT MIN(ID), fromDate, toDate FROM table
GROUP BY fromDate, toDate
MicSim
  • 26,265
  • 16
  • 90
  • 133
  • what would happen here if you left out the MIN? – Aaron Anodide Feb 27 '12 at 08:45
  • @MicSim: This will work for the test data but not for the OP's requirement. If **one** of both dates is not unique, it should not be repeated. Change one of both `27/02/2012` to `26/02/2012` and you get three records instead of the required two. – Tim Schmelter Feb 27 '12 at 09:01
  • @TimSchmelter can you edit (add more sample data) if you undertood the question well? – dejjub-AIS Feb 27 '12 at 10:05
  • @NoobASThreeDeveloper: He wants to avoid that fromdate is repeating in another record as well as todate. A simple group by both would avoid repeating records where both dates are the same but not where one of both is the same but the other not. Quote: "I don't want to show duplicate values, so from date validate with other from date, if it's equal then it should not repeat, to date validate with other to date, if it's equal then to date should not repeat". So replace the second `27/02/2012` with `26/02/2012` and ensure that 2 records are returned instead of three because fromdate isn't unique. – Tim Schmelter Feb 27 '12 at 10:33
  • I've deleted my cte-row_number approach since OP is using sql-server 2000. – Tim Schmelter Feb 27 '12 at 10:35
2

A ROW_NUMBER solution would be easier to write but following works with SQL Server 2000.

In a nutshell

  • get all lowest fromdates in a subselect
  • get all lowest todates in a subselect for the remaining lowest fromdates by JOINING with our previous subselect.
  • get all records from the actual table for the remaining todates by JOINING with our previous subselect.

SQL Statement

SELECT  Table1.*
FROM    Table1
        INNER JOIN (
          SELECT  ID = MIN(Table1.ID), todate
          FROM    Table1
                  INNER JOIN (
                    SELECT  ID = MIN(ID), fromdate
                    FROM    Table1
                    GROUP BY
                            fromdate
                  ) UniqueFromDates ON UniqueFromDates.ID = Table1.ID
          GROUP BY
                  todate        
        ) UniqueToDates ON UniqueToDates.ID = Table1.ID
Lieven Keersmaekers
  • 57,207
  • 13
  • 112
  • 146
1

from your comments if "from and to date comparison should not repeat" is your requirement, its very simple

In case there are multiple "ID"s with same fromdata and todate and you want to show the FIRST ID then use this

  select MIN(id) id,fromdata, todate from tablename group by fromdata, todate

In case there are multiple "ID"s with same fromdata and todate and you want to show the LAST ID then use this

  select MAX(id) id,fromdata, todate from tablename group by fromdata, todate
dejjub-AIS
  • 1,501
  • 2
  • 24
  • 50
  • This will return 3 records but OP wants 2. – Tim Schmelter Feb 27 '12 at 08:50
  • add more sample data then we can give the correct query instead of queries based on assumptions – dejjub-AIS Feb 27 '12 at 08:50
  • @TimSchmelter it will return only two. – dejjub-AIS Feb 27 '12 at 08:54
  • @Noob: Change one of both 27/02/2012 to 26/02/2012 and you get three records instead of the required two. – Tim Schmelter Feb 27 '12 at 09:23
  • 1
    I leave it to the asker. I have been asked personally question like this many times. They first reject saying "this will result like this and like that" explaining various scenarios, then when I ask "forget the SQL and Queries, tell me how you pick manually based on your conditions" then they realize "oops this is not HOW I want" and explain it more clearly. This often happens when they mixup things with what they want and how they want – dejjub-AIS Feb 27 '12 at 10:00
0

You can also use PARTITION BY like ↓

SELECT id,
       fromdate,
       todate
FROM   (SELECT id,
               fromdate,
               todate,
               Row_number() OVER (PARTITION BY fromdate, todate ORDER BY id) AS
               row
        FROM   table) t
WHERE  t.row = 1
shenhengbin
  • 4,236
  • 1
  • 24
  • 33