April 3, 2010 at 6:04 pm
Size DBName Date
1329476ABC2009-06-08 06:00:00.843
1329476ABC2009-06-15 06:00:01.120
1373372ABC2009-06-22 06:00:00.270
1373372ABC2009-06-29 06:00:00.743
1373372ABC2009-07-06 06:00:00.713
1373372ABC2009-07-13 06:00:01.130
1373372ABC2009-07-20 06:00:01.063
1388286ABC2009-08-10 06:00:00.240
1388286ABC2009-08-17 06:00:00.427
Hoa can i get the Required result below fro mthe above data.
1329476ABC2009-06-08 06:00:00.843
1373372ABC2009-06-29 06:00:00.743
1388286ABC2009-08-10 06:00:00.240
April 3, 2010 at 10:53 pm
Tara, from your expected result set, i see u want the minimum date for the combination of size and DB name..
(if the above statement is right, then can u pls edit your desired result., it seems contradictory)
you could try something like :
SELECT dbname, size, MIN(date) FROM YOUR_TABLE_NAME
GROUP BY dbname, size
Also, please provide your table strucutre, sample data in ready-to-use format... this will help us in resolving the issue soon, which wil save both of our times...:-)
April 3, 2010 at 11:59 pm
Tara,
To get your result a GROUP BY clause has to be used.
When a GROUP BY clause is used, the required columns have to present in either with the AGGREGATE functions (like MIN() as above) or column should be mentioned in the GROUP BY part.
John
April 4, 2010 at 4:20 am
DECLARE @Table
TABLE (
name SYSNAME NOT NULL,
size INTEGER NOT NULL,
date_time DATETIME NOT NULL,
PRIMARY KEY (name, size, date_time)
);
-- Sample data
INSERT @Table
(name, size, date_time)
SELECT 'ABC', 1329476, '2009-06-08T06:00:00.843' UNION ALL
SELECT 'ABC', 1329476, '2009-06-15 06:00:01.120' UNION ALL
SELECT 'ABC', 1373372, '2009-06-22 06:00:00.270' UNION ALL
SELECT 'ABC', 1373372, '2009-06-29 06:00:00.743' UNION ALL
SELECT 'ABC', 1373372, '2009-07-06 06:00:00.713' UNION ALL
SELECT 'ABC', 1373372, '2009-07-13 06:00:01.130' UNION ALL
SELECT 'ABC', 1373372, '2009-07-20 06:00:01.063' UNION ALL
SELECT 'ABC', 1388286, '2009-08-10 06:00:00.240' UNION ALL
SELECT 'ABC', 1388286, '2009-08-17 06:00:00.427';
-- One possible solution
SELECT T1.name,
T1.size,
T1.date_time
FROM @Table T1
WHERE date_time =
(
SELECT MIN(T2.date_time)
FROM @Table T2
WHERE T2.name = T1.name
AND T2.size = T1.size
)
ORDER BY
T1.name,
T1.size,
T1.date_time;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply