CODEWITHSUNDEEP
sqloracle-database
Luis perez
Luis perez

Reputation: 132

compare in same table by one column

I have a table with these registers

ColumnA     ColumnB
apple       180725
banana      180725
apple       180724

I would like to get only the columnA the registers that appears yesterday and two days ago.

How could I do these in sql. With two tables is easy but how it is in one table I don't know how to make it.

Thanks and sorry for my English!

Upvotes: 0

Views: 46

Answers (2)

Goran Kutlaca
Goran Kutlaca

Reputation: 2024

First to say, answer that user APC gave you is a correct one. You may as well use self join and get a correct result. I'm writing this answer just to suggest you another approach, which I consider to be better in both performance and in principle:

select t1.columna
 from your_table t1
where t1.columnb = to_number(to_char(sysdate,'yymmdd'))-1
  and t1.columna = (select t2.columna 
                      from your_table t2
                     where t1.columna = t2.columna
                       and t2.columnb = to_number(to_char(sysdate,'yymmdd'))-2)

Principle: You join a table only when you need to select some data (column values) from it. Otherwise, it's better to connect it through conditions in where clause, as I wrote it in my answer. The need for that principle may not be as obvious in your example, because of the small amount of data. But, if you would need to join a few more (possibly larger) tables to this query, an additional join would affect the performance more than it does now. Therefore it is better to avoid joins that are not really needed (you do not select their table columns).

Performance of the first query: Link: If your query is heavier with data than presented in your question and you plan to join more tables to this one, it might negatively affect performance of your query

Performance of the second query, one I am suggesting to you: Link: Uses only half the amount of bytes, and lesser is better!

Upvotes: 0

APC
APC

Reputation: 146189

The date is columnB

It's good practice to use a DATE datatype to hold dates. In Oracle we can do arithmetic with dates so there's really no need to store them as numbers, and especially not as numbers without the century.

Anyway, self joins are like any other join, with table aliases to distinguish them:

select t1.columnA
from your_table t1
     inner join your_table t2
     on t1.columnA = t2.columnA 
where t1.columnB = to_number(to_char(sysdate,'yymmdd'))-1
and  t2.columnB = to_number(to_char(sysdate,'yymmdd'))-2

This would also work

and  t2.columnB = t1.columnB - 1

If columnB were a DATE column the WHERE clause would be:

where t1.columnB = trunc(sysdate)-1
and  t2.columnB = trunc(sysdate)-2

Upvotes: 1

Related Questions