?
Solved

select top 1 count(*) returning count of more than one record?

Posted on 2008-10-13
6
Medium Priority
?
2,053 Views
Last Modified: 2012-05-05
Ok I'm building a query to create a view in SQL server 2005 for work. One of the columns of the view is a simple 0/1 answer.

0 = the user in this record, has no records in TableB
1 = the user in this record has 1 or more records in TableB

So that column is simply a subquery (below)

The layout of TableB is as follows:
SHCDISTN
SHCRCD
SHCSTDT
SHCNBR
SCHCODE

there are no primary keys or relations to this table really. The SHCSTDT is a student number, so every medical 'code' has it's own record in the table with the corresponding student number. So student number "xyz" may have 3 records, no records, or 10000 record in TableB. Now given that, back to the subquery.

I figured I'd be tricky and simply do a count() for all records with that student number (xyz) but limit the result set to 1 (so the response is either 0 or 1 as desired).


The only issue is when the student number (xyz in this case) has 2 or more records in the table, instead of returning a "1", it returns the number of records they have, ignoring the "TOP 1" result set limit.

I come from a MySQL background so the MSSQL is still a little new to me. Am I missing a certain keyword or something? I've also tried to "group by SHCSTDT" but still get the same result. If needed I can clarify better.
////////////sub query
      (
        SELECT TOP 1
            COUNT(SHCSTDT) AS total
        FROM
            dbo.SHC
        WHERE
            ( SHCSTDT = dbo.SBR.SBRSTDT )
      ) AS [Medical Problems]

Open in new window

0
Comment
Question by:MMDeveloper
  • 3
  • 2
6 Comments
 
LVL 43

Expert Comment

by:TimCottee
ID: 22701739
Hello MMDeveloper,

You need a group by:

        (
          SELECT TOP 1
              COUNT(SHCSTDT) AS total
          FROM
              dbo.SHC
          WHERE
              ( SHCSTDT = dbo.SBR.SBRSTDT )
          GROUP BY SHCSTDT
        ) AS [Medical Problems]

Regards,

TimCottee
0
 
LVL 11

Accepted Solution

by:
Otana earned 2000 total points
ID: 22701753
The top 1 will return the first record of the resultset in which the count has already been done, so you get the full count.

Try this for subquery:

(SELECT CASE WHEN EXISTS(SELECT 1 FROM dbo.SHC WHERE SHCSTDT = dbo.SBR.SBRSTDT ) THEN 1 ELSE 0 END) AS [Medical Problems]
0
 
LVL 15

Author Comment

by:MMDeveloper
ID: 22701759
     (
        SELECT TOP 1
            COUNT(1) AS Expr1
        FROM
            dbo.SHC
        WHERE
            ( SHCSTDT = dbo.SBR.SBRSTDT )
        GROUP BY
            SHCSTDT
      ) AS [Medical Problem]


I still get results higher than 1. I now get null, 0, 1, 2, 3, 4, 5, etc.
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
LVL 15

Author Comment

by:MMDeveloper
ID: 22701796
Well I didn't 'fix' it but I just used a workaround for now.


      (
        SELECT
            CASE WHEN COUNT(*) >= 1 THEN '1'
                 ELSE '0'
            END AS Expr1
        FROM
            dbo.SHC
        WHERE
            ( SHCSTDT = dbo.SBR.SBRSTDT )
      ) AS [Medical Problem]
0
 
LVL 11

Expert Comment

by:Otana
ID: 22701812
Did you try my solution? It should be faster than yours.
0
 
LVL 15

Author Closing Comment

by:MMDeveloper
ID: 31505568
Sorry we must have been posting at the same time and I didn't see yours. Your solution worked just fine and I appreciate your help.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question