August 26, 2009 at 3:35 pm
HI..I am stuck.. I have data like this...
Data1 Data2 Data3 ....
A 100 20*
A 102 30
A 103 40
B 104 10*
B 105 50
B 106 50
B 107 30
C 108 20*
C 109 50
C 110 10
I want to get only three lines of data. In order of Data2 ASC
A 100 20
B 104 10
C 108 20
Any ideas...
August 26, 2009 at 3:41 pm
August 26, 2009 at 4:11 pm
SELECT TOP 1 Data1, Data2, Data3
FROM myTable
GROUP BY Data1, Data2, Data3
ORDER BY Data2
August 26, 2009 at 4:21 pm
DECLARE @Table TABLE (Data1 char(1), Data2 int, Data3 int)
INSERT INTO @Table
SELECT 'A', 100, 20 UNION ALL
SELECT 'A', 102, 30 UNION ALL
SELECT 'A', 103, 40 UNION ALL
SELECT 'B', 104, 10 UNION ALL
SELECT 'B', 105, 50 UNION ALL
SELECT 'B', 106, 50 UNION ALL
SELECT 'B', 107, 30 UNION ALL
SELECT 'C', 108, 20 UNION ALL
SELECT 'C', 109, 50 UNION ALL
SELECT 'C', 110, 10
SELECT t1.*
FROM @Table t1
INNER JOIN (
SELECT Data1,
MIN(Data2) as Data2
FROM @Table t
GROUP BY Data1
) t2
ON t1.Data1 = t2.Data1 AND t1.Data2 = t2.Data2
August 26, 2009 at 4:40 pm
Or:
with RowNumbered as (
select
row_number() over (partition by Data1 order by Data2 asc) as rownum,
Data1,
Data2,
Data3
from
dbo.MyTable -- you could substitute @Table from the above post and use its setup to check it out
)
select
Data1,
Data2,
Data3
from
RowNumbered
where
rownum = 1;
August 26, 2009 at 4:51 pm
Thanks kind folks should be able to get over this hump now !
Lynn..you have helped me before..thx:-)
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply