Selecting First row from a set
-
I have good one:
There is a table t1 with columns C1 and C2. The result from t1 is as under:
1 1
1 2
1 3
2 1
2 2
2 3
3 2
4 1
......
I want to select the result set:
1 1
2 1
3 2
4 1
......
The first record in the group. I have done this using a temp table and an Identity Value and then by selecting the Min(IdentityCOL) or can be done by using cursors as well.
But is there a way to write a SQL Select query for the same problem without creating a temp table or Cursor? Help will be appreciated
-
Not sure it's what you need but :
Select c1, min(c2) as c2 from t1 group by c1
-
Thanks for the reply Remi - but it is not the Minimum it is the first value in the group that I want in the result set.
-
Maybe something like this :
create table #ids( n int,name varchar(20))
insert into #ids (n,name) values (7534,'tt')
insert into #ids (n,name) values (7535,'tt')
insert into #ids (n,name) values (7536,'tt')
insert into #ids (n,name) values (7537,'nn')
insert into #ids (n,name) values (7538,'nn')
insert into #ids (n,name) values (7539,'tt')
/*
Start end COunt name
7534 7536 3 tt
7537 7538 2 nn
7539 7539 1 tt
*/
SELECT X.name,MAX(Y.n),X.n,X.n-MAX(Y.n)+1 as Count
FROM
(SELECT a.n,a.name
FROM #ids a LEFT OUTER JOIN #ids b
ON a.name=b.name AND a.n=b.n-1
WHERE b.name IS NULL
) X
INNER JOIN
(SELECT a.n,a.name
FROM #ids a LEFT OUTER JOIN #ids b
ON a.name=b.name AND a.n=b.n+1
WHERE b.name IS NULL
) Y
ON X.name=Y.name
WHERE Y.n<=X.n
GROUP BY X.name,X.n
DROP TABLE #ids
-
Thats was fantastic, awesome, brilliant - thanks a lot Remi!
Is it possible to remove the Max co-ordinate from the result set? It needs to be removed from Group By as well.
-
I mean the n (max value) from the result set - can that be removed?
-
just Wrap the query in a select statement SQL is smart enough!
select X
from
( THE QUERY) derived
* Noel -
Also I forgot the usual :
Table are sets, sets have no order. There's no first or last or middle in a table. If you want order specify an order by clause. But I guess you already knew that
.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply