Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2011-03-10
7
452 Views
Last Modified: 2012-05-11
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.
0
Comment
Question by:zimmer9
  • 4
  • 3
7 Comments
 
LVL 40

Expert Comment

by:Sharath
ID: 35101136
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
 

Author Comment

by:zimmer9
ID: 35101238
That looks good.

What if you want to keep onlly these records in table tblGetDocumentAttributesMod?
0
 
LVL 40

Expert Comment

by:Sharath
ID: 35101289
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:zimmer9
ID: 35101337
I get the following message:

Msg 207, Level 16, State 1, Line 22
Invalid column name 'MaiID'.           --->  AND t1.MaiID = (SELECT MAX(MailID)
0
 
LVL 40

Expert Comment

by:Sharath
ID: 35101357
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
 

Author Comment

by:zimmer9
ID: 35101394
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
 
LVL 40

Accepted Solution

by:
Sharath earned 500 total points
ID: 35101475
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

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Use Distinct with two fields 3 14
awk and Pythagoras? 5 19
SQL Syntax 24 45
Clone table from one server.database to another server.database 24 35
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question