Retreive Data From Top(5) problem

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

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.

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

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

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.

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

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

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