Bookmarks

Yahoo Gmail Google Facebook Delicious Twitter Reddit Stumpleupon Myspace Digg

Search queries

sqldatasource dal, wwwxxxenden, convert raid5 to raid 10 mdadm, apache force chunked, nrao wwwxxx, xxxxxdup, procmail change subject header, wwwXxx not20, Wwwxxx.doks sas, linux raid resync after reboot

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