Unique row from subset of different values

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

Transact-SQL (2005)

Unique row from subset of different values

Author

Topic

justjohno
Starting Member

23 Posts

Posted - 2010-01-04 : 19:05:21

I need help with the table below. The disired result is at the bottom. I've been working on this for some time and have not gotten anywhere. Need to make a unique row for each pc_id, extracting from the colums that are not null and with the newest pc_date. I got a temp table and cursor, but just don't know what to do with it. The table below has more colums, but I just included a small set. What I have so far is below. If anyone can help me with this, it would be great.

Thanks

Table:
pc_id pc_rec pc_name pc_disk pc_ram pc_date
----------- ----------- -------- ----------- ----------- -----------------------
1 1 aaaaaaaa NULL NULL 2009-05-30 00:00:00.000
1 2 NULL 800 4 2009-05-30 00:00:00.000
1 3 NULL NULL NULL 2009-05-30 00:00:00.000
1 3 NULL NULL NULL 2009-05-31 00:00:00.000
1 4 NULL NULL NULL 2009-05-30 00:00:00.000
1 5 NULL NULL NULL 2009-05-30 00:00:00.000
2 1 bbbbbbbb NULL NULL 2009-05-30 00:00:00.000
2 2 NULL 600 2 2009-05-30 00:00:00.000
2 3 NULL NULL NULL 2009-05-30 00:00:00.000
2 4 NULL NULL NULL 2009-05-23 00:00:00.000
2 4 NULL NULL NULL 2009-05-29 00:00:00.000
2 5 NULL NULL NULL 2009-05-30 00:00:00.000
3 1 ccccccc NULL NULL 2009-05-30 00:00:00.000
3 2 NULL 400 2 2009-05-30 00:00:00.000
3 3 NULL NULL NULL 2009-05-30 00:00:00.000
3 4 NULL NULL NULL 2009-05-30 00:00:00.000
3 5 NULL NULL NULL 2009-05-30 00:00:00.000
4 1 dddddd NULL NULL 2009-05-30 00:00:00.000
4 2 NULL 800 4 2009-05-30 00:00:00.000
4 3 NULL NULL NULL 2009-05-30 00:00:00.000
4 4 NULL NULL NULL 2009-05-30 00:00:00.000
4 5 NULL NULL NULL 2009-05-30 00:00:00.000
5 1 ffffffff NULL NULL 2009-05-30 00:00:00.000
5 1 ffffffff NULL NULL 2009-05-30 00:00:00.000
5 2 NULL 850 2 2009-05-30 00:00:00.000
5 3 NULL NULL NULL 2009-05-30 00:00:00.000
5 4 NULL NULL NULL 2009-05-30 00:00:00.000
5 5 NULL NULL NULL 2009-05-30 00:00:00.000
5 5 NULL NULL NULL 2009-05-31 00:00:00.000

Disired result set:
pc_id pc_rec pc_name pc_disk pc_ram pc_date
----------- ----------- -------- ----------- ----------- -----------------------
1 1 aaaaaaaa 800 4 2009-05-30 00:00:00.000
2 1 bbbbbbbb 600 2 2009-05-30 00:00:00.000
3 1 ccccccc 400 2 2009-05-30 00:00:00.000
4 1 dddddd 800 4 2009-05-30 00:00:00.000
5 1 ffffffff 850 2 2009-05-30 00:00:00.000

What I got so far:

------------------------------------------------------------------------------------
-- DECLARE statements - Declare variables used in the code block
------------------------------------------------------------------------------------
DECLARE @pc_id int,
@pc_rec int,
@pc_name char(8),
@pc_disk int,
@pc_ram int,
@pc_bios char(10),
@pc_ip char(15),
@pc_last_boot datetime,
@pc_os char(20),
@pc_last_logid char(10),
@pc_date datetime

-------------------------------------------------------------------------------------
-- Create temp table
-------------------------------------------------------------------------------------

DECLARE @pc_data_final table (
pc_id int,
pc_rec int,
pc_name char(8),
pc_disk int,
pc_ram int,
pc_bios char(10),
pc_ip char(15),
pc_last_boot datetime,
pc_os char(20),
pc_last_logid char(10),
pc_date datetime
)

-------------------------------------------------------------------------------------
-- DECLARE CURSOR statement - Populate the cursor with values that will be evaluated
-------------------------------------------------------------------------------------
DECLARE c1 CURSOR FOR
SELECT pc_name,
pc_rec,
pc_name,
pc_disk,
pc_ram,
pc_bios,
pc_ip,
pc_last_boot,
pc_os,
pc_last_logid,
pc_date
FROM pc_data
---------------------------------------------------------------------------------------
--OPEN statement - Open the cursor to begin data processing
---------------------------------------------------------------------------------------
OPEN c1

----------------------------------------------------------------------------------------
--FETCH NEXT statements - Assign the specific values from the cursor to the variables
-----------------------------------------------------------------------------------------
FETCH NEXT
FROM c1
INTO @pc_name,
@pc_rec,
@pc_name,
@pc_disk,
@pc_ram,
@pc_bios,
@pc_ip,
@pc_last_boot,
@pc_os,
@pc_last_logid,
@pc_date
-----------------------------------------------------------------------------------------
-- WHILE statement - Condition to begin and continue data processing
-----------------------------------------------------------------------------------------
WHILE @@FETCH_STATUS = 0

-----------------------------------------------------------------------------------------
-- BEGIN...END statement - Start and end of the code block
-----------------------------------------------------------------------------------------
BEGIN
select @pc_name,
@pc_rec,
@pc_name,
@pc_disk,
@pc_ram,
@pc_bios,
@pc_ip,
@pc_last_boot,
@pc_os,
@pc_last_logid,
@pc_date
--where @pc_name is not null

FETCH NEXT
FROM C1
INTO @pc_name,
@pc_rec,
@pc_name,
@pc_disk,
@pc_ram,
@pc_bios,
@pc_ip,
@pc_last_boot,
@pc_os,
@pc_last_logid,
@pc_date

END
-----------------------------------------------------------------------------------------
-- CLOSE statement - Releases the current data and associated locks, but permits
-- the cursor to be re-opened.
-----------------------------------------------------------------------------------------
CLOSE C1

-----------------------------------------------------------------------------------------
-- DEALLOCATE statement - Destroys the cursor
-----------------------------------------------------------------------------------------
DEALLOCATE C1

John O

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-01-04 : 19:14:14

a common table expression will do the trick
with cte

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

balaganapathy.n
Starting Member

18 Posts

Posted - 2010-01-05 : 01:14:29

Hi, Please try this way.

CREATE TABLE #TEMP(pc_id INT, pc_rec INT, pc_name VARCHAR(50), pc_disk INT, pc_ram INT, pc_date DATETIME)

INSERT INTO #TEMP (pc_id, pc_rec, pc_name, pc_disk, pc_ram, pc_date )
SELECT 1,1, 'aaaaaaaa', NULL, NULL, CAST ('2009-05-30 00:00:00.000' AS DATETIME) UNION
SELECT 1, 2, NULL, 800, 4, CAST ('2009-05-30 00:00:00.000' AS DATETIME) UNION
SELECT 1, 3, NULL, NULL, NULL, CAST ('2009-05-30 00:00:00.000' AS DATETIME) UNION
SELECT 1, 3, NULL, NULL, NULL, CAST ('2009-05-31 00:00:00.000' AS DATETIME) UNION
SELECT 1, 4, NULL, NULL, NULL, CAST ('2009-05-30 00:00:00.000' AS DATETIME) UNION
SELECT 1, 5, NULL, NULL, NULL, CAST ('2009-05-30 00:00:00.000' AS DATETIME) UNION
SELECT 2, 1, 'bbbbbbbb', NULL, NULL, CAST ('2009-05-30 00:00:00.000' AS DATETIME) UNION
SELECT 2, 2, NULL, 600, 2, CAST ('2009-05-30 00:00:00.000' AS DATETIME) UNION
SELECT 2, 3, NULL, NULL, NULL, CAST ('2009-05-30 00:00:00.000' AS DATETIME) UNION
SELECT 2, 4, NULL, NULL, NULL, CAST ('2009-05-23 00:00:00.000' AS DATETIME) UNION
SELECT 2, 4, NULL, NULL, NULL, CAST ('2009-05-29 00:00:00.000' AS DATETIME) UNION
SELECT 2, 5, NULL, NULL, NULL, CAST ('2009-05-30 00:00:00.000' AS DATETIME) UNION
SELECT 3, 1, 'ccccccc', NULL, NULL, CAST ('2009-05-30 00:00:00.000' AS DATETIME) UNION
SELECT 3, 2, NULL, 400, 2, CAST ('2009-05-30 00:00:00.000' AS DATETIME) UNION
SELECT 3, 3, NULL, NULL, NULL, CAST ('2009-05-30 00:00:00.000' AS DATETIME) UNION
SELECT 3, 4, NULL, NULL, NULL, CAST ('2009-05-30 00:00:00.000' AS DATETIME) UNION
SELECT 3, 5, NULL, NULL, NULL, CAST ('2009-05-30 00:00:00.000' AS DATETIME) UNION
SELECT 4, 1, 'dddddd', NULL, NULL, CAST ('2009-05-30 00:00:00.000' AS DATETIME) UNION
SELECT 4, 2, NULL, 800, 4, CAST ('2009-05-30 00:00:00.000' AS DATETIME) UNION
SELECT 4, 3, NULL, NULL, NULL, CAST ('2009-05-30 00:00:00.000' AS DATETIME) UNION
SELECT 4, 4, NULL, NULL, NULL, CAST ('2009-05-30 00:00:00.000' AS DATETIME) UNION
SELECT 4, 5, NULL, NULL, NULL, CAST ('2009-05-30 00:00:00.000' AS DATETIME) UNION
SELECT 5, 1, 'ffffffff', NULL, NULL, CAST ('2009-05-30 00:00:00.000' AS DATETIME) UNION
SELECT 5, 2, NULL, 850, 2, CAST ('2009-05-30 00:00:00.000' AS DATETIME) UNION
SELECT 5, 3, NULL, NULL, NULL, CAST ('2009-05-30 00:00:00.000' AS DATETIME) UNION
SELECT 5, 4, NULL, NULL, NULL, CAST ('2009-05-30 00:00:00.000' AS DATETIME) UNION
SELECT 5, 5, NULL, NULL, NULL, CAST ('2009-05-30 00:00:00.000' AS DATETIME) UNION
SELECT 5, 5, NULL, NULL, NULL, CAST ('2009-05-31 00:00:00.000' AS DATETIME)

SELECT pc_id, MIN(pc_rec), MAX(pc_name), MAX(pc_disk), MAX(pc_ram), MAX(pc_date)
FROM #TEMP
GROUP BY pc_id

DROP TABLE #TEMP

balaganapathy n.

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2010-01-05 : 01:37:34

I don't think you can reliably.
1) What do you do when you have 2 different (or identical) values on the same date? You might have to change the order by to get this
2) I can't see how you can show a single date when your data is derrived from many dates, so you'll just have to pick one.

You can try something like this which might get you started:
select
a.pc_id,
(select top 1 pc_rec from pc_data b where a.pc_id=b.pc_id and pc_rec is not null order by pc_date desc) pc_rec,
(select top 1 pc_name from pc_data b where a.pc_id=b.pc_id and pc_name is not null order by pc_date desc) pc_name
from
(select distinct pc_id from pc_data) a
order by pc_id

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-01-08 : 12:49:06

cte is nice I think this will work for you


;WITH cte (pc_id, pc_rec, pc_name, pc_disk, pc_ram, pc_date)
AS
(
    SELECT pc_id, pc_rec, pc_name, pc_disk, pc_ram, pc_date
    FROM #TEMP
    WHERE pc_name IS NOT NULL AND pc_disk IS NULL AND  pc_ram IS NULL 
)
    SELECT  e.pc_id, e.pc_rec, cte.pc_name, e.pc_disk, e.pc_ram, e.pc_date
    FROM #TEMP AS e
    JOIN cte ON e.pc_id = cte.pc_id
    WHERE e.pc_name IS NULL AND e.pc_disk IS NOT NULL AND  e.pc_ram IS NOT NULL 

GO

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources