SQL Server Performance Forum – Threads Archive

problem in group by

Hello position_date to_date
—————————— ——————————————————
2006-06-01 00:00:00.000 2006-06-01 00:00:00.000
2006-06-01 00:00:00.000 2006-06-01 00:00:00.000
2006-06-01 00:00:00.000 2006-06-01 00:00:00.000
2006-06-01 00:00:00.000 2006-06-01 00:00:00.000
2006-06-01 00:00:00.000 2006-06-01 00:00:00.000
2006-06-01 00:00:00.000 2006-06-01 00:00:00.000
2006-06-01 00:00:00.000 2006-06-01 00:00:00.000
2006-06-01 00:00:00.000 2006-06-01 00:00:00.000
2006-06-01 00:00:00.000 2006-06-01 00:00:00.000
2006-06-01 00:00:00.000 NULL
2006-06-01 00:00:00.000 2006-06-01 00:00:00.000
2006-06-02 00:00:00.000 2006-06-07 00:00:00.000
2006-06-08 00:00:00.000 NULL
2006-06-08 00:00:00.000 NULL
——————————————————-
From this table I want unique postion date record, don’t care about to_date.
select position_date,to_date from fiv_position_limit
group by position_date,to_date position_date to_date
—————————— ——————————————————
2006-06-01 00:00:00.000 NULL
2006-06-01 00:00:00.000 2006-06-01 00:00:00.000
2006-06-02 00:00:00.000 2006-06-07 00:00:00.000
2006-06-08 00:00:00.000 NULL (4 row(s) affected)
———————————- I should not get repeated values of position date. Thank U All

---

add this:
where to_date is not NULL

---

To ranjitjain If we add that position_date to_date
———————- ——-
2006-06-08 00:00:00.000 NULL won’t display….but I want that also
S.Jeya Prabhu "A ship in Harbour is safe,
But that is not what ships are built for."

---

try this:
declare @t1 table(col1 datetime,col2 datetime)
insert into @t1
select
‘2006-06-01 00:00:00.000’, ‘2006-06-01 00:00:00.000’ union all
select
‘2006-06-01 00:00:00.000’, ‘2006-06-01 00:00:00.000’ union all
select
‘2006-06-01 00:00:00.000’, ‘2006-06-01 00:00:00.000’ union all
select
‘2006-06-01 00:00:00.000’, ‘2006-06-01 00:00:00.000’ union all
select
‘2006-06-01 00:00:00.000’, ‘2006-06-01 00:00:00.000’ union all
select
‘2006-06-01 00:00:00.000’, ‘2006-06-01 00:00:00.000’ union all
select
‘2006-06-01 00:00:00.000’, ‘2006-06-01 00:00:00.000’ union all
select
‘2006-06-01 00:00:00.000’, ‘2006-06-01 00:00:00.000’ union all
select
‘2006-06-01 00:00:00.000’, ‘2006-06-01 00:00:00.000’ union all
select
‘2006-06-01 00:00:00.000’, NULL union all
select
‘2006-06-01 00:00:00.000’, ‘2006-06-01 00:00:00.000’ union all
select
‘2006-06-02 00:00:00.000’, ‘2006-06-07 00:00:00.000’ union all
select
‘2006-06-08 00:00:00.000’, NULL union all
select
‘2006-06-08 00:00:00.000’, NULL select * from @t1 t1 where isnull(col2,0) in
(select top 1 isnull(col2,0) from @t1 where col1=t1.col1)
group by col1,col2

---

Only one record is get affected… select t1.position_date,t1.to_date from fiv_position_limit t1
where isnull(to_date,0) in
(select top 1 isnull(to_date,0) from fiv_position_limit where position_date=t1.position_date)
group by position_date,to_date
position_date to_date
—————————————————— ——————————————————
2006-06-01 00:00:00.000 NULL (1 row(s) affected)

---

select Distinct position_date,to_date from fiv_position_limit Madhivanan Failing to plan is Planning to fail

---

Have you tried the query which i posted above?

---

Ya, Jeya prabhu..you can try what ranjitjain had given. SELECT * FROM fiv_position_limit A
WHERE (ISNULL(to_date, 0) IN
(SELECT TOP 1 isnull(to_date, 0)
FROM fiv_position_limit B
WHERE A.position_date = B.position_date))
GROUP BY position_date, to_date I had tested it out and it worked well.

---

To smy Thank u very much, Its works fine… Thanks to all.

---

or SELECT Distinct * FROM fiv_position_limit A
WHERE (ISNULL(to_date, 0) IN
(SELECT TOP 1 isnull(to_date, 0)
FROM fiv_position_limit B
WHERE A.position_date = B.position_date))
Madhivanan Failing to plan is Planning to fail

---

]]>