Sql subquery or group problem, results have multiple rows

Question

Post reply

Sql subquery or group problem, results have multiple rows

graham.oriley

Valued Member

Points: 64
More actions
March 22, 2011 at 5:54 am

#236534

Hi,
Problem:
My sql statement is returning a row of each max date_entered
but it also lists all usernames if they vary. I only want the last date_entered row but I also need the username displayed.
Any clever people know how I can change this to achieve the desired
result? ie. only return rows 1,3 and 4, not 2. I've removed some select
fieldnames for easy of viewing.
Results:
URN Supplier_NameNote_Entered_DateNote_Added_ByDoc_Id
93200550 COVERGOLD LTD20110123 14:05:06paul.timmerman@netdocs.co.uk101500000097
93200550 COVERGOLD LTD20110122 14:05:06websupport@netdocs.co.uk101500000097 (I don't want this row!!)
93200548 COVERGOLD LTD20110122 14:03:04websupport@netdocs.co.uk101500000095
93200549 COVERGOLD LTD20110122 14:04:48websupport@netdocs.co.uk101500000096
SQL Statement:
SELECT Tiffindex.URN, Tiffindex.Supplier_Name, Tiffindex.Supplier_No, Tiffindex.Invoice_No, Tiffindex.Invoice_Date, Tiffindex.Credit_No,
Tiffindex.Invoice_Currency, Tiffindex.Invoice_Type, Tiffindex.Net_Value, MAX(Tiffnotes.Date_Entered) AS Note_Entered_Date, Tiffnotes.Username as Note_Added_By, Tiffindex.Doc_Id
FROM Tiffindex LEFT OUTER JOIN
Tiffnotes ON Tiffindex.URN = Tiffnotes.URN
WHERE (Tiffindex.Doc_Status = '3') AND (Tiffindex.Company_No = '01') AND (Tiffindex.Authoriser = 'websupport@netdocs.co.uk') AND (Tiffindex.Page_No = '1')
GROUP BY Tiffindex.URN, Tiffindex.Supplier_Name, Tiffindex.Supplier_No, Tiffindex.Invoice_No, Tiffindex.Invoice_Date, Tiffindex.Credit_No, Tiffindex.Invoice_Currency, Tiffindex.Invoice_Type, Tiffindex.Net_Value, Tiffindex.Doc_Id, Tiffnotes.Username
ORDER BY Tiffindex.Invoice_No, Tiffindex.Invoice_Date
Thanks in advance.
Graham.
March 22, 2011 at 6:32 am

This was removed by the editor as SPAM
March 22, 2011 at 7:56 am

This was removed by the editor as SPAM

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

graham.oriley Valued Member Points: 64 More actions · Answer 1

Table Definitions:

Tiffindex table

URN varchar(20)

Supplier_Namevarchar(50)

Supplier_Novarchar(20)

Invoice_Novarchar(20)

Invoice_Datevarchar(10)

Invoice_Currencyvarchar(10)

Invoice_Typevarchar(10)

Net_Valuedecimal(18, 2)

VAT_Valuedecimal(18, 2)

Gross_Valuedecimal(18, 2)

Credit_Novarchar(20)

Company_Novarchar(2)

Doc_Id varchar(12)

UserId varchar(50)

Doc_Statusvarchar(10)

Page_No varchar(10)

Authoriservarchar(50)

Parked varchar(1)

Approved varchar(1)

tiffpath varchar(255)

media_no varchar(10)

date_importedvarchar(30)

TiffNotes table:

URN varchar(20)

Date_Enteredvarchar(30)

Usernamevarchar(100)

Note text

graham.oriley Valued Member Points: 64 More actions · Answer 2

Sample data in csv:

TiffIndex:

URN,Supplier_Name,Supplier_No,Invoice_No,Invoice_Date,Invoice_Currency,Invoice_Type,Net_Value,VAT_Value,Gross_Value,Credit_No,Company_No,Doc_Id,UserId,Doc_Status,Page_No,Authoriser,Parked,Approved,tiffpath,media_no,date_imported

93200548,COVERGOLD LTD,C16,147214,20100930,GBP,INVOICE,700,122.5,822.5,,1,101500000095,,3,1,websupport@netdocs.co.uk,N,N,\1015\00000095.tif,1,20110322

93200549,COVERGOLD LTD,C16,147225,20100930,GBP,INVOICE,500,87.5,587.5,,1,101500000096,,3,1,websupport@netdocs.co.uk,N,N,\1015\00000096.tif,1,20110322

93200550,COVERGOLD LTD,C16,147196,20100930,GBP,INVOICE,138.66,24.27,162.93,,1,101500000097,,3,1,websupport@netdocs.co.uk,N,N,\1015\00000097.tif,1,20110322

93200550,COVERGOLD LTD,C16,147196,20100930,GBP,INVOICE,138.66,24.27,162.93,,1,101500000097,,3,2,websupport@netdocs.co.uk,N,N,\1015\00000098.tif,1,20110322

93200550,COVERGOLD LTD,C16,147196,20100930,GBP,INVOICE,138.66,24.27,162.93,,1,101500000097,,3,3,websupport@netdocs.co.uk,N,N,\1015\00000099.tif,1,20110322

93200550,COVERGOLD LTD,C16,147196,20100930,GBP,INVOICE,138.66,24.27,162.93,,1,101500000097,,3,4,websupport@netdocs.co.uk,N,N,\1015\00000100.tif,1,20110322

graham.oriley Valued Member Points: 64 More actions · Answer 3

Sample data in csv:

TiffNotes:

URN,Date_Entered,Username,Note

93200548,20110122 14:03:04,websupport@netdocs.co.uk,check supplier

93200549,20110122 14:04:48,websupport@netdocs.co.uk,change values

93200550,20110122 14:05:06,websupport@netdocs.co.uk,check for duplicate

93200550,20110123 14:05:06,paul.timmerman@netdocs.co.uk,process

graham.oriley Valued Member Points: 64 More actions · Answer 4

Wow that's spot on!! That's amazing.

I certainly need to brush up on sql.

Thank you very much.

Cheers

Graham.

webskater SSCarpal Tunnel Points: 4214 More actions · Answer 5

I am no expert - in fact I am about as far from being an expert as it is possible to be.

So, what I have written below may be a load of nonsense.

CREATE TABLE #SomeTable1

(

URN int,

Supplier_Name varchar(255),

Note_Entered_Date datetime,

Note_Added_By varchar(100),

Doc_Id float

)

GO

INSERT INTO #SomeTable1

(URN, Supplier_Name, Note_Entered_Date, Note_Added_By, Doc_ID)

SELECT 93200550, 'COVERGOLD LTD', '20110123 14:05:06', 'paul.timmerman@netdocs.co.uk', 101500000097 UNION ALL

SELECT 93200550, 'COVERGOLD LTD', '20110122 14:05:06', 'websupport@netdocs.co.uk', 101500000097 UNION ALL

SELECT 93200548, 'COVERGOLD LTD', '20110122 14:03:04', 'websupport@netdocs.co.uk', 101500000095 UNION ALL

SELECT 93200549, 'COVERGOLD LTD', '20110122 14:04:48', 'websupport@netdocs.co.uk', 101500000096

GO

Select RowNo, URN, Supplier_Name, Note_Entered_Date, Note_Added_By, Doc_ID

From

(

Select Row_Number()

OVER (Partition by URN Order by Note_Entered_Date Desc ) as RowNo,

URN, Supplier_Name, Note_Entered_Date, Note_Added_By, Doc_ID

from #SomeTable1

) A

WHERE RowNo = 1