CODEWITHSUNDEEP
sqlsql-server
Shmewnix
Shmewnix

Reputation: 1573

How to Select data from SQL Server 2000 using distinct clause on ONLY 1 column

I have a query where I'm trying to select some customer information: name, address, city, state, and zip. I'd like to pull all information and only pull of the records if there is a dupe.

Example of data:

 Invoice_Date First    Last    Addr       City     State     Zip
     11/11/14   Jim     Jones   12 Cedar   alkdjf     TN       29430
     11/11/15   Ralph   Jones   12 Cedar   alkdjf     TN       29430
     11/11/14   Robert  Smith   15 block   slkjdd     TX       10932

What I want to return:

Invoice_Date First    Last    Addr       City     State     Zip
     11/11/15   Ralph   Jones   12 Cedar   alkdjf     TN       29430 (newest Record)
     11/11/14   Robert  Smith   15 block   slkjdd     TX       10932

This is my query that is able to pull ALL customers for the specified dates:

SELECT     
    Invoice_Tb.Invoice_Date, Invoice_Tb.Customer_First_Name, 
    Invoice_Tb.Customer_Last_Name, 
    Invoice_Tb.Customer_Address, Invoice_Tb.City, 
    Invoice_Tb.Customer_State, Invoice_Tb.ZIP_Code
FROM
    Invoice_Tb 
LEFT OUTER JOIN
    Invoice_Detail_Tb ON Invoice_Tb.Store_Number = Invoice_Detail_Tb.Store_Number 
                      AND Invoice_Tb.Invoice_Number = Invoice_Detail_Tb.Invoice_Number 
                      AND Invoice_Tb.Invoice_Date = Invoice_Detail_Tb.Invoice_Date
WHERE     
    (Invoice_Tb.Invoice_Date IN ('11/11/14', '11/11/15')) 
    AND (Invoice_Detail_Tb.Invoice_Detail_Code = 'FSV') 
    AND (LEN(Invoice_Tb.Customer_Address) > 4)
ORDER BY 
    Invoice_Tb.Customer_Address

Now, obviously I can't use Row_Number here, because it's not an option in SQL Server 2000, so that's out.

I've tried Select Distinct - but I'm in need of the other information (first name, last name, etc), and when using select Distinct, it also returns distinct records for First and Last name. I only want 1 record, per address.

How can I return 1 row, for each distinct Address while including first name last name of the MOST recent visit, in this case - 11/11/15.

Upvotes: 0

Views: 903

Answers (4)

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13969

you can do something like this query:

SELECT
    i.last,
    MAX(invoice_date) AS InvoiceDAte,
    MAX(first) AS First,
    MAX(addr) AS addr,
    MAX(zip) AS zip
FROM invoice i
INNER JOIN (SELECT
    last,
    MAX(invoice_date) AS MaxInvoiceDate
FROM invoice
GROUP BY last) a
    ON a.last = i.last
    AND a.MaxInvoiceDate = i.Invoice_date
GROUP BY i.last

Here max(invoice_date) can be multiple in that case we require to take top 1 on that

Upvotes: 0

M T Head
M T Head

Reputation: 1290

create table #SomeTest (Invoice_Number Int, InvoiceDt DateTime, FName Varchar(24), LName Varchar(24), Addr Varchar(24), City Varchar(24), St Varchar(2), Zip Varchar(12) )

insert into #SomeTest (Invoice_Number, InvoiceDt, FName, LName, Addr, City, St, Zip) values (1, '11/11/14', 'Jim','Jones', '12 Cedar', 'alkdjf', 'TN', '29430')
insert into #SomeTest (Invoice_Number, InvoiceDt, FName, LName, Addr, City, St, Zip) values (2, '11/11/15', 'Ralph','Jones', '12 Cedar', 'alkdjf', 'TN', '29430')
insert into #SomeTest (Invoice_Number, InvoiceDt, FName, LName, Addr, City, St, Zip) values (3, '11/11/14', 'Robert','Smith', '15 block', 'slkjdd', 'TX', '10932')


select * from #SomeTest
where Invoice_Number in 
(
select Invoice_Number from 
   (select Invoice_Number = max(Invoice_Number), SupperAddy = Addr + '#' + City + '#' + St + '#' +  Zip from #SomeTest
   group by Addr + '#' + City + '#' + St + '#' +  Zip) X
)

Upvotes: 1

Tab Alleman
Tab Alleman

Reputation: 31785

Here's the "standard" way I always did this in my MsSql 2000 days. In your case, a subquery in the WHERE clause would also work, but I will use the INNER JOIN version of this solution. I employed a little bit of pseudo-code to cut down on my typing. You should be able to figure it out:

SELECT     
    t1.Invoice_Date, t1.Customer_First_Name, 
    t1.Customer_Last_Name, 
    t1.Customer_Address, t1.City, 
    t1.Customer_State, t1.ZIP_Code
FROM
    Invoice_Tb t1
INNER JOIN Invoice_Tb t2
    ON t1.Invoice_Number = (
      SELECT TOP 1 t2.Invoice_Number
      WHERE t2.Customer_Address=t1.Customer_Address
      AND {t2.City,State,Zip=t1.City,State,Zip} --psuedocode
      ORDER BY t2.Invoice_Date DESC
    )

LEFT OUTER JOIN
    Invoice_Detail_Tb ON ...
WHERE     
   ...
ORDER BY 
    Invoice_Tb.Customer_Address

Also note that if any of the "address" fields can be NULL, you will have to handle that possibility when comparing them in the subquery.

Upvotes: 0

David Cram
David Cram

Reputation: 778

If you don't want to use any analytic function (like row_number) then you could do something like this:

--This gives you the most recent date for an address
Select max(invoice_Date) as Invoice_date
Invoice_Tb.Customer_Address
, Invoice_Tb.City
, Invoice_Tb.Customer_State
, Invoice_Tb.ZIP_Code
into #tmp1
from Invoice_Tb 
group by Invoice_Tb.Customer_Address
, Invoice_Tb.City
, Invoice_Tb.Customer_State
, Invoice_Tb.ZIP_Code
GO

--link back to the name for the most recent address
Select a.Invoice_date
,b.Customer_First_Name as [First]
,b.Customer_Last_Name as [Last]
, a.Invoice_Tb.Customer_Address as [Addr]
, a.Invoice_Tb.City
, a.Invoice_Tb.Customer_State as [State]
, a.Invoice_Tb.ZIP_Code as [Zip]
from #tmp1 a
left join Invoice_T b on 
a.Invoicedate = b.Invoicedate
a.Customer_Address = b.Customer_Address
a.City = b.City
a.Customer_State = b.Customer_State
a.ZIP_Code = b.ZIP_Code
GO

Upvotes: 0

Related Questions