Click here to Skip to main content
11,479,170 members (62,834 online)
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# ASP.NET
hello

I am inserting some data in my table on different dates but there are more than 1 record for a particular date now i want to fetch top 2 distinct dates...


my fields are like


Sr.No.______CNAME_____PNAME____DATE

1__________RAM________abc______28/12/2012
2__________MOHAN____XYZ______28/12/2012
3__________SHYAM_____CDF______01/01/2013
4__________FFFF________GHH______01/01/2013
5__________lljl___________err______29/01/2013
6__________dfr__________dsf_____29/01/2013


I want to fetch 29 and 1 but the output is 28,29


can any one help me
Posted 4-Dec-12 4:19am
Comments
CHill60 at 4-Dec-12 9:25am
   
Can you post the code you used to get that output
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 4

Here is an example, hopefully this satisfy what you are looking for

SET DATEFORMAT DMY
 
create table test
(
    id int,
    cName varchar(50),
    PName Varchar(50),
    myDate Datetime
)
go
 
insert into test values (1,'RAM','abc', cast('28/12/2012' as datetime))
insert into test values (2,'MOHAN','XYZ', cast('28/12/2012' as datetime))
insert into test values (3,'SHYAM','CDF', cast('01/01/2013' as datetime))
insert into test values (4,'FFFF','GHH', cast('01/01/2013' as datetime))
insert into test values (5,'lljl','err', cast('29/01/2013' as datetime))
insert into test values (6,'dfr','dsf', cast('29/01/2013' as datetime))
 
select top 2 mydate
from test
group by mydate
order by mydate desc
 

drop table test
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Hi,

Date column datatype should be datetime in table

select distinct top 2 dates from order by date desc
  Permalink  
Comments
Pradeep_kaushik at 4-Dec-12 9:39am
   
i use this query but the output is 28 and 29 it compair only date not month and year
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

hi you need to use this
Select distinct top 2 date from table order by date desc
  Permalink  
Comments
Pradeep_kaushik at 4-Dec-12 9:38am
   
i use this query but the output is 28 and 29 it compair only date not month and year
ShivKrSingh at 5-Dec-12 4:14am
   
Send your table Design. I think you had taken wrong datatype.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

Depending on your database, use TOP or LIMIT clause, e.g. with MySQL:
select distinct DATE from MYTABLE limit 2
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 5

first set your datatype datetime
 
Date column datatype should be datetime in table
 
select distinct top 2 dates from order by date desc
  Permalink  
Comments
Pradeep_kaushik at 4-Dec-12 11:49am
   
ok but now it takes time also how can i insert only date
bhavesh002 at 5-Dec-12 1:53am
   
use ToShortDateString() method for get only date
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 6

try this

select top 2 yourdatecolumnname,count(yourdatecolumnname)
from yourtablename group by yourdatecolumnname

you can also user order by for your according.
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 7

I'm assuming you want the record id's 3-6 as those four records have the top two dates. Try this:

Select myTable.* from MyTable inner join
(select distinct top 2 Convert(DateTime,[Date],102) as [Date] order by Convert(datetime,[Date],102) desc) TopDates on Convert(datetime,MyTable.[Date],102) = TopDates.[Date]
  Permalink  
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 215
1 F-ES Sitecore 195
2 OriginalGriff 165
3 Frankie-C 120
4 DamithSL 95
0 Sergey Alexandrovich Kryukov 7,890
1 OriginalGriff 7,366
2 Sascha Lefèvre 3,064
3 Maciej Los 2,491
4 Richard Deeming 2,335


Advertise | Privacy | Mobile
Web03 | 2.8.150520.1 | Last Updated 4 Dec 2012
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100