Click here to Skip to main content
Search within:



Next

select distinct top 2 dates from database
Sign Up to vote bad
good
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 - 3:19
Pradeep_kaushik536
Add a Solution

Comments
CHill60 - 4 Dec '12 - 9:25
Can you post the code you used to get that output

7 solutions

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  
Posted 4 Dec '12 - 3:37
Simon_Whale31.3K
Hi,
 
Date column datatype should be datetime in table
 
select distinct top 2 dates from order by date desc
  Permalink  
Posted 4 Dec '12 - 3:28
ashokmmrk927
Comments
Pradeep_kaushik - 4 Dec '12 - 9:39
i use this query but the output is 28 and 29 it compair only date not month and year
hi you need to use this
Select distinct top 2 date from table order by date desc
  Permalink  
Posted 4 Dec '12 - 3:28
ShivKrSingh1.1K
Comments
Pradeep_kaushik - 4 Dec '12 - 9:38
i use this query but the output is 28 and 29 it compair only date not month and year
ShivKrSingh - 5 Dec '12 - 4:14
Send your table Design. I think you had taken wrong datatype.
Depending on your database, use TOP or LIMIT clause, e.g. with MySQL:
select distinct DATE from MYTABLE limit 2
  Permalink  
Posted 4 Dec '12 - 3:34
CPallini246K
try this
 
select top 2 yourdatecolumnname,count(yourdatecolumnname)
from yourtablename group by yourdatecolumnname
 
you can also user order by for your according.
  Permalink  
Posted 4 Dec '12 - 6:57
Developer Amit Vishwakarma453
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  
Posted 4 Dec '12 - 8:23
Saintd81340
Edited 4 Dec '12 - 8:29
v2
first set your datatype datetime
 
Date column datatype should be datetime in table
 
select distinct top 2 dates from order by date desc
  Permalink  
Posted 4 Dec '12 - 5:16
bhavesh0021.7K
Comments
Pradeep_kaushik - 4 Dec '12 - 11:49
ok but now it takes time also how can i insert only date
bhavesh002 - 5 Dec '12 - 1:53
use ToShortDateString() method for get only date
Add a Solution

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

  Print Answers RSS
Your Filters
Interested
Ignored
     
0 OriginalGriff 218
1 Sergey Alexandrovich Kryukov 159
2 Santhosh G_ 155
3 Richard MacCutchan 145
4 Maciej Los 136
0 Sergey Alexandrovich Kryukov 10,264
1 OriginalGriff 7,937
2 CPallini 4,201
3 Rohan Leuva 3,522
4 Maciej Los 3,135


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