assign priority 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)

assign priority in select statement

Author

Topic

eevans
Starting Member

48 Posts

Posted - 2009-03-03 : 09:21:59

Hello,
This is obviously incorrect syntax but it gives the just of what I want to do...

SELECT emp_id,
COALESCE (WHEN d_cde='t', WHEN d_cde='k', WHEN d_cde='f')

An employee (emp_id) may have multiple columns with different d_cde values. If, for example, an employee had two columns- one with d_cde='k' and another with d_cde'f'- I would want the results to show...

emp_id d_cde
123456 k

Any suggestions? Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-03 : 09:26:59

SELECT emp_id,
MAX(d_cde)

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-03-03 : 09:27:07

Something like this

SELECT emp_id, max(d_cde) from table
group by emp_id

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-03-03 : 09:27:33

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-03 : 09:28:56

[code]SELECT emp_id,
COALESCE(MAX(CASE WHEN d_cde='k' THEN d_cde ELSE NULL END),MAX(CASE WHEN d_cde='f' THEN d_cde ELSE NULL END),MAX(CASE WHEN d_cde='t' THEN d_cde ELSE NULL END))
FROM table
GROUP BY emp_id
[/code]

eevans
Starting Member

48 Posts

Posted - 2009-03-03 : 09:33:03

Ok that makes sense. Thanks.

One more question, what if the values didn't happen to have a convinent alphabetical order.

For example, what if I want a single row for each emp_id returned based on this order: 'k'(1), 't'(2), 'f'(3)?

Thanks.

eevans
Starting Member

48 Posts

Posted - 2009-03-03 : 09:34:31

Disregard previous post. I hadn't read visakh16 yet. Thanks!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-03 : 09:39:37

Visakhs post is limited.
You should use an auxialiary table having "order of magnitude".

CREATE TABLE	MagnitudeTable
		(
			Code CHAR(1),
			Weight INT
		)
GO
INSERT	MagnitudeTable
SELECT	'k', 1 UNION ALL
SELECT	't', 2 UNION ALL
SELECT	'f', 3
GO

SELECT		EmpID,
		dCode
FROM		(
			SELECT		t1.EmpID,
					t1.dCode,
					ROW_NUMBER() OVER (PARTITION BY t1.EmpID ORDER BY mt.Weight) AS recID
			FROM		Table1 AS t1
			INNER JOIN	MagnitudeTable AS mt ON mt.Code = t1.d_code
		) AS d
WHERE		recID = 1
ORDER BY	EmpID

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-03 : 09:43:37

quote:
Originally posted by eevans

Disregard previous post. I hadn't read visakh16 yet. Thanks!

As peso suggested, i'm assuming that you'll always have only three codes. if your code values are not static and also the order in which you want output varies then you need to use dynamic sql or use another table as peso suggested.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources