CODEWITHSUNDEEP
sql-servergroup-bysql-order-byunique
sp_m
sp_m

Reputation: 2705

Get the smallest date from table with unique records in sql server

I have table say VendorReport in this table i have three columns ID,PrefixId,Download_date data in my table is as follow

ID      PrefixId Download_date

1   VIS017  28-09-2012
2   VIS028  29-09-2012
3   VIS035  29-09-2012
4   VIS028  30-09-2012
5   VIS028  29-09-2012
6   VIS028  01-10-2012
7   VIS025  30-09-2012

i want the unique PrefixId records with smallest date as show below

1   VIS017  28-09-2012
2   VIS028  29-09-2012
3   VIS035  29-09-2012
4   VIS025  30-09-2012

so i have tried this query but not getting expected result.

select VendorReport.PrefixId,VendorReport.Download_Date from VendorReport
join (select PrefixId, MIN(Download_Date) d_date from VendorReport group by PrefixId) t2 on VendorReport.PrefixId= t2.PrefixId order by VendorReport.Download_Date asc

Upvotes: 1

Views: 74

Answers (4)

Ankit
Ankit

Reputation: 207

I'M new in sql server pls try this

select prefixId,min(download_date) as download_date from #abc group by prefixId order by prefixId asc

Upvotes: 1

valex
valex

Reputation: 24144

It's not clear what you want to get. Hope this will help:

WITH T AS
(
select 
     VendorReport.*,
     ROW_NUMBER() OVER (PARTITION BY PrefixID 
                        ORDER BY Download_date, ID) as RowNum
from VendorReport
)
SELECT ID,PrefixId, Download_date 
FROM T 
WHERE RowNum=1
Order by Download_Date DESC

SQLFiddle demo

Upvotes: 0

Kishore
Kishore

Reputation: 846

Try this..............

select Row_number() over ( order by x.Download_date),x.PrefixId,x.Download_date
(
select PrefixId,Min(Download_date)  Download_date
from 
VendorReport 
group by Prefixid
) x

Upvotes: 0

Dattatray Kale
Dattatray Kale

Reputation: 81

Here you go

create table #VendorReport(
 ID int,
 PrefixId nvarchar(50),
 Download_date datetime
)


insert into #VendorReport values(1,'IS017','2012-09-28');
insert into #VendorReport values(2,'IS028','2012-09-29');
insert into #VendorReport values(3,'IS035','2012-09-29');
insert into #VendorReport values(4,'IS028','2012-09-30');
insert into #VendorReport values(5,'IS028','2012-09-29');
insert into #VendorReport values(6,'IS028','2012-10-01');
insert into #VendorReport values(7,'IS025','2012-09-30');

 select * from #VendorReport
 select ROW_NUMBER() OVER(ORDER BY PrefixId) as Id, PrefixId, min(Download_date) as Download_date from #VendorReport group by PrefixId


drop table #VendorReport

Upvotes: 0

Related Questions