473,544 Members | 2,299 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

retrieving the the max occurrence of a record

Hi,

In the datawarehouse DB (under MS commerce server 2002) a table stores
the referer domain name. Table structure is like

refererdomainid <binary>,domain InternalFlag
<0/1>,refererDomai nName<varchar>

e.g.

<binary>|0|unkn own
<binary>|1|goog le.com
<binary>|1|yaho o.com
<binary>|1|goog le.com
<binary>|1|goog le.com
<binary>|1|goog le.com
<binary>|1|alta vista.com

my problem is to build a query (using this table only) which
refererDomainNa me has the max occurrence and how many times. As in the
table above it is google.com and 4 times.

Can anyone help me.
Thanks in advance.
Jul 20 '05 #1
1 6375
Here are two alternatives:

SELECT refererdomainna me, COUNT(*)
FROM SomeTable
GROUP BY refererdomainna me
HAVING COUNT(*) >= ALL
(SELECT COUNT(*)
FROM SomeTable
GROUP BY refererdomainna me)

SELECT TOP 1 WITH TIES
refererdomainna me, COUNT(*)
FROM SomeTable
GROUP BY refererdomainna me
ORDER BY COUNT(*) DESC

--
David Portas
SQL Server MVP
--
Jul 20 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
4326
by: Robert | last post by:
How can I query an existing table and update a field in each record in the table with the occurrence count of each record e.g. update field to 1 (= first record occurrence), update field to 2 for 2nd record occurrence i.e. first duplicate record), update the field to 3 for the 3rd record occurrence i.e. 2nd duplicate record Example for a...
1
3879
by: tangus via DotNetMonster.com | last post by:
Hello all, I'm really struggling with getting some Active Directory code to work in ASP.NET. Can you please provide assistance? I am executing the following code: Dim enTry As DirectoryEntry = New DirectoryEntry("LDAP://domain") Dim mySearcher As New DirectorySearcher(enTry) Dim resEnt As SearchResult mySearcher.Filter =...
0
2040
by: Andy | last post by:
Hi All. I'm working for a company that has set out a guideline for retrieving data from a database. Nobody can explain to me the reason for the following. When retrieving a set of records from database to a VB.net app, they retrieve the database fields as a record set eg. "select name, suburb from myTable"
10
4106
by: Bhavna | last post by:
I am using a Replace function to replace single quotes with double when submitting a text field in the database i.e. Replace (q, "'", "' ' ") which works fine. When I retrieve the field from the database which has apostrophe I am getting 'Object expected' error message. Is there a way to fix this? Thanks, Bhavna
11
2007
by: Nemisis | last post by:
Hi everyone, sorry if this post gets really long, i just wanna make sure i fully explain what i am trying to do. I am new to OOP and .net 2.0, so if this is obvious, i am sorry. I have wrote a data access layer, which contists of separate servicebases for each object within my database. CompanyDataService (contains CRUD methods)...
7
4688
by: rfinch | last post by:
Very new to this but using the MS working with dynamics CRM 3.0 book to run web application to retrieve lead records from CRM 3.0. Have followed the book instructions on page 380-382. But am getting the following in ie 7. Compiler Error Message: CS0234: The type or namespace name 'CrmSdk' does not exist in the class or namespace...
15
3508
by: gunnar.sigurjonsson | last post by:
I´m having some problem retrieving identity value from my newly inserted row into a view. I have two tables T1 and T2 which I define as following CREATE TABLE T1 ( id BIGINT GENERATED ALWAYS AS IDENTITY ( START WITH 1
9
35373
ADezii
by: ADezii | last post by:
One question which pops up frequently here at TheScripts is: 'How do I retrieve data from a Recordset once I've created it?' One very efficient, and not that often used approach, is the GetRows() Method of the Recordset Object. This Method varies slightly from DAO to ADO, so for purposes of this discussion, we'll be talking about DAO Recordsets....
3
43416
ADezii
by: ADezii | last post by:
Last Tip, we demonstrated the technique for retrieving data from a DAO Recordset, and placing it into a 2-dimensional Array using the GetRows() Method. This week, we will cover the same exact Method (GetRows()), but only as it applies to an ADO Recordset. Although there are similarities in the 2 methodologies, the ADO Method offers 2 more Optional...
0
7625
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
7781
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
7717
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
1
5306
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
4930
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3421
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1848
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
993
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
677
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.