order by/group by question 1

[ktucci]

i have a query i need grouped by col1 and ordered by the values of col1 and col2 matching first then the non matching values then the next group in col1

this is the ddata as it is nowsee 2nd out output for desired result)

col1 col2
---- ----
12 21
12 22
12 12
12 23
13 24
13 13
13 25
13 26
14 27
14 28
14 14
14 29

i would like to output this likedesired result)

col1 col2
---- ----
12 12
12 21
12 22
12 23
13 13
13 24
13 25
13 26
14 14
14 27
14 28
14 29

any help will be greatly appreciated...thanks in advance

keith

 

[jhall156]

if these are integers then I have 2 solutions depending on whether col2 can be less than col1 and you still want the results to begin with the match and then proceed from there. The second order by clause is if you want the lowest col2 value first regardless of whether it is less than col1.

ORDER BY col1, col1 % col2, CONVERT(numeric(8,4),col2)/CONVERT(numeric(8,4),col1)

or

ORDER BY col1, CONVERT(numeric(8,4),col2)/CONVERT(numeric(8,4),col1), col1 % col2


JHall

 

[ktucci]

thanks for the quick reply to my post...

it worked for one table that had integers but i also have a table that is varchar...for that matter it is a table of telephone numbers (5555555555)..is there a way to do the same thing for a non-integer datatype

thanks

keith

 

[jhall156]

Try wrapping a VAL() around the columns. JHall

 

[ktucci]

thanks for your help...it put me on the right track...actually this is the solution we came up with over here that ended up working...thanks again

select col1, col2
from tbl_1
order by col1,
case when col1 = col2 then 0 else 1 end, col2

 

[pankajv]

I am not sure why these many things are required. I feel below query should give the expected results:

select col1,col2
from tab
group by col1,col2

correct me if I am wrong.

 

[tlbroadbent]

pankajv,

The group by is insufficient on its own because if it does order the result set (which it will not do in SQL 7 and higher) The results would be in the order of the col11 and then col2. This will not be the correct order if col2 has values less that col1.
[tt]
Col1 Col2
12 8
12 10
12 12
12 14[/tt]

The reults desired are...
[tt]
Col1 Col2
12 12
12 8
12 10
12 14[/tt] Terry L. Broadbent - Salt Lake City, UT
Home of the 2002 Winter Olympics (Feb 8-24)

http://www.saltlake2002.com

 

[pankajv]

So in that case group by along with order by should work.
i.e
Select Col1, Col2
FRom Tab
group by col1,Col2
order by Col2,Col1

 

[tlbroadbent]

No. ordering by col2 then col1 would return the following result.

The result with your latest suggestion would be.
[tt]
Col1 Col2
12 8
12 10
12 12
12 14

With multiple values in col1 the is even further from the desired result.

Col1 Col2
10 8
12 8
10 9
12 10
12 12
10 14
12 14
10 19[/tt] Terry L. Broadbent - Salt Lake City, UT
Home of the 2002 Winter Olympics (Feb 8-24)

http://www.saltlake2002.com

 

[Dugy]

I tend to agree with pankajv in that these solutions are over complicating it. It simply requires,

SELECT * FROM tbl
ORDER BY col1, col2

Have tested this and it produces exactly the result as displayed in the initial post.

 

[Dugy]

Oops! stupid me!

"matching first then the non matching values"