quote:

---

Originally posted by visakh16

join this with drug table on drugkey to get other associated info

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

quote:

---

Originally posted by visakh16

select TOP 1 DR.DrugName 
from FactTable F, Disease D ,Drug DR
where 
F.DiseaseKey=D.DiseaseKey and
D.DiseaseType='lifestyle' and
DR.DrugKey = F.DrugKey
Group By DrugName
ORDER BY sum(F.Quantity) DESC

add WITH TIES if you need to return all drug in case of tie for max usage

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

quote:

---

Originally posted by vijayrawatsan

U gave this answer for question 3

select TOP 1 DR.DrugName
from FactTable F, Disease D ,Drug DR
where
F.DiseaseKey=D.DiseaseKey and
D.DiseaseType='lifestyle' and
DR.DrugKey = F.DrugKey
Group By DrugName
ORDER BY sum(F.Quantity) DESC

Instead Of Group By DrugName shouldn't it be Group By DR.DrugName(please do tell)
its not a problem as you've only single occurance of DrugName anyways its always best to use alias for clarity



Please check my below answers ...please do tell are they correct....(Literally thanks a lot for helping...)
answer4)
select TOP 1 PH.Name from Facttable F, Disease D, Physician PH
where
F.DiseaseKey=D.DiseaseKey and
F.PhysicianKey=PH.PhysicianKey and
D.DiseaseType='genetic'
Group By PH.Name
Order By count(F.PhysicianKey) DESC

correct


answer5)
select TOP 1 T.Day from FactTable F, Time T
where
F.TimeKey=T.TimeKEy and
Group By T.Day
Order By count(F.TimeKey) DESC
not correct this will just give day occuring maximum times in fact but you need also consider condition there is maximum demand of drugs so need a join to drug as well to get maximum count and get day corresponding to that


answer6)
select TOP 1 D.DiseaseName from FactTable F, Patient P, Disease D
where
F.DiseaseKey = D.DiseaseKEy and
F.PatientKey=P.PatientKey and
P.Gender='female'
Group By D.DiseaseName
Order By count(DiseaseKey)

need a desc in order by as you want to look in decending order

---