?
Solved

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

Posted on 2008-10-13
6
Medium Priority
?
2,045 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
Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

 
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

801 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