Bookmarks

Yahoo Gmail Google Facebook Delicious Twitter Reddit Stumpleupon Myspace Digg

Search queries

/proc/kallsyms format, sqldatasource dal, wwwxxxenden, convert raid5 to raid 10 mdadm, apache force chunked, nrao wwwxxx, xxxxxdup, procmail change subject header, wwwXxx not20, Wwwxxx.doks sas

Links

XODOX
Impressum

#1: SQL group select question

Posted on 2005-04-14 00:00:40 by Dave Roe

I have a table with a unique id, a name, a number and a value, for example:

id name numb value
-- ---- ---- -----
1 tom 10 1000
2 dick 10 2000
3 harry 10 3000
4 dick 21 4000
5 harry 21 5000
6 harry 32 6000

As you can see, tom has 1 entry, dick has 2 and harry has 3. I would
like to select 3 records (one for each tom, dick and harry), based on
the associated number (id 1 should be chosen, because there is no other
'tom' record, id 4 should be chosen over id 2, because 21 > 10, and id 6
over ids 3 and 5, because 32 > 10 or 21). The value column is the data
that I am interested in:

id value
-- -----
6 6000
4 4000
1 1000

So far, I have tried:

SELECT id, value FROM test1 GROUP BY name, numb DESC

but this, of course, gives me all 6 records, but grouped so that the
first tom, dick or harry is the record I am interested in.

Can anyone offer any pointers?

/dave

Report this message