order by - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

order by

Author

Topic

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-06-07 : 00:07:55

hi all,
ive no idea how to perform this order by query.. i want to sort by qty, but at the same time i want to categorized by tID.. if i order by qty, the tID will not stay in group.. here's my sample data :-
(i want to sort by qty when Flag='AVA', the rest tID will follow the 'AVA' tID)


DECLARE	@tbl1 TABLE (tID varchar(12), qty float, flag varchar(5) )

INSERT	@tbl1
SELECT	'A', null, 'AVA' UNION ALL
SELECT	'A', '2', 'PICK' UNION ALL
SELECT	'B', '1', 'AVA' UNION ALL
SELECT	'B', null, 'PICK' UNION ALL
SELECT	'C', null, 'AVA' UNION ALL
SELECT	'C', '1', 'PICK'

select * from @tbl1
order by qty 

---expected result
DECLARE	@tblResult TABLE (tID varchar(12), qty float, flag varchar(5) )

INSERT	@tblResult
SELECT	'A', null, 'AVA' UNION ALL
SELECT	'A', '2', 'PICK' UNION ALL
SELECT	'C', null, 'AVA' UNION ALL
SELECT	'C', '1', 'PICK' UNION ALL
SELECT	'B', '1', 'AVA' UNION ALL
SELECT	'B', null, 'PICK' 

select * from @tblresult

~~~Focus on problem, not solution ¯\(º_o)/¯ ~~~

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-06-07 : 00:13:28

did u try ...

order by tId, qty

--------------------------------------------------
S.Ahamed

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-06-07 : 00:25:37

yes..
tid, qty = will ignore the qty order.. means it puts B,1.0,AVA on top of C,NULL,AVA
qty,tid = will puts the tid in chaos..

maybe need some case but i dunnno how
thanks btw pbguy

~~~Focus on problem, not solution ¯\(º_o)/¯ ~~~

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2007-06-07 : 01:58:04

Hi,

u need to take a temp table to order the tid's first.

DECLARE @tbl1 TABLE (tID varchar(12), qty float, flag varchar(5) )

INSERT @tbl1
SELECT 'A', null, 'AVA' UNION ALL
SELECT 'A', '2', 'PICK' UNION ALL
SELECT 'B', '1', 'AVA' UNION ALL
SELECT 'B', null, 'PICK' UNION ALL
SELECT 'C', null, 'AVA' UNION ALL
SELECT 'C', '1', 'PICK'

declare @t table (i int identity(1, 1), tid varchar(10))

insert into @t(tid)
select tid
from @tbl1 t
where flag = 'ava'
order by qty

select t1.*, t2.i from @tbl1 t1
inner join @t t2 on t1.tid = t2.tid
order by t2.i

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-06-07 : 02:10:53

hi.. thanks..
it worked although actually i'd prefer something similar to order by with combination of case clause maybe.. anyone? :P

~~~Focus on problem, not solution ¯\(º_o)/¯ ~~~

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-07 : 06:29:10

quote:
Originally posted by PeterNeo

Hi,

u need to take a temp table to order the tid's first.

DECLARE @tbl1 TABLE (tID varchar(12), qty float, flag varchar(5) )

INSERT @tbl1
SELECT 'A', null, 'AVA' UNION ALL
SELECT 'A', '2', 'PICK' UNION ALL
SELECT 'B', '1', 'AVA' UNION ALL
SELECT 'B', null, 'PICK' UNION ALL
SELECT 'C', null, 'AVA' UNION ALL
SELECT 'C', '1', 'PICK'

declare @t table (i int identity(1, 1), tid varchar(10))

insert into @t(tid)
select tid
from @tbl1 t
where flag = 'ava'
order by qty

select t1.*, t2.i from @tbl1 t1
inner join @t t2 on t1.tid = t2.tid
order by t2.i

if you change the sequence of the data a bit, the result will be different
Try with this


INSERT	@tbl1
SELECT	'C', null, 'AVA' UNION ALL
SELECT	'C', '1', 'PICK' UNION ALL
SELECT	'A', null, 'AVA' UNION ALL
SELECT	'A', '2', 'PICK' UNION ALL
SELECT	'B', '1', 'AVA' UNION ALL
SELECT	'B', null, 'PICK'

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-07 : 06:37:22

try this . . . not sure the ordering is what you required. Try with more sample data


DECLARE	@tbl1 TABLE (tID varchar(12), qty float, flag varchar(5) )

INSERT	@tbl1
SELECT	'A', null, 'AVA' UNION ALL
SELECT	'A', '2', 'PICK' UNION ALL
SELECT	'B', '1', 'AVA' UNION ALL
SELECT	'B', null, 'PICK' UNION ALL
SELECT	'C', null, 'AVA' UNION ALL
SELECT	'C', '1', 'PICK'

select 	t.*, s.ava_qty
from	@tbl1 t
inner join 
	(
		select	tID, ava_qty = max(qty)
		from	@tbl1
		where	flag	= 'AVA'
		group by tID
	) s
	on	t.tID	= s.tID
order by s.ava_qty, t.tID, t.flag, t.qty

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources