Select Distinct Records for min date

sql select min date from multiple rows
sql select max date for multiple records
sql select min value from multiple rows
sql select min date from multiple columns
sql select min value from multiple columns
sql min date in where clause
sql select row with min value in group
select distinct * from table

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

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

Forums : How to select distinct MIN value using SQL, I have a requirement to retrieve the row with the minimum value for a date field. And, I do not want duplicate rows to be returned, if there are� The SQL SELECT DISTINCT Statement. The SELECT DISTINCT statement is used to return only distinct (different) values. Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values.

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

[Solved] Select distinct id with max date according to department , It should be something like that: Hide Copy Code. SELECT A.RevNo, A. RevContent, A.PIC, A.Created FROM YourTable A INNER JOIN� If you apply the DISTINCT clause to a column that has NULL, the DISTINCT clause will keep only one NULL and eliminates the other. In other words, the DISTINCT clause treats all NULL “values” as the same value. SQL Server SELECT DISTINCT examples. For the demonstration, we will use the customers table from the sample database.

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

Get rows with most recent date for each different item, have an amount (select max(dt) from product), '1 day' )::date as gdt) d cross join --assuming each listed product has an amount on the min date (select distinct� Hi, I am trying to select a distinct group of records from within my data file. I want to get the record with the latest date for each ID. There could be 1, 2, 3, 4

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

MySQL MIN() Function, Functions � Comparison Functions � Date Functions � String Functions � Window Functions The DISTINCT has no effect on the MIN() function like other aggregate In this example, the query checks all values in the column buyPrice of the SELECT MIN(buyPrice) FROM products WHERE productline = ' Motorcycles';. distinct operator. 02/13/2020; 2 minutes to read; In this article. Produces a table with the distinct combination of the provided columns of the input table. T | distinct Column1, Column2 Produces a table with the distinct combination of all columns in the input table. T | distinct * Example. Shows the distinct combination of fruit and price.

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

SQL MIN() function, The SQL MIN function is used to find the minimum value or lowest value of a This function is useful to determine the smallest of all selected values of a column . DISTINCT is not meaningful with MIN function. MIN can be used with numeric, character, and datetime columns, but not with bit columns. Min returns the smallest value in a numeric field and the earliest date or time value in a Date/Time field. On the Design tab, in the Query Setup group, click the down arrow next to All (the Top Values list), and either enter the number of records that you want to see, or select an option from the list.

How can I SELECT rows with MAX(Column value), DISTINCT by , Select BOTH the home and its max date time, then join back to the top table on BOTH the fields: Want to learn SQL from basics! Here's the right� SQL SELECT DISTINCT Statement How do I return unique values in SQL? SELECT DISTINCT returns only distinct (i.e. different) values. SELECT DISTINCT eliminates duplicate records from the results. DISTINCT can be used with aggregates: COUNT, AVG, MAX, etc. DISTINCT operates on a single column. DISTINCT for multiple columns is not supported.

PostgreSQL MIN Function: Get Minimum Value In a Set, You will learn how to use PostgreSQL MIN function to get the minimum value of a set is one of aggregate functions that returns the minimum value in a set of values. In this query, we used a subquery to select the minimum rental rate. We constantly publish useful PostgreSQL tutorials to keep you up-to-date with the � select distinct. Person, Earning, Site, Date from have group by Person. having date=min(date); Quit; In other words, i am trying to select the distinct rows per person by the first earning date. The problem is that there are still some duplicates.

Hi I'm trying to update a column with MIN(Date) based on distinct IDs , Thanks in advance. update #Sample_2019 set Date_Of_Service = a.DOS. from ( Select Unique_ID, MIN(DOS) as DOS. From #Sample_2019 a. SELECT DISTINCT a.UserName , a.DeviceName , a.DateStamp FROM DocLogon a WHERE a.DateStamp = (SELECT MAX(DateStamp) FROM DocLogon c WHERE c.DeviceName = a.DeviceName) AND A.DeviceName <> '' ORDER BY a.DeviceName. The excluding of blank a.DeviceName only subtracts 3 rows.

Comments
  • Could you post your schema? I don't understand what you are trying to do with this query....
  • I was not able to use aggregate function and a second column for the subquery..it was giving all sorts of errors....
  • @Santosh, try to rewrite what you exactly need in plain english without using SQL keywords such as distinct and min
  • I have re-written what I was looking for..hope that helps...also I found the solution, look at Leslie's solution as I used that...
  • Thanks a lot everyone for your help....
  • hi leslie..thanks for that SQL...it works fine...I had tried the Group by but did not try the next part of it !!!
  • great! you should accept it as the answer then! Glad to have been able to help!
  • i need to select ID's for the min date