Selecting entries with same value in a field

[elmas156]

Hello everyone,

 

I have a database with strings of information that I am trying to create a report about.  My question is pretty simple, but I'm not sure where to find the information that I need.

 

Basically, am using two DB fields to select the information that I need.  One field is named "entryid" and the other is "stringid."  The "entryid" is always different... basically incrimental (1,2,3,4....), the "stringid" may be the same for several entries.

 

What I am trying to do is to select the entries that has the same "stringid."  So basically, if I have 5 entries that have "entryid" 5, 14, 25, 29, and 41 (just random numbers I selected), and they all have a "stringid" of 5, and then another 3 entries that have "entryid" 6, 13, and 20, with "stringid" of 6, how would I select the entries that have a "stringid" of 5 or 6 so that my results are 5 and 6?  I know that I will have to use a while loop, but not sure of the sql code.

 

Any help is appreciated.

 

[kickstart]

Hi

 

Not quite sure what you mean (you seem to be wanting to find the records with a stringid of 5 or 6 so that you can find their stringid).

 

I assume you want to know the entry ids for records with a stringid of 5 or 6

 

SELECT entryid

FROM SomeTable

WHERE stringid IN (5,6)

 

However I might have misunderstood things.

 

All the best

 

Keith

[elmas156]

well, kind of, but the problem is I don't know what the stringid is beforehand.  This is going to be run from a cron job.  Here's what I have so far:

 

$result = mysql_query("SELECT `entryid`,`stringid` FROM allmsgs WHERE reported = 'n' ORDER BY `messid` ASC") or die (mysql_error());

 

This returns the entryid and stringid  for ALL entries that have not been reported yet (reported = 'n'), which means that I get the following results:

 

entryid      stringid

5                5

13              5

19              5

23              5

24              5

6                6

14              6

22              6

 

What I need to do is select all of the entries from each stringid, whith the higest entryid.  So in this example, I would like for my results to return:

 

entryid = 24, 22 (the most recent entries of "stringid=5" and "stringid=6")

 

I hope this makes more sense.

 

I was thinking that I would use something like:

 

select entryid from `allmsgs` where `entryid`=(select max(`entryid`) from allmsgs);

//maybe put this in a while loop and specify somehow to only select the "max('entryid')" for each "stringid"?

 

Thoughts?

[kickstart]

Hi

 

Ah, right.

 

Something like this:-

 

$result = mysql_query("SELECT stringid,  MAX(entryid) FROM allmsgs WHERE reported = 'n' GROUP BY stringid") or die (mysql_error());

 

That should bring the latest entry id for each string id.

 

If you want more details then you can use a subselect of the above joined back against the table.

 

SELECT entryid, stringid, messid
FROM (SELECT stringid,  MAX(entryid) AS MaxEntryId FROM allmsgs WHERE reported = 'n' GROUP BY stringid) Sub1
INNER JOIN allmsgs
ON Sub1.MaxEntryId = allmsgs.entryid
AND Sub1.stringid = allmsgs.entryid
ORDER BY messid ASC

 

All the best

 

Keith

[elmas156]

Thanks very much! I think I can make it work from there.