Display data in order - 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

General SQL Server Forums

New to SQL Server Programming

Display data in order

Author

Topic

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2010-12-03 : 04:46:43

My quert is :

SELECT G.c_hi0101_diag_type,G. C_HI0102_DIAG,D.T_DIAG_PRIN 
     FROM PPRNPI.HIPTDIAGNS G
     LEFT OUTER JOIN PPRNPI.CRPTICD9DIAG D
     ON G.c_hi0102_diag = D.C_DIAG_PRIN 
     LEFT OUTER JOIN PPRNPI.HIPTFILEINFO HF
     ON G.i_cms_track_key = HF.i_cms_track_key and substr(HF.T_K301_FORMAT,1,3) = 'AP' 
     WHERE   G.c_hi0101_diag_type in ('BK','ABK','BF','ABF')
     AND G.i_cms_track_key = '103'

i am getting data. but i need the 'BK','ABK' data needs to be dispayed first and 'BF','ABF' needs to be displayed second.

is it possible to do? please help me on achieving this

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-03 : 04:55:40

order by case when G.c_hi0101_diag_type in ('BK','ABK') then 1 else 2 end

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2010-12-03 : 05:01:01

[code]
SELECT G.c_hi0101_diag_type,G. C_HI0102_DIAG,D.T_DIAG_PRIN
FROM PPRNPI.HIPTDIAGNS G
LEFT OUTER JOIN PPRNPI.CRPTICD9DIAG D
ON G.c_hi0102_diag = D.C_DIAG_PRIN
LEFT OUTER JOIN PPRNPI.HIPTFILEINFO HF
ON G.i_cms_track_key = HF.i_cms_track_key and substr(HF.T_K301_FORMAT,1,3) = 'AP'
WHERE G.c_hi0101_diag_type in ('BK','ABK','BF','ABF')
AND G.i_cms_track_key = '103'
order by case when G.c_hi0101_diag_type in ('BK','ABK') then 1 else 2 end

[/code]

Is this correct ? what is the 1 nad 2 in the order by statement at the last. please let me know.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-03 : 05:08:18

It's just the value to order by
when the resultset has BK or ABK it uses 1 otherwise 2
It orders by these values and as 1 is less than 2 the rows with BK or ABK will be delivered before the ones without.

you could also do

select c_hi0101_diag_type,C_HI0102_DIAG,T_DIAG_PRIN
from
(
SELECT G.c_hi0101_diag_type,G.C_HI0102_DIAG,D.T_DIAG_PRIN, seq = case when G.c_hi0101_diag_type in ('BK','ABK') then 1 else 2 end
FROM PPRNPI.HIPTDIAGNS G
LEFT OUTER JOIN PPRNPI.CRPTICD9DIAG D
ON G.c_hi0102_diag = D.C_DIAG_PRIN
LEFT OUTER JOIN PPRNPI.HIPTFILEINFO HF
ON G.i_cms_track_key = HF.i_cms_track_key and substr(HF.T_K301_FORMAT,1,3) = 'AP'
WHERE G.c_hi0101_diag_type in ('BK','ABK','BF','ABF')
AND G.i_cms_track_key = '103'
) a
order by seq

it will do the same thing

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2010-12-03 : 05:12:05

Thanks a lot nigelrivett.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources