Help me write this SQL Query....

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help me write this SQL Query....

Author

Topic

bobshishka
Yak Posting Veteran

72 Posts

Posted - 2008-01-28 : 17:41:33

I have a table with many records in it. There is one field called "Nature". How would I select the value that appears the most often in the "Nature" field? The nature field contains text.

For example, this code selects those with more than 10 records...

I just want the top record.

SELECT count(*) FROM WEBASGN_FULL GROUP BY NATURE HAVING count(*) > 10

Basically select nature from webasgn_full that occurs the most often in the table....

Thanks

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-28 : 18:14:05

Using RANK() you can return the row(s) with the highest number of occurrences in your table:

DECLARE @a TABLE ( Nature VARCHAR(3) )
INSERT @a ( Nature )
SELECT 'abc' UNION ALL
SELECT 'abc' UNION ALL
SELECT 'abc' UNION ALL
SELECT 'abc' UNION ALL
SELECT 'abc' UNION ALL
SELECT 'def' UNION ALL
SELECT 'def' UNION ALL
SELECT 'ghi' UNION ALL
SELECT 'ghi' UNION ALL
SELECT 'ghi' UNION ALL
SELECT 'ghi' UNION ALL
SELECT 'ghi' UNION ALL
SELECT 'jkl' UNION ALL
SELECT 'jkl' UNION ALL
SELECT 'jkl'

SELECT  Nature
FROM    ( SELECT    RANK() OVER ( ORDER BY COUNT(*) DESC ) AS rowrank,
                    Nature
          FROM      @a
          GROUP BY  Nature
        ) a
WHERE   rowrank = 1

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2008-01-28 : 18:14:07

You need to define "most often"

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

bobshishka
Yak Posting Veteran

72 Posts

Posted - 2008-01-28 : 18:35:22

quote:
Originally posted by dinakar

You need to define "most often"

"Most often" is the value of the field "NATURE" that appears the most in my table.

How about with a count(*) > 6? Is that easier than "most often"?

This table is auto-generated, so I cant use the 'select union all' option.

Does that help?

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-28 : 18:40:14

quote:
Originally posted by bobshishka

[quote]Originally posted by dinakar

This table is auto-generated, so I cant use the 'select union all' option.

I am only using that section to build an example table. The section you should be able to use against your table is:

SELECT  Nature
FROM    ( SELECT    RANK() OVER ( ORDER BY COUNT(*) DESC ) AS rowrank,
                    Nature
          FROM      @a
          GROUP BY  Nature
        ) a
WHERE   rowrank = 1

Just replace @a with your table name.

bobshishka
Yak Posting Veteran

72 Posts

Posted - 2008-01-28 : 18:58:00

When I run this :

set call_type = Conn.Execute ("SELECT Nature FROM (SELECT RANK() OVER (ORDER BY COUNT(*) DESC) AS rowrank, Nature FROM webasgn_full GROUP BY Nature) a WHERE rowrank = 1")

I get this error:

Microsoft JET Database Engine error '80040e14'

Syntax error (missing operator) in query expression 'RANK() OVER (ORDER BY COUNT(*) DESC)'.

/webcad/WEBASGN_FULLlist_1call_test.asp, line 483

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-28 : 19:28:14

Can you run the code in query analyzer? Does it give you an error there?

bobshishka
Yak Posting Veteran

72 Posts

Posted - 2008-01-29 : 11:15:17

Im running this query against a database in Access... is that the problem?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-29 : 11:21:33

Yes. The MS Access queries have a forum of their own.
http://www.sqlteam.com/forums/forum.asp?FORUM_ID=3