Rama Lingam Rama Lingam - 4 months ago 10
SQL Question

DISTINCT is not working for SELECT query in MSSQL

Here i have the invoice details table.
I need the all values without duplicate of

[invoiceno]
.
I am try

select distinct invoiceno,name,addr1,addr2,id from invoice_table;


Result:

invoiceno name addr1 addr2 id
2016718001 Severus Sanpe 7,Hogwards, Sevilee,USA 7451 5
2016718002 Severus Sanpe 7,Hogwards, Sevilee,USA 7451 8
2016718002 Severus Sanpe 7,Hogwards, Sevilee,USA 7451 9


I want the result:

invoiceno name addr1 addr2 id
2016718001 Severus Sanpe 7,Hogwards, Sevilee,USA 7451 5
2016718002 Severus Sanpe 7,Hogwards, Sevilee,USA 7451 8


It's working fine without
id
in
SELECT
. But i need that also.
How to do this?

Answer

You probably want a GROUP BY query with MIN aggregated function:

select
  invoiceno, name, addr1, addr2, min(id) as id
from
  invoice_table
group by
  invoiceno, name, addr1, addr2

or if the same invoice can have multiple names and/or addresses, you can use something like this:

select t.invoiceno, t.name, t.addr1, t.addr2, t.id
from
  invoice_table t inner join (select invoiceno, min(id) as min_id) m
  on t.invoiceno=m.invoiceno and t.id=m.min_id

this will return the first id (the one with the lowest value) for every invoiceno.