By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,660 Members | 1,071 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,660 IT Pros & Developers. It's quick & easy.

Rank within a category (Sql)

P: n/a
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.

Aug 25 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a

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?

Aug 25 '06 #2

P: n/a
"Helge's" <he*************@plena.nowrote in message
<11*********************@i3g2000cwc.googlegroups.c om>:
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
Aug 25 '06 #3

P: n/a
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********@FortuneJames.com

Aug 25 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.