SQLTeam.com | Weblogs | Forums

Need Output Using order by function


#1

e_id userid username productname discription modelno inputpower voltage size color qty dateadds
1 13 abuzar Drill Bits HKJ3510, 1/2-20UNF NULL NULL NULL 1010 Blue 12 2015-10-06
2 13 abuzar Drill Bits ZWJ2113 JT6 NULL NULL NULL 1/2" TAPER Blue 32 2015-10-06
3 13 abuzar Drill Bits KEY LESS-LIGHT DUTY NULL NULL NULL 13MM Blue 44 2015-10-06
4 13 abuzar Drill Bits HKJ3510, 1/2-20UNF NULL NULL NULL 10
10 Blue 0 2015-10-07
5 13 abuzar Drill Bits ZWJ2113 JT6 NULL NULL NULL 1/2" TAPER Blue 0 2015-10-07
6 13 abuzar Drill Bits KEY LESS-LIGHT DUTY NULL NULL NULL 13MM Blue 0 2015-10-07
7 13 abuzar Drill Bits CHUCK KEY NULL NULL NULL 13MM Blue 0 2015-10-07
13 9 Mustafa110 Drill Bits HKJ3510, 1/2-20UNF NULL NULL NULL 10*10 Blue 45 2015-10-07
14 9 Mustafa110 Drill Bits ZWJ2113 JT6 NULL NULL NULL 1/2" TAPER Blue 456 2015-10-07
15 9 Mustafa110 Drill Bits KEY LESS-LIGHT DUTY NULL NULL NULL 13MM Blue 56 2015-10-07
16 9 Mustafa110 Drill Bits CHUCK KEY NULL NULL NULL 13MM Blue 88 2015-10-07
17 9 Mustafa110 Marble Cutter NULL BPT4 SB NULL NULL 5 2015-10-07
18 13 abuzar Drill Bits KEY LESS-LIGHT DUTY NULL NULL NULL 13MM Blue 0 2015-10-07
19 13 abuzar Impact Drill NULL P123 NULL NULL 1 2015-10-07
20 13 abuzar Marble Cutter NULL BPT4 SB 1240 W 220 wt NULL NULL 1 2015-10-07

Above is my table with data in it.

when i execute the below querry.
select distinct userid,username, convert(varchar(10),dateadds,120) as dateadds from tbl_enq order by convert(varchar(10),dateadds,120) desc

i get the following output see below.

userid username dateadds
13 abuzar 2015-10-07
9 Mustafa110 2015-10-07
13 abuzar 2015-10-06

The Output is which i am getting is not in a format what i want it should be like below..

userid username dateadds
9 Mustafa110 2015-10-07
13 abuzar 2015-10-07
13 abuzar 2015-10-06

Any Help Would Be appreciated Thank You.


#2

From your description I assume you also want it sorted by UserID as well? Then add that to your ORDER BY

order by convert(varchar(10),dateadds,120) desc, userID ASC

OR

order by userID ASC, convert(varchar(10),dateadds,120) desc

#3

Respected Sir Thank You For your reply.
I want the latest record on top.
UserID Column does'nt have unique id.
the column name which is having unique id is "e_id"


#4

use

ORDER BY e_id DESC....

#5

Both the original output and you desired output have the latest record on top....because they both have the same DATEADDS value.When there is a tie like this, the order within that tie is undefined. You can impose a secondary criteria by specifying an additional ORDER BY column which is what Dohsan has already point out.


#6

What is the data-type for the "DateAdds" column?


#7

data type is date


#8

The your ORDER BY should simply be...

ORDER BY DateAdds DESC, UserID Asc

There is no need to format the date for the order by.