select one records from each group

Question

Post reply

select one records from each group

jay-125866

SSCommitted

Points: 1629
More actions
June 29, 2005 at 9:27 am

#67719

I'm trying to select one record only from a group. For example, using Pubs database, when I execute this query, select top 10 emp_id, fname, lname,job_lvl from employee order by job_lvl, there are three records for job_lvl 35. I'd like to show only one record from job_lvl 35. How do i do it?

Viewing 15 posts - 1 through 15 (of 29 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Can you post some sample data with the expected results... I'm not sure I understand what you want.

fhanlon SSChampion Points: 13131 More actions · Answer 2

Which record? There are several records where job_lvl = 35. If you just want the one where all the fields other than the job_lvl are alphabetically the largest you could write:

select top 10 max(emp_id), max(fname), max(lname) ,max(job_lvl)

from employee

group by job_lvl

order by job_lvl

Is this all you want?

Francis

noeld SSC Guru Points: 96590 More actions · Answer 3

OR:

select top 10 e.emp_id, e.fname, e.lname , e.job_lvl

from employee e join

(select job_lvl, min(emp_id) minID

from employee

group by job_lvl ) lvls on lvls.minID = e.emp_id and lvls.job_lvl = e.job_lvl

order by e.job_lvl

??? You need to specify a criteria to identify which of the employees at that level must be returned

* Noel

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Do you see me guessing?? I'm sick of it, now I just wait for the right informatin before trying to help the guy.

sushila SSC-Dedicated Points: 35293 More actions · Answer 5

Just for the fun of it.....

My guess is that fhanlon has it right!

**ASCII stupid question, get a stupid ANSI !!!**

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

Here's my guess, for the fun of it. Might be totally off but someone will learn something from this .

select top 90% per group :

SELECT O.XType

--, count(*) AS TotalHits_Found

, O.name

, (SELECT CEILING(COUNT(*) * 0.9) FROM dbo.SysObjects O4 WHERE O4.XType = O.XType) as [90%]

, (SELECT COUNT(*) FROM dbo.SysObjects O5 WHERE O5.XType = O.XType) AS [100%]

FROM dbo.SysObjects O

WHERE EXISTS (SELECT * FROM dbo.SysObjects O2 WHERE O2.XType = O.XType AND O2.id = O.id and O2.id IN (SELECT TOP 90 PERCENT id FROM dbo.SysObjects O3 WHERE O3.XType = O.XType ORDER BY O3.id))

--GROUP BY O.XType

ORDER BY O.XType

, O.Name

Please note that in this case it would seem to make more send to use NOT IN (Select top 10% ...), but from the tests I made, the plans and speed were the same. I didn't investigate much further than that though.

jay-125866 SSCommitted Points: 1629 More actions · Answer 7

thanks everyone. I've got it rightusing fhanlon's query.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

Congrats Sushila, and Fhanlon for correctly guesssing this solution.

Hey Jay can you post the actual requirements next time, we won't have to shoot out every possible answer to every possible problem in the book.

TIA.

noeld SSC Guru Points: 96590 More actions · Answer 9

I have to differ here:

I am pretty sure that fahnlon's is wrong ( no offense )

When you perform those max() are retrieving the MAX per column you may end up with a Person Firsname and Another Person's Last name giving you a name that does NOT exists. For example for Job_lvl = 35 it shows Paolo Lincoln Which Does not exists

Therefore all guesses were wrong an my query was right

Cheers!

* Noel

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 10

Now how can we help a guy who can't even tell what he wants .

Is it possible that my Pubs is broken... That query returns nothing for me Noeld... and you've obviously test that.

I'll try reinstalling...

noeld SSC Guru Points: 96590 More actions · Answer 11

You don't need to do anything very complicated to see that when you write max() of several columns you are going to get the max of each independently of what is on the other max() the only tie is the pivot column on the group by

* Noel

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 12

Obviously, now you see how little attention I pay to answers when I don't have any information on the question .

Anyways I reinstalled pubs and you're query is working now (obviously). The guy before me must have had to much fun with it .

noeld SSC Guru Points: 96590 More actions · Answer 13

I hear you, Lets just hope that the Poster kept reading the thread or he will be in for a BIG surpirse

* Noel

fhanlon SSChampion Points: 13131 More actions · Answer 14

I conceed Noel. The repeated use of MAX isn't right. Your query is better.

Francis