SQL Server Performance

Retreive Data From Top(5) problem

Discussion in 'SQL Server 2005 General Developer Questions' started by sun919, Oct 10, 2006.

  1. sun919 New Member

    Hi there ,
    Thanks again everyone for helping me out last time , I have a new problem situation since
    i am not sure which query to use .. I ve try IN, EXISTS and still give me error
    Basically I want to retrieve Data that is in ICD10 Table, but I query select top(5) from another table and using count()to sort the order and group. The query select Top works fine but when i combine with another table ICD10 it give me problems
    here is my query

    Select ICD10.ICD10code from ICD10
    where ICDD10.ICD10ID IN

    (below work fine it is the beginning)
    ( SELECT TOP (5) DiscoverList.ICD10ID, COUNT(DiscoverList.ICD10ID) AS Exp1, Patient.Gender
    FROM DiscoverList INNER JOIN
    Appointment ON DiscoverList.AppointmentID = Appointment.AppointmentID INNER JOIN
    Patient ON Patient.HNID = Appointment.HNID
    WHERE (Appointment.AppointmentDate > CONVERT(DATETIME, '2006-05-01 00:00:00', 102))
    GROUP BY DiscoverList.ICD10ID, Patient.Gender
    HAVING (Patient.Gender = 1)
    ORDER BY Exp1 DESC )

    please have a look
    many thanks
    sun
  2. Adriaan New Member

    Run your subquery as a separate query:

    SELECT TOP (5) DiscoverList.ICD10ID, COUNT(DiscoverList.ICD10ID) AS Exp1, Patient.Gender
    FROM DiscoverList INNER JOIN
    Appointment ON DiscoverList.AppointmentID = Appointment.AppointmentID INNER JOIN
    Patient ON Patient.HNID = Appointment.HNID
    WHERE (Appointment.AppointmentDate > CONVERT(DATETIME, '2006-05-01 00:00:00', 102))
    GROUP BY DiscoverList.ICD10ID, Patient.Gender
    HAVING (Patient.Gender = 1)
    ORDER BY Exp1 DESC

    Now what errors do you get?

    My guesses ...

    You only need the ICD10ID column in the column list.
    You should move COUNT(*) to the HAVING clause.
    You can drop the Patient.Gender column from the GROUP BY clause.

    I don't think you can use the alias for the COUNT(*) in the ORDER BY clause, so use ORDER BY COUNT(*) DESC.

    Use COUNT(column_name) only if you want to ignore NULL values on that column. If the column is not nullable, then COUNT(*) is equivalent.
  3. Roji. P. Thomas New Member

    quote:Originally posted by Adriaan

    I don't think you can use the alias for the COUNT(*) in the ORDER BY clause, so use ORDER BY COUNT(*) DESC.

    You Can.

    Roji. P. Thomas
    http://toponewithties.blogspot.com
  4. FrankKalis Moderator

    Yes, alias reuse in the ORDER BY clause is the only exception. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  5. sun919 New Member

    when i run select TOp
    it work fine the result are like this

    ICD10ID Exp1
    5 6
    1 4
    8 4
    2 3
    3 1
    and when i write

    select ICD10Code from ICD10
    WHere ICD10ID IN (pharse with select top) i expect to get something like

    Parkinson
    Heart Disease
    Lung Cancer
    Stroke
    Diabetic

    many thanks
    sun
  6. Adriaan New Member

    The query is returning the ICD10Code, exactly as requested. If you don't ask for the full name, you won't see it in the resultset.

    If the full name for the disease is in the same ICD10 table, then just use the column for the full name in the column list of the main query.

    If there is a reference (lookup) table for the full name, then you have to add that table to the main query, using a JOIN on the foreign key column.
  7. sun919 New Member

    I've try this code and it works now removing gender and adding Count(*) instead

    SELECT ICD10Code
    FROM ICD10
    WHERE (ICD10ID IN
    (SELECT TOP (5) DiscoverList.ICD10ID
    FROM DiscoverList INNER JOIN
    Appointment ON DiscoverList.AppointmentID = Appointment.AppointmentID INNER JOIN
    Patient ON Patient.HNID = Appointment.HNID
    WHERE (Appointment.AppointmentDate > CONVERT(DATETIME, '2006-05-01 00:00:00', 102))
    GROUP BY DiscoverList.ICD10ID
    ORDER BY COUNT(*) DESC))

    but the question still remain .... since i need to added condition in Patient Table ...
    i.e. users can filter out the result of top 5 diesease by gender or age which brings out a question of how would i might do
    that since when i added condition like below it doesnt work

    SELECT ICD10Code
    FROM ICD10
    WHERE ICD10ID EXISTS IN
    (SELECT TOP (5) DiscoverList.ICD10ID, Patient.Gender
    FROM DiscoverList INNER JOIN
    Appointment ON DiscoverList.AppointmentID = Appointment.AppointmentID INNER JOIN
    Patient ON Patient.HNID = Appointment.HNID
    WHERE (Appointment.AppointmentDate > CONVERT(DATETIME, '2006-05-01 00:00:00', 102))
    GROUP BY DiscoverList.ICD10ID, Patient.Gender
    HAVING (Patient.Gender = 1)
    ORDER BY COUNT(*) DESC)

    it said something is error when i ve try
    WHERE EXISTS
    (SELECT TOP (5)

    it give me all ICD10CODE instead just 5 of them
    so how do i come about ?
    many thanks again
    sun
  8. sun919 New Member

    sorry my mistake .... in the last post it should be
    WHERE ICD10ID IN
    instead of
    WHERE ICD10ID EXISTS IN

  9. sun919 New Member

    hi.... thanks for all your help ... i just realize that in th IN Suppose to only Take one parameter so What i did was to do the
    following :

    SELECT ICD10Code
    FROM ICD10
    WHERE (ICD10ID IN
    (SELECT TOP (5) DiscoverList.ICD10ID
    FROM DiscoverList AS DiscoverList INNER JOIN
    Appointment ON DiscoverList.AppointmentID = Appointment.AppointmentID INNER JOIN
    Patient ON Patient.HNID = Appointment.HNID
    WHERE (Appointment.AppointmentDate > CONVERT(DATETIME, '2006-05-01 00:00:00', 102))
    GROUP BY DiscoverList.ICD10ID, Patient.Gender
    HAVING (Patient.Gender = 1)
    ORDER BY COUNT(*) DESC))

    AND BY removing ,Patinet.Gender the code, works fine now
    again many thanks for all the help
    sun

Share This Page