to sort database according to date.

Question

0.00/5 (No votes)

See more:

Hello,I am working on a project where i have to generate a latest status report of an application which is saved in database as AppNo according to the date.

The hint given to me is that i should first sort my database according to date for a particular AppNo and when the AppNo changes the data just data just above that denotes the latest status of that Application.
But I donot know how to sort it...so kindly help me...

Thank you..

Posted 14-Jul-13 8:31am

Shivangi Rawat

Add a Solution

Comments

[no name] 14-Jul-13 14:33pm

Your hint is telling you to query your data using "ORDER BY".

Reza Alipour Fard 14-Jul-13 14:42pm

Do you must save data in database as sorted data?
For Example if you have bellow records in DB
ID Name Date
-----------------------------
1 Test1 2012-01-01
2 Test2 2013-01-01
And now you want add a recod in 2012-06-30, you table value must be:

ID Name Date
-----------------------------
1 Test1 2012-01-01
2 NewTest 2012-06-30
3 Test2 2013-01-01

Your hint is it?

1 solution

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)

Maciej Los · Accepted Answer · 2013-07-15T09:33:00

Generally, to sort data you need to ues ORDER BY[^] clause.

Have a look at example:

SQL

--define temporary table
DECLARE @apps TABLE(AppNo INT, AppName VARCHAR(30), ModDate DATETIME)

--insert data
INSERT INTO @apps (AppNo, AppName, ModDate)
SELECT 1 AS AppNo, 'ApplicationNo1' AS ApName, '2010-05-11' AS ModDate
UNION ALL SELECT 2, 'ApplicationNo2', '2010-06-11'
UNION ALL SELECT 1, 'ApplicationNo1', '2010-09-01'
UNION ALL SELECT 3, 'ApplicationNo3', '2010-09-21'
UNION ALL SELECT 1, 'ApplicationNo1', '2011-03-15'
UNION ALL SELECT 2, 'ApplicationNo2', '2011-04-23'
UNION ALL SELECT 3, 'ApplicationNo3', '2012-06-13'
UNION ALL SELECT 2, 'ApplicationNo2', '2012-07-01'
UNION ALL SELECT 1, 'ApplicationNo1', '2012-10-18'
UNION ALL SELECT 1, 'ApplicationNo1', '2013-01-01'
UNION ALL SELECT 2, 'ApplicationNo2', '2013-03-01'

--get data sorted by AppNo and last ModDate
SELECT *
FROM @apps
ORDER BY AppNo, ModDate DESC

--get data sorted by AppNo and ModDate; the highest ModNo is for last ModDate
SELECT *, ROW_NUMBER() OVER(PARTITION BY AppNo ORDER BY ModDate DESC) AS ModNo 
FROM @apps

Results:
1. query:

AppNo AppName       ModDate                    ModNo
1     ApplicationNo1  2013-01-01 00:00:00.000
1     ApplicationNo1  2012-10-18 00:00:00.000
1     ApplicationNo1  2011-03-15 00:00:00.000
1     ApplicationNo1  2010-09-01 00:00:00.000
1     ApplicationNo1  2010-05-11 00:00:00.000
2     ApplicationNo2  2013-03-01 00:00:00.000
2     ApplicationNo2  2012-07-01 00:00:00.000
2     ApplicationNo2  2011-04-23 00:00:00.000
2     ApplicationNo2  2010-06-11 00:00:00.000
3     ApplicationNo3  2012-06-13 00:00:00.000
3     ApplicationNo3  2010-09-21 00:00:00.000

2. query:

AppNo   AppName         ModDate                    ModNo
1	ApplicationNo1	2013-01-01 00:00:00.000	   1
1	ApplicationNo1	2012-10-18 00:00:00.000	   2
1	ApplicationNo1	2011-03-15 00:00:00.000	   3
1	ApplicationNo1	2010-09-01 00:00:00.000	   4
1	ApplicationNo1	2010-05-11 00:00:00.000	   5
2	ApplicationNo2	2013-03-01 00:00:00.000	   1
2	ApplicationNo2	2012-07-01 00:00:00.000	   2
2	ApplicationNo2	2011-04-23 00:00:00.000	   3
2	ApplicationNo2	2010-06-11 00:00:00.000	   4
3	ApplicationNo3	2012-06-13 00:00:00.000	   1
3	ApplicationNo3	2010-09-21 00:00:00.000	   2