CODEWITHSUNDEEP
sqloracle-database
SaurabhD
SaurabhD

Reputation: 211

How to find the records when it comes first in the table, i have multiple such records

I have a few columns, I am looking for a SQL query which can give me output with column 4 and 5 where I have value as 1 when value is seen for first time and when it comes again the value will be 0.

Hope I am not making question very complicated.

UniqueNameCount and UniqueDESCCount is the output I am looking at...

Inventory ID    Name    Description UniqueNameCount UniqueDESCCount

Expected results:

Inventory ID    Name    Description UniqueNameCount UniqueDESCCount
IN0001  Item 1  Desc 1  1   1
IN0002  Item 2  Desc 2  1   1
IN0003  Item 3  Desc 1  1   0
IN0004  Item 1  Desc 1  0   0
IN0005  Item 2  Desc 5  0   1
IN0006  Item 2  Desc 5  0   0
IN0007  Item 2  Desc 5  0   0
IN0008  Item 3  Desc 2  0   0
IN0009  Item 3  Desc 2  0   0
IN0010  Item 1  Desc 3  0   1

Upvotes: 0

Views: 44

Answers (2)

MT0
MT0

Reputation: 168361

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE table_name ( Inventory_ID, Name, Description ) AS
          SELECT 'IN0001',  'Item 1',  'Desc 1' FROM DUAL
UNION ALL SELECT 'IN0002',  'Item 2',  'Desc 2' FROM DUAL
UNION ALL SELECT 'IN0003',  'Item 3',  'Desc 1' FROM DUAL
UNION ALL SELECT 'IN0004',  'Item 1',  'Desc 1' FROM DUAL
UNION ALL SELECT 'IN0005',  'Item 2',  'Desc 5' FROM DUAL
UNION ALL SELECT 'IN0006',  'Item 2',  'Desc 5' FROM DUAL
UNION ALL SELECT 'IN0007',  'Item 2',  'Desc 5' FROM DUAL
UNION ALL SELECT 'IN0008',  'Item 3',  'Desc 2' FROM DUAL
UNION ALL SELECT 'IN0009',  'Item 3',  'Desc 2' FROM DUAL
UNION ALL SELECT 'IN0010',  'Item 1',  'Desc 3' FROM DUAL;

Query 1:

SELECT Inventory_ID,
       Name,
       Description,
       CASE
         WHEN LAG( Name ) OVER ( PARTITION BY Name ORDER BY Inventory_ID ) IS NULL
         THEN 1
         ELSE 0
         END AS UniqueNameCount,
       CASE
         WHEN LAG( Description ) OVER ( PARTITION BY Description ORDER BY Inventory_ID ) IS NULL
         THEN 1
         ELSE 0
         END AS UniqueDESCCount
FROM   table_name
ORDER BY Inventory_ID

Results:

| INVENTORY_ID |   NAME | DESCRIPTION | UNIQUENAMECOUNT | UNIQUEDESCCOUNT |
|--------------|--------|-------------|-----------------|-----------------|
|       IN0001 | Item 1 |      Desc 1 |               1 |               1 |
|       IN0002 | Item 2 |      Desc 2 |               1 |               1 |
|       IN0003 | Item 3 |      Desc 1 |               1 |               0 |
|       IN0004 | Item 1 |      Desc 1 |               0 |               0 |
|       IN0005 | Item 2 |      Desc 5 |               0 |               1 |
|       IN0006 | Item 2 |      Desc 5 |               0 |               0 |
|       IN0007 | Item 2 |      Desc 5 |               0 |               0 |
|       IN0008 | Item 3 |      Desc 2 |               0 |               0 |
|       IN0009 | Item 3 |      Desc 2 |               0 |               0 |
|       IN0010 | Item 1 |      Desc 3 |               0 |               1 |

Upvotes: 1

jarlh
jarlh

Reputation: 44796

Use correlated sub-queries to find out if same name or description has been seen for a lower Inventory_ID.

select Inventory_ID, Name, Description,
       case when exists (select 1 from tablename t2
                         where t2.Name = t1.name
                           and t2.Inventory_ID < t1.Inventory_ID) then 0
            else 1
       end as UniqueNameCount,
       case when exists (select 1 from tablename t2
                         where t2.Description= t1.Description
                           and t2.Inventory_ID < t1.Inventory_ID) then 0
            else 1
       end as UniqueDESCCount
from tablename t1

Alternatively:

select t.Inventory_ID, t.Name, t.Description,
       coalesce(nt.cnt,0) as UniqueNameCount,
       coalesce(dt.cnt,0) as UniqueDescriptionCount
from tablename t
    left join (select MIN(Inventory_ID) Inventory_ID, Name, 1 as cnt
               from tablename group by Name) nt
        ON t.name = nt.name and t.Inventory_ID = nt.Inventory_ID
    left join (select MIN(Inventory_ID) Inventory_ID, Description, 1 as cnt
               from tablename group by Description) dt
        ON t.name = dt.Description and t.Inventory_ID = tt.Inventory_ID

Upvotes: 1

Related Questions