select one records from each group
-
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?
-
Can you post some sample data with the expected results... I'm not sure I understand what you want.
-
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
-
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 -
Do you see me guessing?? I'm sick of it, now I just wait for the right informatin before trying to help the guy.
-
Just for the fun of it.....
My guess is that fhanlon has it right!
**ASCII stupid question, get a stupid ANSI !!!** -
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.
-
thanks everyone. I've got it rightusing fhanlon's query.
-
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.
-
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 -
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...
-
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 -
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
. -
I hear you, Lets just hope that the Poster kept reading the thread or he will be in for a BIG surpirse
* Noel -
I conceed Noel. The repeated use of MAX isn't right. Your query is better.
Francis
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply