How can I select this in SQL?
December 17, 2010 6:27 PM Subscribe
SQLfilter: Help me solve a problem that's driving me crazy. I'm working with someone else's data, and I've got a seemingly insurmountable select problem that no amount of googling has helped me with so far.
I've got a table of several hundred thousand rows that looks like this:
PHRASE_ID PHRASE AUTHOR_ID AUTHOR_NAME TOTAL
14201 as true as 41 ShakeQuarto 8
14201 as true as 43 Sharpham 2
14201 as true as 44 Shirley 4
14201 as true as 45 ThomasGoffe 2
14201 as true as 48 Wilkins 1
14201 as true as 49 Wilson 1
14202 we heare of 2 Apocrypha 1
14202 we heare of 7 Chettle 1
14202 we heare of 13 Fletcher 1
14202 we heare of 22 Jonson 2
14202 we heare of 25 Kyd 1
14202 we heare of 40 ShakeFolio 1
14202 we heare of 41 ShakeQuarto 1
My problem is this: I need to retrieve all the records for each PHRASE_ID, but only if the AUTHOR_NAME with the highest TOTAL is a specific value. I can sort the TOTAL field with ORDER BY, so there's no problem there, but I've been wracking my brains trying to figure out how to do the rest of it.
I've pulled the data into a C# program working with a SQLite add-in, but so far the method I'm trying just runs and runs and hangs up my machine. (Which probably isn't terribly surprising; it's looking for over 13,000 phrases, and my algorithm's most likely a horrible mess.)
Can anyone give me a hand?