Contact Us
  
 
 
    SearchSearch   RegisterRegister   Log inLog in 

Example of sorting in mysql table

Example of sorting in mysql table

 
Post new topic   Reply to topic     Forum Index -> DataBase, RDBMS, MySql, Oracle, db2, SQL Server problems
View previous topic :: View next topic  
Author Message
admin
Site Admin


Joined: 24 Aug 2006
Posts: 30

PostPosted: Sun Dec 16, 2007 10:07 am    Post subject: Example of sorting in mysql table Reply with quote




Sorting is done on table to get descending and ascending order of any particular attribute and field.

Why we need sorting.
This is question in our mind. Suppose we have 20000 records. Every day it added more 500 records, user want to see only those record that inserted in current date. This needs a sorting on date. Sorting in mysql can be on any data type.
e.g
    Integer
    Float
    String
    Double
    Date


Take a table to sort and order by

Code:
 
SELECT iStateID, sStateName, dCreatedDate FROM statemaster s


Code:

+----------+------------------+---------------------+
| iStateID | sStateName       | dCreatedDate        |
+----------+------------------+---------------------+
|        2 | Haryana          | 2007-10-06 12:31:07 |
|        3 | New Delhi        | 2007-10-06 12:31:12 |
|        5 | Punjab           | 2007-10-06 12:31:24 |
|        6 | Jammu & Kashmir  | 2007-10-06 12:31:34 |
|        7 | Himachal Pradesh | 2007-10-06 12:31:46 |
|        8 | Uttar Pradesh    | 2007-10-06 12:31:58 |
|        9 | west bengal      | 2007-10-12 11:43:27 |
+----------+------------------+---------------------+


In this table iStateID is having primary key. This result in automatic sorting by ascending.

Desc sorting mysql

Code:

SELECT iStateID, sStateName, dCreatedDate FROM statemaster s order by iStateID desc


Output of this query

Code:

+----------+------------------+---------------------+
| iStateID | sStateName       | dCreatedDate        |
+----------+------------------+---------------------+
|        9 | west bengal      | 2007-10-12 11:43:27 |
|        8 | Uttar Pradesh    | 2007-10-06 12:31:58 |
|        7 | Himachal Pradesh | 2007-10-06 12:31:46 |
|        6 | Jammu & Kashmir  | 2007-10-06 12:31:34 |
|        5 | Punjab           | 2007-10-06 12:31:24 |
|        3 | New Delhi        | 2007-10-06 12:31:12 |
|        2 | Haryana          | 2007-10-06 12:31:07 |
+----------+------------------+---------------------+


For ascending sorting
Code:

SELECT iStateID, sStateName, dCreatedDate FROM statemaster s order by iStateID asc


Output of this query

Code:

+----------+------------------+---------------------+
| iStateID | sStateName       | dCreatedDate        |
+----------+------------------+---------------------+
|        2 | Haryana          | 2007-10-06 12:31:07 |
|        3 | New Delhi        | 2007-10-06 12:31:12 |
|        5 | Punjab           | 2007-10-06 12:31:24 |
|        6 | Jammu & Kashmir  | 2007-10-06 12:31:34 |
|        7 | Himachal Pradesh | 2007-10-06 12:31:46 |
|        8 | Uttar Pradesh    | 2007-10-06 12:31:58 |
|        9 | west bengal      | 2007-10-12 11:43:27 |
+----------+------------------+---------------------+


end of order by mysql help
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Post new topic   Reply to topic     Forum Index -> DataBase, RDBMS, MySql, Oracle, db2, SQL Server problems All times are GMT
Page 1 of 1

 
Jump to:  
  Quick Post a Reply Topic
     
  Name (required)  
     
  Email Address (will not be published) (required)  
     
  Subject of topic (required)  
     
   
     
   
     
  Advertisement  
     
 
     


  More Links  
 
JSP Introduction
JSP Setup
Simple JSP Example
JSP Variable
Array Declaration in JSP
JSP Forms and User Input
Form Validation
JSP Cookies
JSP Session Object
Loop, content collection, iterator, conditional check in JSP
JSP Application Object
JSP Include
JSP Response Object
JSP Request Object
JSP Error Handling
JSP File Handling Object
JSP Action Tag
JSP Implicit Objects
JSP Declaration
JSP Directive
JDBC Introduction
JDBC Driver
JDBC Connection
JDBC Prepared/Statement
JDBC ResultSet
JDBC Close Connection object
JDBC Insert
JDBC Display Records
JDBC Update
JDBC Delete
JDBC Sorting
JDBC Query
JDBC Exception
JDBC Add Batch
JDBC Transaction/RollBack