Avatar of ttphil
ttphil asked on

Select Distinct certain fields using SQLDataReader

Hi,

I am trying to figure out how to pull in data from an SQL DB without duplicate names.
The fields im using are fname, sname, company and notes.
I use SELECT DISTINCT to  pull in data to a datagrid where sname is in a textbox. I only want it to return 1 instance of each fname, sname and company and to return only the most recent notes instance. There can be many rows with the same fname, sname and company, but at the moment, its including the notes too so I get more than one result.

How might i go about doing this?

Hope this is clear, ive posted my code below.


string sSelectSQL = "SELECT DISTINCT fname, sname, company, notes FROM Main WHERE sname like '" + snametxt.Text + " %' ORDER BY fname ASC";
                    SqlCommand oCommand = new SqlCommand(sSelectSQL, oConnection);
                    oConnection.Open();
                    oReader = oCommand.ExecuteReader();
 
                    
                    DataSet ds = new DataSet();
                    DataTable dt = new DataTable("Table1");
 
                    ds.Tables.Add(dt);
                    ds.Load(oReader, LoadOption.PreserveChanges, ds.Tables[0]);
                    datagridview1.DataSource = ds.Tables[0];
 
                    oReader.Close();
                    oConnection.Close();

Open in new window

.NET ProgrammingMicrosoft SQL Server

Avatar of undefined
Last Comment
ttphil

8/22/2022 - Mon
reb73

Change the first line of code

string sSelectSQL = "SELECT fname, sname, company, MAX(notes) FROM Main WHERE sname like '" + snametxt.Text + " %' GROUP BY fname, sname, company ORDER BY fname ASC";
Dimitris

I assume that the most recent Notes will be the notes where the ID of the table is greater.

If you have a date field then put the max on the date field

SO

SELECT t.fname, t.sname,t.company, Main.Notes
FROM Main
INNER JOIN (
                    SELECT fname, sname, company, MAX(ID) as LatestID
                    FROM   Main
                    WHERE sname like '" + snametxt.Text + " %'
                    GROUP BY fname, sname, company) t
      ON t.ID = Main.ID
ORDER BY t.fName ASC
Dimitris

You have to do the internal select in order to get the most recent notes (and not the max of the text)
and based on the id of the recent notes retrieve the note
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER
ttphil

Hi Guys,

Thanks for your comments, im now using the code below:

string sSelectSQL = "SELECT fname, sname, company, MAX(ID) FROM Main WHERE sname like '" + snametxt.Text + " %' GROUP BY fname, sname, company ORDER BY fname ASC";

It brings up only the most recent record and groups it all together just the way i want. But, i also need it to display the notes and a building field (forgot about that one earlier!!) Its already selecting the most recent record but if i add it to the SELECT after company it errors saying its not in the group by......
Dimitris

SELECT t.fname, t.sname,t.company, Main.Notes , Main.BuildingField
FROM Main
INNER JOIN (
                    SELECT fname, sname, company, MAX(ID) as LatestID
                    FROM   Main
                    WHERE sname like '" + snametxt.Text + " %'
                    GROUP BY fname, sname, company) t
      ON t.ID = Main.ID
ORDER BY t.fName ASC

this one brings name, company, notes you can add the building field from Main

You have to use the entire select and not the inner select only...
Raja Jegan R

Try the query below:

Should solve your requirement.
select fname, sname, company, notes
from (
SELECT fname, sname, company, notes, 
row_number() over (partition by fname, sname, company order by fname asc) rnum
FROM Main 
WHERE sname like '" + snametxt.Text + " %' ) temp
where rnum = 1

Open in new window

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
ttphil

Sorry im still struggling a bit with this. I have the code below:

string sSelectSQL = "SELECT t.fname, t.sname, t.company, Main.notes FROM Main INNER JOIN (SELECT fname, sname, company, MAX(ID) as LatestID FROM Main WHERE sname like '" + snametxt.Text + "%' GROUP BY fname, sname, company)t ON t.ID = Main.ID ORDER BY t.fname ASC";

But this errors with 'problem populating table:[System.Data.SQLClient.SqlException: invalid column name 'id'.'
ASKER CERTIFIED SOLUTION
reb73

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
ttphil

Thanks for your help, its working now and i can see now how the code is working :)
Dimitris

Come on.....
What is this!!!!!!
I've posted the solution form the beginning and you give the grades to someone else??

HAVE MERCY!!!!!!
 
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER
ttphil

Already on it!