duplicate value for null

Question

Hi Expert,

when i am using the below query getting 2 rows of id due else condition is null any way i can replace this

create table table1 (id int, sales int, Saledate date) create table table2 (id int, sales int, invoicedate date) insert into table1 values(1, 23, '2022-12-23'), (2, 24, '2022-12-24'), (3, 25, '2022-12-25') insert into table2 values(1,0, '2022-12-25'), (2, 0,'2022-12-26'), (3, 0,'2022-12-27') Select a.id,a.sales, Case when A.Saledate=MAX(A.Saledate) Over() then A.Saledate else null End MaxSaledate, Case when B.invoicedate=MAX(B.invoicedate) Over() then B.invoicedate else null End Maxinvoicedate from table1 AS A inner join table2 AS B on A.id = B.id drop table table1 ,table2

Answer 1

in short i only wants max data for that particular id no other garbage data

Answer 2

you can try the following: create table table1 (id int, sales int, Saledate date)

create table table2
(id int, sales int, invoicedate date)

insert into table1
values(1, 23, '2022-12-23'),
(2, 24, '2022-12-24'),
(3, 25, '2022-12-25'),
(3, 25, '2022-12-30')

insert into table2
values(1,0, '2022-12-25'),
(2, 0,'2022-12-26'),
(2, 0,'2022-12-30'),
(3, 0,'2022-12-27')



Select a.id,a.sales,
  max(A.Saledate) MaxSaledate,
  max(b.invoicedate) Maxinvoicedate
from table1 AS A
inner join table2 AS B
on A.id = B.id
group by a.id,a.sales

 drop table table1 ,table2

Answer 3

how we can use 2 same date in this query for same id

Select a.id,a.sales, Case when A.Saledate=MAX(A.Saledate) Over() then A.Saledate else null End MaxSaledate, Case when B.invoicedate=MAX(B.invoicedate) Over() then B.invoicedate else null End Maxinvoicedate

from table1 AS A inner join table2 AS B on A.id = B.id

Answer 4

Hi @Shambhu Rai

If I understand correctly, you want to show the highest value of salesdate and invoicedate in the select query. Correct? If not, can you please post what you expect to see?

If my assumption is correct, you can use the following code:

create table table1 
(id int, sales int, Saledate date)

create table table2
(id int, sales int, invoicedate date)

insert into table1
values(1, 23, '2022-12-23'),
(2, 24, '2022-12-24'),
(3, 25, '2022-12-25'),
(3, 25, '2022-12-30')

insert into table2
values(1,0, '2022-12-25'),
(2, 0,'2022-12-26'),
(2, 0,'2022-12-30'),
(3, 0,'2022-12-27')



Select a.id,a.sales,
  max(greatest(A.Saledate, b.invoicedate)) MaxSaledate,
  max(greatest(A.Saledate, b.invoicedate)) Maxinvoicedate
from table1 AS A
inner join table2 AS B
on A.id = B.id
group by a.id,a.sales

 drop table table1 ,table2