Finding lowest date among each category

Here we have data scattered in table like below

Subject                        Date

------------------------------ -----------------------

Data & File Structure          2014-11-18 10:12:57.767

System Software                2014-11-26 10:12:57.767

DBMS                           2014-11-18 10:12:57.767

Data & File Structure          2014-11-24 10:12:57.767

DBMS                           2014-11-16 10:12:57.767

Data & File Structure          2014-11-25 10:12:57.767

System Software                2014-11-23 10:12:57.767

Data & File Structure          2014-11-24 10:12:57.767

DBMS                           2014-11-29 10:12:57.767

Now we are interested in finding lowest date for each subject.

Let’s create table and try to find the solution using t-sql concepts.

create table Class

([Subject] varchar(30),

[Date] datetime)

Command(s) completed successfully.

So here our table is ready, lets insert data according to problem statement.

insert into Class

select 'Data & File Structure', getdate() - 12 union all

select 'System Software', getdate() - 4 union all

select 'DBMS', getdate() - 12 union all

select 'Data & File Structure', getdate() - 6 union all

select 'DBMS', getdate() - 14 union all

select 'Data & File Structure', getdate() - 5 union all

select 'System Software', getdate() - 7 union all

select 'Data & File Structure', getdate() - 6 union all

select 'DBMS', getdate() - 1

Now our table is ready with data. Ok, its time to fire a query taking both the column under group by

select Subject,Date from Class

group by Subject, Date

Subject                        Date

------------------------------ -----------------------

Data & File Structure          2014-11-18 12:33:53.440

Data & File Structure          2014-11-24 12:33:53.440

Data & File Structure          2014-11-25 12:33:53.440

DBMS                           2014-11-16 12:33:53.440

DBMS                           2014-11-18 12:33:53.440

DBMS                           2014-11-29 12:33:53.440

System Software                2014-11-23 12:33:53.440

System Software                2014-11-26 12:33:53.440

Now, we are interested in finding lowest date among each subject, so we can apply MIN aggregate function to date field taking subject as group by field.

select Subject,min(Date) from Class

group by Subject

Subject                       

------------------------------ -----------------------

Data & File Structure          2014-11-18 12:33:53.440

DBMS                           2014-11-16 12:33:53.440

System Software                2014-11-23 12:33:53.440

Yup we got our work done; this is what we were expecting.

Get your Sofa in washroom and do code J

Post Reference: Vikram Aristocratic Elfin Share