0

I want an output like this table using db2...

NR  TAG1    TAG2    someData1   someData2
=========================================
1   Class1  2015    11665456     862187687
1   Class1  2014    33254665     86221187687
1   Class1  2013    55557321     8687687787
2   Class2  2015    21654765     86822117687
2   Class2  2014    57658776     8632187687
2   Class2  2013    54878575     8682127687

now I trying but failing to write an SQL Code to have a Distinct on the first 3 rows.

When I try to get all data with (*) I get 865 records.

When I only take the first 3 rows and ignore the rest with distinct I get 808 records.

But I dont know how to show the rest of the data.

user3614483
  • 5
  • 2

1 Answers1

0

I presume you want to remove duplicates by considering only first three COLUMNS and display the result. If my understanding is wrong, please explain further.

with cte as 
    (select *, row_number() over(partition by NR, TAG1, TAG2 order by NR, TAG1, TAG2) as row_num from Table_name)
    select * from cte where row_num=1
Chendur
  • 1,099
  • 1
  • 11
  • 23
  • I want exactly what you explained remove duplicates by considering only first three COLUMNS. But not (*) rather only NR, TAG1, TAG2, someData1, someData2 becouse there is a lot more in that table and what cte stand for? – user3614483 Feb 15 '16 at 05:54
  • Look up Common table expression for understanding of CTE. If you do not want all columns replace the * with only columns you need and you are good to go ! – Chendur Feb 15 '16 at 06:03
  • For starters, http://stackoverflow.com/questions/14274942/sql-server-cte-and-recursion-example – Chendur Feb 15 '16 at 06:04
  • Does this works on db2? becouse I´m now lost. In the from clause I have to write my table name? – user3614483 Feb 15 '16 at 06:07
  • Sorry I missed FROM TABLENAME in the query, I have edited it now. And yes it works in DB2 – Chendur Feb 15 '16 at 06:20