Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2008-10-13
6
Medium Priority
?
2,051 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

636 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