Rank within a category (Sql)

[Helge's]

I have this sql that give me the sales rank from the highest to the
lowest. I have several categories and need to rank within each
category. h1.Cat is the categoryfield.

SELECT h1.Cat, h1.Oms, (SELECT COUNT(*)
FROM tblOms AS h2
WHERE h2.oms >= h1.oms) AS Rank
FROM tblOms AS h1
ORDER BY h1.Oms DESC;

Would it be like GROUP by h1.Cat some place.

[pietlinden]

Helge's wrote:

I have this sql that give me the sales rank from the highest to the
lowest. I have several categories and need to rank within each
category. h1.Cat is the categoryfield.

SELECT h1.Cat, h1.Oms, (SELECT COUNT(*)
FROM tblOms AS h2
WHERE h2.oms >= h1.oms) AS Rank
FROM tblOms AS h1
ORDER BY h1.Oms DESC;

Would it be like GROUP by h1.Cat some place.

so where's the GROUP BY statement after the FROM clause?

[RoyVidar]

"Helge's" <he************ *@plena.nowrote in message
<11************ *********@i3g20 00cwc.googlegro ups.com>:

I have this sql that give me the sales rank from the highest to the
lowest. I have several categories and need to rank within each
category. h1.Cat is the categoryfield.

SELECT h1.Cat, h1.Oms, (SELECT COUNT(*)
FROM tblOms AS h2
WHERE h2.oms >= h1.oms) AS Rank
FROM tblOms AS h1
ORDER BY h1.Oms DESC;

Would it be like GROUP by h1.Cat some place.

Hei igjen Helge!

Try something like this;

SELECT h1.Cat, h1.Oms, (SELECT COUNT(*)
FROM tblOms AS h2
WHERE h2.oms >= h1.oms AND
h2.Cat = h1.Cat) AS Rank
FROM tblOms AS h1
ORDER BY h1.Oms DESC;

--
Roy-Vidar

[CDMAPoster]

Helge's wrote:

I have this sql that give me the sales rank from the highest to the
lowest. I have several categories and need to rank within each
category. h1.Cat is the categoryfield.

SELECT h1.Cat, h1.Oms, (SELECT COUNT(*)
FROM tblOms AS h2
WHERE h2.oms >= h1.oms) AS Rank
FROM tblOms AS h1
ORDER BY h1.Oms DESC;

Would it be like GROUP by h1.Cat some place.

C.f.:

http://groups.google.com/group/comp....b6eb508f22fa9d

James A. Fortune
CD********@Fort uneJames.com