Top 5 of each catagory

[Zipster]

Hi,

I have my data structured as follows:

nID txtName txtType boolShow

0 Ted CAR 1
1 Sam CAR 1
2 Kevin VAN 1
3 Sarah VAN 1
And so on...

How would I show any top 5 of each txtType where the boolShow value is true?

Thanks for your help.

K Abbott IT Manager / Web Developer

http://www.kabbott.com

 

[Zipster]

Sorry,

Also should of said showing the nID, txtName, and txtType fields in the result.

K Abbott IT Manager / Web Developer

http://www.kabbott.com

 

[r937]

i don;t think sql can do "any top 5"

"top" makes sense only if you specify which top 5, based on the values in some column(s)

"any" suggests randomization

and finally, but most importantly, which database system are you using?



r937.com | rudy.ca

 

[Zipster]

Sorry MS SQL,

SELECT myCol TOP 5 FROM DB

Would select the top 5 items in a databse no?

K Abbott IT Manager / Web Developer

http://www.kabbott.com

 

[r937]

like this --

SELECT TOP 5 myCol FROM myTable

you are likely to get the "nearest" 5, not the "top" 5

r937.com | rudy.ca

 

[Zipster]

That will do, but I need the top five of each txtType like the data structure in my first post.

Do you know whow I would do this?

Ta.

K Abbott IT Manager / Web Developer

http://www.kabbott.com

 

[r937]

yes, i know how to get the top 5 by category, but like i said, unfortunately it requires some distinguishing criterion in order to write the sql

how about the top 5 by nid? the top 5 by txtname?

actually, there aren't that many alternatives, unless of course your table is really more complex than what you have shown

r937.com | rudy.ca

 

[Zipster]

Yes top five by txtname is great! Please show.

K Abbott IT Manager / Web Developer

http://www.kabbott.com

 

[r937]

select t1.nID
     , t1.txtName
     , t1.txtType
  from yourtable as t1
inner
  join yourtable as t2
    on t2.txtType = t1.txtType
   and t2.txtName >= t1.txtName
group
    by t1.nID
     , t1.txtName
     , t1.txtType
having count(*) <= 5

r937.com | rudy.ca

 

[Zipster]

Dude,

You are the master.

Thank you.

K Abbott IT Manager / Web Developer

http://www.kabbott.com