September 21, 2011 at 6:23 am
[font="Courier New"]In search of SQL to Create a VIEW which will display distinct rows from a table -- where the base table contains DUP rows.
My base table: Table_A --- with columns:
ACCT <-- part of PK
ROOM <-- part of PK
CUST <-- part of PK
SHELF <-- part of PK
REGION
LASTUPDATEDATE
(Note: PK defined on 4 col's: ACCT, ROOM, CUST, SHELF)
Sample rows in Base Table:
Acct_1 Room_1 CUST_1 SHELF_1 NORTH 2011-07-01 14:20:34.000
Acct_1 Room_1 CUST_1 SHELF_2 NORTH 2011-08-01 12:33:16.000
Acct_1 Room_1 CUST_1 SHELF_3 NORTH 2011-09-10 08:01:44.000
Using the above data sample, I'm looking for VIEW DDL on base Table_A which will OMIT the column name "SHELF" and render a distinct row as:
Acct_1 Room_1 CUST_1 NORTH 2011-07-01 14:20:34.000
My spec's say it doesn't matter which LASTUPDATEDATE value I pull.
More concerned w/ a single row being returned w/ the distinct values for ACCT, ROOM, CUST, REGION (and any of the LASTUPDATEDATE values
CREATE view [V1].[Table_A] with schemabinding as
select ACCT, ROOM, CUST, REGION, LASTUPDATEDATECORP from dbo.Table_A
The above View DDL is returning DUP rows. I need a single row.. [/font]
September 21, 2011 at 6:35 am
sounds like row number will give you exactly what you need:
SELECT
ACCT,
ROOM,
CUST,
REGION,
LASTUPDATEDATECORP
FROM
(SELECT
ROW_NUMBER() OVER(PARTITION BY ACCT, ROOM, CUST, REGION ORDER BY LASTUPDATEDATECORP DESC) AS RW,
ACCT,
ROOM,
CUST,
REGION,
LASTUPDATEDATECORP
FROM
dbo.Table_A) AS MyAlias
WHERE
RW = 1
Lowell
September 21, 2011 at 6:58 am
Lowell - worked perfectly. thanks!
September 21, 2011 at 7:06 am
Express12 (9/21/2011)
Lowell - worked perfectly. thanks!
you did everything right, thank you! excellent explanation, sample code of what you tried, gave me everything i needed to build a testable, working example.
good job, and glad i could help!
Lowell
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply