CODEWITHSUNDEEP
sqloracle-database
zeus
zeus

Reputation: 12975

How to select only first row?

I have this table

Table_A

  link    name
  1       toto 
  2       lulu

Table_B

  link    action   date
  1       OK       01/02/2010 
  1       OK       01/02/2022
  1       OK       01/03/2021
  1       OK       01/02/2025

Then i want to do query like

Select 
  * 
from 
  Table_A, Table_B 
where
  table_A.link (+)= table_b.link and
  table_b.action (+)= 'OK'

the problem is that this query return duplicates

link    name    action    date
1       toto    OK        01/02/2010 
1       toto    OK        01/02/2022
1       toto    OK        01/03/2021
1       toto    OK        01/02/2025
2       lulu

and i want :

link    name    action    date
1       toto    OK        01/02/2025
2       lulu

How to do ?

Upvotes: 1

Views: 2198

Answers (5)

Barbaros Özhan
Barbaros Özhan

Reputation: 65408

You can use row_number() window analytic function

Select link, name, action, "date"
  From
  (
   Select a.link, a.name, b.action, b."date",
          row_number() over (partition by a.link order by b."date" desc ) as rn
     From Table_A a
     Left Join Table_B b 
       on a.link = b.link
      and b.action = 'OK' 
  )
  Where rn = 1;

LINK  NAME  ACTION  date
----  ----- ------  ----------
1     toto  OK      01.02.2025
2     lulu  NULL    NULL

Demo

P.S. Evidently, I don't suggest old comma-seperated join syntax, whereas I do ANSI-92 SQL syntax as in the above query. Another remark that Oracle DB's SQL doesn't require an alias for the subquery, but most of the other DBMS does.

Upvotes: 0

Didier
Didier

Reputation: 1809

1) Use key word GROUP BY

https://www.techonthenet.com/oracle/group_by.php

2) For date good practice is use format: YYYY-MM-dd

link action date
1    OK     2010-02-01

For displaying:

link name action date
1    toto OK     2025-02-01
2    lulu        2023-03-01

insert lulu entry in Table_b

My Table_b:

link  action  date
1     OK      2010-02-01
1     OK      2022-02-01
1     OK      2021-03-01
1     OK      2025-02-01
2             2023-03-01

3) Try this query (Mysql syntax):

SELECT 
  Table_A.link, Table_a.name, action, MAX(date) AS date
FROM 
  Table_A, Table_B 
WHERE
  Table_A.link = Table_B.link
  GROUP BY name
  ORDER BY link;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271003

Use proper, explicit, standard JOIN syntax!

A simple way to write the query:

Select a.*, b.*
from Table_A a left join
     (select b.*, row_number() over (partition by b.link order by b.date desc) as seqnum
      from Table_B b
      where b.action = 'OK'
     ) b
     on b.link = a.link and b.seqnum = 1;

Or use aggregation:

Select a.link, a.name, b.action, max(b.date)
from Table_A a left join
     Table_B b
     on b.link = a.link and b.action = 'OK'
group by a.link, a.name, b.action

Upvotes: 0

user5683823
user5683823

Reputation:

It is generally better to use ANSI join syntax, but following your lead, I will use the Oracle syntax (with the "plus" operator for outer joins).

First, note that you have the "plus" on the wrong side in the first condition in the WHERE clause; you need "fictitious" rows added to table_B, not to table_A.

Then, it is generally best to do the aggregation first, if it depends on just one table. This is what I do in the subquery in the FROM clause below.

Also, date is an Oracle keyword, and it should not be used as a column name. I hope you are not using it that way. For my code, I assumed the column name is dt.

select a.link, a.name, b.action, b.dt
from   table_a a,
       ( select link, 'OK' action, max(dt) dt
         from   table_b
         where  action = 'OK'
         group  by link
       ) b
where  b.link (+) = a.link
;

Upvotes: 1

Mason Wells
Mason Wells

Reputation: 25

You can use the Distinct statement: https://www.techonthenet.com/oracle/distinct.php to select unique data.

Also check out the Group By statement: https://www.techonthenet.com/oracle/group_by.php to group by certain columns, for example to select unique names:

Select 
  * 
from 
  Table_A, Table_B 
where
  table_A.link (+)= table_b.link and
  table_b.action (+)= 'OK'
group by table_A.name

Upvotes: 0

Related Questions