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.
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();
Last Comment
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
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
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
and based on the id of the recent notes retrieve the note
ASKER
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......
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......
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...
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...
Try the query below:
Should solve your requirement.
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
ASKER
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.SQLClie
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.SQLClie
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thanks for your help, its working now and i can see now how the code is working :)
Come on.....
What is this!!!!!!
I've posted the solution form the beginning and you give the grades to someone else??
HAVE MERCY!!!!!!
What is this!!!!!!
I've posted the solution form the beginning and you give the grades to someone else??
HAVE MERCY!!!!!!
ASKER
Already on it!
Microsoft SQL Server
Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.
171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
string sSelectSQL = "SELECT fname, sname, company, MAX(notes) FROM Main WHERE sname like '" + snametxt.Text + " %' GROUP BY fname, sname, company ORDER BY fname ASC";