Query needed - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Query needed

Author

Topic

bpuccha
Starting Member

34 Posts

Posted - 2012-06-01 : 11:47:59

My table has the below date

ID P_no T_no risk po F cla form plan
1 00001 001 1 ON N HOM B SP
2 00001 001 2 ON N HOM C SP
3 00001 001 3 PQ Y HOM D SP
4 00001 001 4 BC N HOM A SP
5 00001 001 5 ON N HOM B SP
6 00001 001 6 PQ Y HOM D SP

Need the data in the below format

I need to get all the rows where flag = 'N' first then flag = 'Y'
and distinct p_no,t_no,po,cla,form and plan

ID P_no T_no risk po F cla form plan
1 00001 001 1 ON N HOM B SP
2 00001 001 2 ON N HOM C SP
4 00001 001 4 BC N HOM A SP
3 00001 001 3 PQ Y HOM D SP

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-01 : 12:03:49

[code]
SELECT ID, P_no, T_no, risk, po, F, cla, form, plan
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY p_no,t_no,po,cla,form ,plan ORDER BY risk) AS RN,*
FROM table
)t
WHERE RN=1
ORDER BY P_no, T_no,cla,plan, CASE F WHEN 'N' THEN 1 ELSE 2 END,risk
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bpuccha
Starting Member

34 Posts

Posted - 2012-06-01 : 12:15:09

It is throwing the eblow error

'row_number' is not a recognized function name.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-01 : 12:26:44

quote:
Originally posted by bpuccha

It is throwing the eblow error

'row_number' is not a recognized function name.

Are you sure you're using SQL Server with compatibilty level over 80?

what does below return?


SELECT @@VERSION

EXEC sp_dbcmptlevel 'database name here'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bpuccha
Starting Member

34 Posts

Posted - 2012-06-01 : 12:34:26

SELECT @@VERSION:

Microsoft SQL Server 2000 - 8.00.2301 (Intel X86) Jan 6 2012 17:23:12 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

and executed the EXEC sp_dbcmptlevel 'database name here'

Still it is throwing the same error..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-01 : 12:42:24

quote:
Originally posted by bpuccha

SELECT @@VERSION:

Microsoft SQL Server 2000 - 8.00.2301 (Intel X86) Jan 6 2012 17:23:12 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

and executed the EXEC sp_dbcmptlevel 'database name here'

Still it is throwing the same error..

then you should have posted it in 2000 forum

ROW_NUMBER is available only from SQL 2005 onwards

in sql 2000 you can try below


SELECT t.*
FROM table t
INNER JOIN (SELECT p_no,t_no,po,cla,form ,plan, MIN(risk) AS RN
FROM table
GROUP BY  p_no,t_no,po,cla,form ,plan
)t1
ON t1.p_no = t.p_no 
AND t1.t_no = t.t_no
AND t1.po = t.po
AND t1.cla = t.cla
AND t1.form = t.form
AND t1.plan = t.plan
AND t1.RN = t.risk
ORDER BY t.P_no, 
t.T_no,
t.cla,
t.plan, 
CASE t.F WHEN 'N' THEN 1 ELSE 2 END,
t.risk

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bpuccha
Starting Member

34 Posts

Posted - 2012-06-01 : 14:25:44

Thanks,,It is working fine....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-01 : 23:43:36

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources