Asp Forum - distinct / group by / having - microsoft.public.sqlserver.programming

Lee

3/9/2007 11:22:00 AM

My data table has 4 million records, each record is assigned a
business ID based on the company, so its possible some records have
the same business ID.

My query needs to return just one from each business ID (it doesn't
matter which one at this stage -the first occurrence will be fine) as
well as the WHERE clause part of the query.

eg,

I need to get a distinct list of business ID:

SELECT distinct([businessID]) FROM data WHERE [country]='UK'

now with that list returned above I need to select the first
occurrence of the business ID from the same table.

I've tried the examples above but they do not return the correct
results ( I know how many records I should get based on the SELECT
distinct([businessID]) FROM data WHERE [country]='UK' query )

The group by and having queries do not allow me to return the other
columns, when I add them to the group by part of the query it effects
the records returned.

I know this is probably very simple and that I'm missing something
obvious but if you could help I would be greatful.

Thanks in advance,

Lee

5 Answers

Erland Sommarskog

3/9/2007 11:37:00 AM

Lee (lee@digital-interactive.com) writes:
> My data table has 4 million records, each record is assigned a
> business ID based on the company, so its possible some records have
> the same business ID.
>
>
> My query needs to return just one from each business ID (it doesn't
> matter which one at this stage -the first occurrence will be fine) as
> well as the WHERE clause part of the query.
>
> eg,
>
> I need to get a distinct list of business ID:
>
> SELECT distinct([businessID]) FROM data WHERE [country]='UK'
>
> now with that list returned above I need to select the first
> occurrence of the business ID from the same table.

SELECT businessID, col1, col2, col3
FROM (SELECT businessID, col1, col2, col3,
rn = row_number() OVER(PARTITION BY businessID
ORDER BY somecol)
FROM tbl
WHERE country = 'UK')
WHERE rn = 1

This solution requires SQL 2005.

For the future, please keep in mind to specify which version of SQL Server
you are using.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/...
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/...

masri999

3/9/2007 11:45:00 AM

Need to assume that you have businessdate (one occurence for
businessid) or some id in the table which is auto incremated
(identity)

select a.* from data a,
(select .businessID,min(businessdate) as businessdate
from data
group by businessid ) b
where a.businessID = b.businessID
and a.businessdate = b.businessdate

On Mar 9, 4:37 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> Lee (l...@digital-interactive.com) writes:
> > My data table has 4 million records, each record is assigned a
> > business ID based on the company, so its possible some records have
> > the same business ID.
>
> > My query needs to return just one from each business ID (it doesn't
> > matter which one at this stage -the first occurrence will be fine) as
> > well as the WHERE clause part of the query.
>
> > eg,
>
> > I need to get a distinct list of business ID:
>
> > SELECT distinct([businessID]) FROM data WHERE [country]='UK'
>
> > now with that list returned above I need to select the first
> > occurrence of the business ID from the same table.
>
> SELECT businessID, col1, col2, col3
> FROM (SELECT businessID, col1, col2, col3,
> rn = row_number() OVER(PARTITION BY businessID
> ORDER BY somecol)
> FROM tbl
> WHERE country = 'UK')
> WHERE rn = 1
>
> This solution requires SQL 2005.
>
> For the future, please keep in mind to specify which version of SQL Server
> you are using.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downl......
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/b... Hide quoted text -
>
> - Show quoted text -

Lee

3/9/2007 12:27:00 PM

Hi

I've tried

SELECT *
FROM (SELECT businessID,
rn=Row_number() OVER(PARTITION BY businessID ORDER BY
surname)
FROM data
WHERE country = 'SINGAPORE')
WHERE rn=1

but it's reporting:

Line 6 Incorrect syntax near the keyword 'WHERE'.

Which is the

WHERE rn=1

Any suggestions?

Thanks
Lee

Tom Moreau

3/9/2007 1:10:00 PM

You need to do it as a CTE. See my reply to your other post.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada

"Lee" <lee@digital-interactive.com> wrote in message
news:1173443207.895713.26020@v33g2000cwv.googlegroups.com...
Hi

I've tried

SELECT *
FROM (SELECT businessID,
rn=Row_number() OVER(PARTITION BY businessID ORDER BY
surname)
FROM data
WHERE country = 'SINGAPORE')
WHERE rn=1

but it's reporting:

Line 6 Incorrect syntax near the keyword 'WHERE'.

Which is the

WHERE rn=1

Any suggestions?

Thanks
Lee

David Portas

3/9/2007 1:19:00 PM

On 9 Mar, 11:22, "Lee" <l...@digital-interactive.com> wrote:
> My data table has 4 million records, each record is assigned a
> business ID based on the company, so its possible some records have
> the same business ID.
>
> My query needs to return just one from each business ID (it doesn't
> matter which one at this stage -the first occurrence will be fine) as
> well as the WHERE clause part of the query.
>
> eg,
>
> I need to get a distinct list of business ID:
>
> SELECT distinct([businessID]) FROM data WHERE [country]='UK'
>
> now with that list returned above I need to select the first
> occurrence of the business ID from the same table.
>
> I've tried the examples above but they do not return the correct
> results ( I know how many records I should get based on the SELECT
> distinct([businessID]) FROM data WHERE [country]='UK' query )
>
> The group by and having queries do not allow me to return the other
> columns, when I add them to the group by part of the query it effects
> the records returned.
>
> I know this is probably very simple and that I'm missing something
> obvious but if you could help I would be greatful.
>
> Thanks in advance,
>
> Lee

Does BusinessID appear as the key of some other table ("Business" in
the following example). If so, try this:

SELECT T.BusinessID, T.col1, T.col2, T.col3
FROM Business AS B
CROSS APPLY
(SELECT TOP 1 BusinessID, col1, col2, col3
FROM data
WHERE BusinessID = B.BusinessID
AND country = 'UK'
) AS T;

Note that this returns some undefined row for each BusinessID,
something I would not usually recommend in production code.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/librar...(en-US,SQL.90).aspx
--

microsoft.public.sqlserver.programming

distinct / group by / having

Lee

Erland Sommarskog

masri999

Lee

Tom Moreau

David Portas

x Login to ForumsZone