CODEWITHSUNDEEP
sqlsql-server
san4u28
san4u28

Reputation: 19

Select Distinct Records for min date

I Have a query where In I need to select all the distinct ID's from the table, also need to select only the min(date) so that I get records that were inserted 1st and not ID's for all dates.

Basically this is what I am looking for -

Table 1 || Table 2-
ID || ID Date

1 || 1 11/11/2010
1 || 1 10/11/2010
3 || 3 12/01/2010
4 || 4 01/01/2010
4 || 4 02/01/2010

So i need to get all records from Table 2(table1.ID=table2.ID) which has the Minimum Date along with that ID

Result here would be
1 10/11/2010
3 12/01/2010
4 01/01/2010

Here is my query

select u.firstName,u.lastName ,count(*) as theCount
from tbl_appts_change_log c,tbl_appts a, tbl_users u
where  c.appt_id=a.ID
and c.user_id=u.userID
 and c.appt_id in ( select  c.appt_id,min(c.date) from tbl_appts_change_log c, tbl_appts a
                        where  c.appt_id=a.ID
                        and a.satellite_id='160' GROUP BY c.appt_id)    
group by u.firstName,u.lastName
 order by count(*) desc,u.firstName,u.lastName

Upvotes: 0

Views: 6049

Answers (6)

Anil Soman
Anil Soman

Reputation: 2467

If what you are looking for is-

Table 1 || Table 2-
ID || ID Date

1 || 1 11/11/2010
1 || 1 10/11/2010
3 || 3 12/01/2010
4 || 4 01/01/2010
4 || 4 02/01/2010

Try using this:

SELECT DISTINCT i1.id, i2.dt
FROM Table1 i1 JOIN (SELECT id, min(date) dt FROM Table2 GROUP BY id) i2
ON i1.id=i2.id

Upvotes: 0

Sadhir
Sadhir

Reputation: 423

I am ignoring your code as it seems to be a completely different scenario from what you have described in your question i.e Table1 and Table2. If all you want is the minimum date for each ID all you need is 

SELECT T1.ID,   MIN(T2.Date)
FROM Table1 T1
    JOIN Table2 T2 ON T1.ID = T2.ID
GROUP BY T1.ID

But i am guessing what you really wanted was something like this?

Table 1 || Table 2-
ID || ID Date Desc

1 || 1 11/11/2010 AAA
1 || 1 10/11/2010 BBB
3 || 3 12/01/2010 CCC
4 || 4 01/01/2010 DDD
4 || 4 02/01/2010 EEE

And the expected result

1 10/11/2010 BBB
3 12/01/2010 CCC
4 01/01/2010 DDD

This is slightly more complicated than just doing a group by, and there are two ways to solve it. You could try both and see which one performs best

Method 1 : Using Row number

;WITH ResultCTE AS
(
    SELECT T2.ID, T2.Date, T2.Desc,
       RowNumber = ROW_NUMBER() OVER(PARTITION BY T2.ID ORDER BY T2.Date ASC)   
    FROM Table1 T1
          JOIN Table2 T2 ON T1.ID = T2.ID
)    
SELECT ID, Date, Desc
FROM ResultCTE    
WHERE RowNumber = 1

Method 2: Nested query

;WITH ResultCTE AS
(
    SELECT T2.ID, MIN(T2.Date) AS Date
    FROM Table1 T1
        JOIN Table2 T2 ON T1.ID = T2.ID
    GROUP BY T2.ID
)
SELECT T.ID, T.Date, T.Desc
FROM Table2 T
    JOIN ResultCTE R
        ON R.ID = T.ID AND R.Date = T.Date

Upvotes: 1

Leslie
Leslie

Reputation: 3644

maybe:

select u.firstName,u.lastName ,count(*) as theCount
from tbl_appts_change_log c
INNER JOIN tbl_appts a on c.appt_id=a.ID
INNER JOIN tbl_users u on c.user_id=u.userID
INNER JOIN ( select  c.appt_id,min(c.date) as LastDate 
    from tbl_appts_change_log c 
    INNER JOIN  tbl_appts a on  c.appt_id=a.ID
    Where a.satellite_id='160' GROUP BY c.appt_id) d
on c.appt_id = d.appt_id and c.date = d.LastDate
group by u.firstName,u.lastName
order by count(*) desc,u.firstName,u.lastName

Upvotes: 1

Martin Schapendonk
Martin Schapendonk

Reputation: 13486

Is this what you're after?

select u.firstName,u.lastName ,count(*) as theCount
from tbl_appts_change_log c,tbl_appts a, tbl_users u
where  c.appt_id=a.ID
and c.user_id=u.userID
and a.satellite_id = '160'
and c.date = (
  select min(date) 
  from tbl_appts_change_log c, tbl_appts a
  where c.appt_id = a.id
  and a.satellite_id = '160'
)
group by u.firstName,u.lastName
order by count(*) desc,u.firstName,u.lastName

Upvotes: -1

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171371

select u.firstName,u.lastName,count(*) as theCount,cm.MinDate
from ( 
    select c.appt_id,min(c.date) as MinDate
    from tbl_appts_change_log c, tbl_appts a 
    where c.appt_id=a.ID 
        and a.satellite_id='160' 
    GROUP BY c.appt_id
    ) cm 
inner join tbl_appts_change_log c on cm.appt_id = c.appt_id and cm.MinDate = c.date 
inner join tbl_appts a on c.appt_id=a.ID 
inner join tbl_users u on c.user_id=u.userID 
group by u.firstName,u.lastName 
order by count(*) desc,u.firstName,u.lastName

Upvotes: 0

aw crud
aw crud

Reputation: 8891

SELECT u.firstName,
  u.lastName ,
  COUNT(*) AS theCount
FROM tbl_appts_change_log c,
  tbl_appts a,
  tbl_users u
WHERE c.appt_id=a.ID
AND c.user_id  =u.userID
AND a.satellite_id = '160'
AND c.date = (SELECT MIN(ci.date)
  FROM tbl_appts_change_log ci,
    tbl_appts ai
  WHERE ci.appt_id   = ai.ID
  AND ci.appt_id = c.appt_id
  AND ai.satellite_id= a.satellite_id
  )
GROUP BY u.firstName,
  u.lastName
ORDER BY COUNT(*) DESC,
  u.firstName,
  u.lastName

Upvotes: 0

Related Questions