Say I have the following table / data:
create table #a(id int, name varchar(2), score int)
go
insert #a values(0, 'a1', 1)
insert #a values(1, 'b1', 0)
insert #a values(2, 'c1', 1)
insert #a values(3, 'd1', 0)
insert #a values(4, 'd2', 1)
insert #a values(5, 'e1', 0)
insert #a values(6, 'e2', 2)
insert #a values(7, 'e3', 1)
insert #a values(8, 'e4', 0)
I'd like to select these rows:
id name score
1 b1 0
2 c1 1
4 d2 1
6 e2 2
Criteria:
This is what I came up with:
select id, name, score
into #b
from #a
where id > 0
group by left(name, 1)
having score = max(score)
go
select f.*
from #b f
left join #b g on left(g.name, 1) = left(f.name, 1) and g.name > f.name
where g.name is null
order by f.name
Can this be done better in terms of not using a temp table / two queries / repetition (all these lefts) and efficiency in general?
Assumptions
name is uniqueTo test the tie-breaker logic we'll add another 'e' row:
insert #a values (9,'e5',2) -- same score as the 6/e2/2 record
Since SAP(Sybase) ASE has quite a few limitations ...
rank() functionrow_number() functionoffset/limit clausestop clause in sub-queriesorder by clause in sub-queries... we need to get a little 'creative' (read: this is going to get a bit convoluted)
First thing we'll do is find the max score for each of the single characters where id > 0:
select left(name,1) as name1,
max(score) as mscore
from #a
where id > 0
group by left(name,1)
order by 1
go
name1 mscore
----- -----------
b 0
c 1
d 1
e 2
Next we'll join this result set with the original table, matching rows based on the first character and score = max(score):
select a2.name1,
a1.name,
a2.mscore
from #a a1
join (select left(name,1) as name1,
max(score) as mscore
from #a
where id > 0
group by left(name,1)) a2
on left(a1.name,1) = a2.name1
and a1.score = a2.mscore
and a1.id > 0
order by 1,2
go
name1 name mscore
----- ---- -----------
b b1 0
c c1 1
d d2 1
e e2 2
e e5 2
Next we'll address the tie-breaker rule; we can handle this by applying the max() function to our a1.name column (making sure to add the appropriate group by clause):
select a2.name1,
max(a1.name) as mname,
a2.mscore
from #a a1
join (select left(name,1) as name1,
max(score) as mscore
from #a
where id > 0
group by left(name,1)) a2
on left(a1.name,1) = a2.name1
and a1.score = a2.mscore
and a1.id > 0
group by a2.name1,
a2.mscore
order by 1,2
go
name1 mname mscore
----- ----- -----------
b b1 0
c c1 1
d d2 1
e e5 2
Last part of the puzzle is to tie this final result back to the original table to obtain the id:
select a3.id,
a4.mname as 'name',
a4.mscore as 'score'
from #a a3
join (select a2.name1,
max(a1.name) as mname,
a2.mscore
from #a a1
join (select left(name,1) as name1,
max(score) as mscore
from #a
where id > 0
group by left(name,1)) a2
on left(a1.name,1) = a2.name1
and a1.score = a2.mscore
and a1.id > 0
group by a2.name1,
a2.mscore) a4
on a3.name = a4.mname
order by 1,2
go
id name score
----------- ---- -----------
1 b1 0
2 c1 1
4 d2 1
9 e5 2
NOTE: Above queries/results verified on SAP(Sybase) ASE 16.0 SP03 PL01.
Net result ...
While it's possible to do what's desired with a single query, the coding is a bit convoluted (and likely a bit harder to maintain).
The original code (2 queries and an intermediate temp table) is a bit easier to understand (and likely a bit easier to maintain).