select the Max no. Of occurances
-
Hi all,
I have a table like this below
OrderID Step Type
12 10 1,2,3,4
12 20 2,3
12 30 1,2,4
so, I need to get the Max no. of type, when I supply the orderID.
When I give OrderID = 12, then the query should display the type 2.
give me some idea how can i get that. thanks
-
You design is flawed. The type colum should be placed into its own table like so :
Table orderTypes
orderid, step, Type (all this being your clustered primary key)).
Then do this :
Select top 1 /*with ties*/ type, count(*) from dbo.OrdersTypes
where orderid = 12
group by type
order by Count(*) desc
-
I agree - having your db designed like this will always frustrate you as queries which should take 30 seconds to write will take 30 minutes and perform 10 times worse. Can you modify the db design?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
-
Thnks, I'm gng to do tht. I had it in my mind but I didn't have proper privillages to change it
-
Thnks, I'm gng to do tht. I had it in my mind but I didn't have proper privillages to change it
If you don't have this privileges, but there is the chance to change it, ask someone else to change it. You might also want to tell that person, that such a design violates First Normal Form.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply