Select statement - 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

Select statement

Author

Topic

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2011-01-18 : 12:12:29

Hi,

I have the following code which works fine for one prac_no (91) and col_uid (23323).. However, I want to make it work for the enter table therefore, remove the where clause .... any help please

SELECT     TOP (1) gprdsql.TblColProcessing.prac_no, gprdsql.TblColProcessing.col_uid, gprdsql.TblColProcessing.stage, 
                      gprdsql.TblColProcessing.system_time, gprdsql.TblColProcessing.status, gprdsql.TblCollections.stage AS ColStage
FROM         gprdsql.TblColProcessing INNER JOIN
                      gprdsql.TblCollections ON gprdsql.TblColProcessing.col_uid = gprdsql.TblCollections.col_uid AND 
                      gprdsql.TblColProcessing.prac_no = gprdsql.TblCollections.prac_no
WHERE     (gprdsql.TblColProcessing.prac_no = 91) AND (gprdsql.TblColProcessing.col_uid = 23323)
ORDER BY gprdsql.TblColProcessing.system_time DESC

The above code outputs:

prac_no col_uid stage system_time status ColStage
91 23323 Received 06/05/10 Completed Processed

What the output will be should be like:

prac_no col_uid stage system_time status ColStage
91 23323 Received 06/05/10 Completed Processed
100 28916 Received 08/05/10 Completed Processed
101 29000 Received 06/04/10 Completed Processed

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-01-18 : 12:59:46

[code]SELECT ~~TOP (1)~~ gprdsql.TblColProcessing.prac_no, gprdsql.TblColProcessing.col_uid, gprdsql.TblColProcessing.stage,
gprdsql.TblColProcessing.system_time, gprdsql.TblColProcessing.status, gprdsql.TblCollections.stage AS ColStage
FROM gprdsql.TblColProcessing INNER JOIN
gprdsql.TblCollections ON gprdsql.TblColProcessing.col_uid = gprdsql.TblCollections.col_uid AND
gprdsql.TblColProcessing.prac_no = gprdsql.TblCollections.prac_no
~~WHERE (gprdsql.TblColProcessing.prac_no = 91) AND (gprdsql.TblColProcessing.col_uid = 23323)~~
ORDER BY gprdsql.TblColProcessing.system_time DESC

[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-24 : 11:08:01

as i understand this is what you want (assuming you're using SQL 2005)


SELECT prac_no,col_uid,stage,system_time,status,ColStage,
FROM
(
SELECT   gprdsql.TblColProcessing.prac_no, gprdsql.TblColProcessing.col_uid, gprdsql.TblColProcessing.stage, 
                      gprdsql.TblColProcessing.system_time, gprdsql.TblColProcessing.status, gprdsql.TblCollections.stage AS ColStage,
ROW_NUMBER() OVER (PARTITION BY gprdsql.TblColProcessing.prac_no,gprdsql.TblColProcessing.col_uid ORDER BY gprdsql.TblColProcessing.system_time DESC) AS rn
FROM         gprdsql.TblColProcessing INNER JOIN
                      gprdsql.TblCollections ON gprdsql.TblColProcessing.col_uid = gprdsql.TblCollections.col_uid AND 
                      gprdsql.TblColProcessing.prac_no = gprdsql.TblCollections.prac_no
)t
WHERE     rn=1

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources