a query to get id out of table grouped by cat

mistercash60000

Well,

I want to have a query and I can't find the solution, I'm looking on it for about 4hours.

I have a table called tblpoints
columns:
-pointid
-cat (can be man / women) ==>category
-points

Now I want to know the pointid of the table which has the highest points in his category.
This way I should get 2 results: the id with the highest points for man and the id for highest points for women.

I think that it has to be something very simple, but I think I'm thinking too long on it.

Hope somebody can help me.
Thx in advance

devinemke

SELECT cat, MAX(points) FROM tblpoints GROUP BY cat

mistercash60000

That is what I also configurred.

But I need the pointID's not the points itself

devinemke

SELECT pointid, cat, MAX(points) FROM tblpoints GROUP BY cat

mistercash60000

That also doesn't work, he gives then the wrong id's

Don't I need a selfjoin or so?

mrhappiness

either with a self join like

SELECT   t.pointid, t.cat, t.points
FROM
  tablepoints t
  LEFT JOIN tablepoints dummy ON
    t.cat = dummy.cat
    AND t.points < dummy.points
WHERE dummy.pointid IS NULL

or with a subquery if you have a mysql-version that is capable of doing so or you select MAX(points) and cat and read the corresponding pointid afterwards using

WHERE points = <points already read> AND cat = <cat already read>

mistercash60000

Do you know if this is possible to use in Microsoft access that query from mrhappiness

Yes I know not a good solution.
But it's for my mom so she can a use forms and don't have to bother of the stuff behind it.

mrhappiness

Why not just try it?

btw: I always thought ms access is able to do subselects?

mistercash60000

It can, but the problem is that I can't give names to tables

Like tblpoints t
and tblpoints dummy

That doesn't work.

How can I solve that then?

mrhappiness

tblpoints AS t

mistercash60000

It works thanks for helping me out especially you mrhappiness.

One more thing is:

I have a table which contains:
gametype = (short range, middle range, long range)

How can I let it sort also on this?

So that I don't sort on t.cat but also gametype

This way I must get 6rows back.

Is this possible in ms access?

mistercash60000

can somebody help me?