CODEWITHSUNDEEP
mysqlselectgreatest-n-per-group
Dodinas
Dodinas

Reputation:

MySQL Select Statement DISTINCT for Multiple Columns

I am currently trying to construct a somewhat tricky MySQL Select Statement. Here is what I am trying to accomplish:

I have a table like this:

data_table

uniqueID      stringID          subject
  1             144           "My Subject"
  2             144           "My Subject - New"
  3             144           "My Subject - Newest"
  4             211           "Some other column"

Bascially, what I'd like to do is be able to SELECT/GROUP BY the stringID (picture that the stringID is threaded) and not have it duplicated. Furthermore, I'd like to SELECT the most recent stringID row, (which in the example above is uniqueID 3).

Therefore, if I were to query the database, it would return the following (with the most recent uniqueID at the top):

uniqueID   stringID    subject
 4          211        "Some other column"  
 3          144        "My Subject - Newest" //Notice this is the most recent and distinct stringID row, with the proper subject column.

I hope this makes sense. Thank you for you help.

Upvotes: 8

Views: 20197

Answers (5)

dino
dino

Reputation: 1

private void LoadAllFamilyMembers(string relationShip)
        {
            lbFamilyMembers.SelectedIndexChanged -= new EventHandler(lbFamilyMembers_SelectedIndexChanged);
            SqlCommand cmd = new SqlCommand("select familymemberid,name from FamilyMembers where relationship = @relationship", con);
            cmd.Parameters.AddWithValue("@relationship", relationShip);
            DataTable dt = new DataTable();
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            adapter.Fill(dt);
            lbFamilyMembers.DataSource = dt;
            lbFamilyMembers.DisplayMember = "name";
            lbFamilyMembers.ValueMember = "familymemberid";
            lbFamilyMembers.SelectedIndex = -1;
            lbFamilyMembers.SelectedIndexChanged += new EventHandler(lbFamilyMembers_SelectedIndexChanged);
        }

Upvotes: -1

WebMuse
WebMuse

Reputation: 1

I had a similar situation and found a different query. Try this:

SELECT MAX(uniqueID), stringID, subject
 FROM data_table
 GROUP BY stringID

Upvotes: 0

Bill Frederickson
Bill Frederickson

Reputation: 31

SELECT DISTINCT(a),
  ( SELECT DISTINCT(b) ) AS b,
  ( SELECT DISTINCT(c) ) AS c

FROM tblMyTBL

WHERE...
Order By...
Etc.

Upvotes: 3

mechanical_meat
mechanical_meat

Reputation: 169304

Edit: Based on new info provided by the OP in a comment, this would be preferable to relying on uniqueID:

select t.uniqueID
       , t.stringID
       , t.subject
       , t.your_timestamp_col
from   data_table t
       left outer join data_table t2
       on t.stringID = t2.stringID
    and
       t2.your_timestamp_col > t.your_timestamp_col
where  t2.uniqueID is null

If, as lexu mentions in a comment, you are certain that the highest uniqueID value always corresponds with the newest subject, you could do this:

select t.uniqueID
       , t.stringID
       , t.subject
from   data_table t
       left outer join data_table t2
       on t.stringID = t2.stringID
    and
       t2.uniqueID > t.uniqueID
where  t2.uniqueID is null

Which basically means: return to me only those records from data_table where there exists no higher uniqueID value.

Upvotes: 2

Andrew Moore
Andrew Moore

Reputation: 95334

Try the following. It might not be the most efficient query, but it will work:

SELECT uniqueID, stringID, subject
FROM data_table
WHERE uniqueID IN
 (
  SELECT MAX(uniqueID) 
  FROM data_table
  GROUP BY stringID
 )
ORDER BY uniqueID DESC

Upvotes: 9

Related Questions