select distinct top 2 dates from database

Question

1.50/5 (2 votes)

See more:

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:19am

Pradeep_kaushik

Add a Solution

Comments

CHill60 4-Dec-12 9:25am

Can you post the code you used to get that output

7 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Simon_Whale · Answer 1 · 2012-12-04T03:37:00

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

SQL

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

ashokmmrk · Answer 2 · 2012-12-04T03:28:00

Solution 1

Hi,

Date column datatype should be datetime in table

select distinct top 2 dates from order by date desc

Posted 4-Dec-12 3:28am

ashokmmrk

Comments

Pradeep_kaushik 4-Dec-12 9:39am

i use this query but the output is 28 and 29 it compair only date not month and year

ShivKrSingh · Answer 3 · 2012-12-04T03:28:00

Solution 2

hi you need to use this
Select distinct top 2 date from table order by date desc

Posted 4-Dec-12 3:28am

ShivKrSingh

Comments

Pradeep_kaushik 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 5-Dec-12 4:14am

Send your table Design. I think you had taken wrong datatype.

CPallini · Answer 4 · 2012-12-04T03:34:00

Solution 3

Depending on your database, use TOP or LIMIT clause, e.g. with MySQL:

SQL

select distinct DATE from MYTABLE limit 2

Posted 4-Dec-12 3:34am

CPallini

bhavesh002 · Answer 5 · 2012-12-04T05:16:00

Solution 5

SQL

first set your datatype datetime

Date column datatype should be datetime in table

select distinct top 2 dates from order by date desc

Posted 4-Dec-12 5:16am

bhavesh002

Comments

Pradeep_kaushik 4-Dec-12 11:49am

ok but now it takes time also how can i insert only date

bhavesh002 5-Dec-12 1:53am

use ToShortDateString() method for get only date

avjabalpur · Answer 6 · 2012-12-04T06:57:00

Solution 6

try this

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

you can also user order by for your according.

Posted 4-Dec-12 6:57am

avjabalpur

Saintd81 · Answer 7 · 2012-12-04T08:23:00

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

SQL

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]

select distinct top 2 dates from database

7 solutions

Solution 4

Solution 1

Solution 2

Solution 3

Solution 5

Solution 6

Solution 7

Add your solution here

Preview 0