How to select only records with the lowest value for a field of type nvarchar?

I am developing an Access application using Access 2003 with an ADP type file wtih SQL Server 2005.

I currently use the following SQL statement to filter out records from one table named
blGetDocumentAttributes and place the result set into another table named tblGetDocumentAttributesMod.

select t1.* into dbo.tblGetDocumentAttributesMod from tblGetDocumentAttributes t1 left join tblGetDocumentAttributes t2 on t1.Account = t2.Account And t1.DocumentDate < t2.DocumentDate where t2.MailId is Null;"

What additional SQL Statement would you use if there are multipe records
in table tblGetDocumentAttributesMod wtih the same account field value and same
documentdate field value but with different MailID values and you only want to keep the
MailID (type nvarchar) value with the highest value for a particular account.
zimmer9Asked:
Who is Participating?
 
SharathConnect With a Mentor Data EngineerCommented:
I typed the MailID wrong.
SELECT t1.* 
  INTO dbo.tblGetDocumentAttributesMod 
  FROM tblGetDocumentAttributes t1 
       LEFT JOIN tblGetDocumentAttributes t2 
         ON t1.Account = t2.Account 
            AND t1.DocumentDate < t2.DocumentDate 
 WHERE t2.MailId IS NULL 
       AND t1.MailId = (SELECT MAX(t3.MailId)         
                         FROM dbo.tblGetDocumentAttributes AS t3 
                        WHERE t1.Account = t3.Account 
                              AND t1.DocumentDate = t3.DocumentDate); 

Open in new window

0
 
SharathData EngineerCommented:
Try like this.
SELECT * 
  FROM dbo.tblGetDocumentAttributesMod AS t1 
 WHERE MailID = (SELECT MAX(MailID) 
                   FROM dbo.tblGetDocumentAttributesMod AS t2 
                  WHERE t1.Account = t2.Account 
                        AND t1.DocumentDate = t2.DocumentDate)

Open in new window

0
 
zimmer9Author Commented:
That looks good.

What if you want to keep onlly these records in table tblGetDocumentAttributesMod?
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
SharathData EngineerCommented:
try like this.
SELECT t1.* 
  INTO dbo.tblGetDocumentAttributesMod 
  FROM tblGetDocumentAttributes t1 
       LEFT JOIN tblGetDocumentAttributes t2 
         ON t1.Account = t2.Account 
            AND t1.DocumentDate < t2.DocumentDate 
 WHERE t2.MailId IS NULL 
       AND t1.MaiID = (SELECT MAX(MailID) 
                         FROM dbo.tblGetDocumentAttributes AS t3 
                        WHERE t1.Account = t3.Account 
                              AND t1.DocumentDate = t3.DocumentDate);

Open in new window

0
 
zimmer9Author Commented:
I get the following message:

Msg 207, Level 16, State 1, Line 22
Invalid column name 'MaiID'.           --->  AND t1.MaiID = (SELECT MAX(MailID)
0
 
SharathData EngineerCommented:
Missed the table alias.
SELECT t1.* 
  INTO dbo.tblGetDocumentAttributesMod 
  FROM tblGetDocumentAttributes t1 
       LEFT JOIN tblGetDocumentAttributes t2 
         ON t1.Account = t2.Account 
            AND t1.DocumentDate < t2.DocumentDate 
 WHERE t2.MailId IS NULL 
       AND t1.MaiID = (SELECT MAX(t3.MailID) 
                         FROM dbo.tblGetDocumentAttributes AS t3 
                        WHERE t1.Account = t3.Account 
                              AND t1.DocumentDate = t3.DocumentDate);

Open in new window

0
 
zimmer9Author Commented:
SELECT t1.*
  INTO dbo.tblGetDocumentAttributesMod
  FROM tblGetDocumentAttributes t1
       LEFT JOIN tblGetDocumentAttributes t2
         ON t1.Account = t2.Account
            AND t1.DocumentDate < t2.DocumentDate
 WHERE t2.MailId IS NULL
       AND t1.MaiID = (SELECT MAX(t3.MailID)          <------------  Line 22
                         FROM dbo.tblGetDocumentAttributes AS t3
                        WHERE t1.Account = t3.Account
                              AND t1.DocumentDate = t3.DocumentDate);

this still gives me the folllowing error message:

Msg 207, Level 16, State 1, Line 22
Invalid column name 'MaiID'.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.