Returning a top value in select statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Returning a top value in select statement

Author

Topic

bendertez
Yak Posting Veteran

94 Posts

Posted - 2009-05-27 : 04:32:02

Hello

I have a relatively simple select statement which is returning two rows for people who work in 2 seperate departments, a field which i need on my report.

I would like to return one row for these people simply selecting the first department value it hits (i'm not bothered which it returns, as long as there is something there).

I want one line with one value in the department field.

What is the easiest way of doing this?

I was thinking of doing some sort of complicated department scoring routine that would then select the higest given value, but i'm not really that bothered what is displayed as long as i have a value in there.

I think i'm looking too hard at this and i'm missing a far simpler way.

Can anybody help?

Thanks in advanced.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-27 : 04:50:42

You can use SELECT EmpID, MAX(Department) FROM Table1 GROUP BY EmpID
We have to see your table definition and same sample data and expected output before giving more useful advice.

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-27 : 10:01:24

or you could do like this


SELECT e.*
FROM Table e
JOIN (SELECT EmpID, MAX(Department) AS Dept FROM Table1 GROUP BY EmpID)e1
ON e1.EmpID=e.EmpID
AND e1.Dept =e.Department

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources