| Author |
 Topic  |
|
|
bpuccha
Starting Member
29 Posts |
 Posted - 06/01/2012 : 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
India
47173 Posts |
Posted - 06/01/2012 : 12:03:49
|
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
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
bpuccha
Starting Member
29 Posts |
Posted - 06/01/2012 : 12:15:09
|
It is throwing the eblow error
'row_number' is not a recognized function name. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 06/01/2012 : 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
29 Posts |
Posted - 06/01/2012 : 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
India
47173 Posts |
Posted - 06/01/2012 : 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
29 Posts |
Posted - 06/01/2012 : 14:25:44
|
| Thanks,,It is working fine.... |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 06/01/2012 : 23:43:36
|
welcome
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
Query needed
