3

situation is that, i want to prepare datasets for front end chart. the raw data from table contains

LAC | FLUID_MERIT|FA_BDATE   |GRP |FARM_FK
------------------------------------------
0   |234.56      |2020-01-01 |12  |10048
1   |234.56      |2009-01-01 |13  |10048
10  |234.56      |2020-01-01 |13  |10048
0   |234.56      |2020-01-01 |13  |10049
2   |234.56      |2009-01-01 |12  |10049
3   |234.56      |2009-01-01 |12  |10048
0   |234.56      |2020-01-01 |12  |10048

so to prepare the average dataset i want is group by LAC and beside there is condition for LAC = 0 where it must be separated by month range. the query below is a sample

select N'0 ( 01-06 M )' as YDS ,CAST(AVG(FLUID_MERIT) as decimal(10, 2)) AS DataSet 
from HerdAnalytics_tbl 
where GRP = 12 and LAC = 0 and DATEDIFF(MONTH, FA_BDATE , GETDATE()) <= 6 

union 

select N'0 ( 07-12 M )' as YDS ,CAST(AVG(FLUID_MERIT) as decimal(10, 2)) AS DataSet 
from HerdAnalytics_tbl 
where GRP = 12 and LAC = 0 and DATEDIFF(MONTH, FA_BDATE , GETDATE()) > 6 and DATEDIFF(MONTH, FA_BDATE , GETDATE()) <= 12 

union 

select N'0 ( 12 < M )' as YDS ,CAST(AVG(FLUID_MERIT) as decimal(10, 2)) AS DataSet 
from HerdAnalytics_tbl 
where GRP = 12 and LAC = 0 and DATEDIFF(MONTH, FA_BDATE , GETDATE()) > 12 

union 

select CAST(LAC as varchar),CAST(AVG(FLUID_MERIT) as decimal(10, 2)) AS DataSet 
from HerdAnalytics_tbl where GRP = 12 and LAC in (select LAC from HerdAnalytics_tbl 
where FARM_FK = 10048 and LAC != 0)  
group by LAC

union 

select 'TOTAL' as YDS ,CAST(AVG(FLUID_MERIT) as decimal(10, 2)) AS DataSet 
from HerdAnalytics_tbl 
where GRP = 12

when i use union to merge them i could not use order by in the 4th query and also it sort by LAC as a varchar data so lac 10 comes after 1 since it is a varchar as shown below

YDS           | DataSet
------------------------
0 ( 01-06 M ) | 117.78
0 ( 07-12 M ) | 465.26
0 ( 12 < M )  | NULL
1             | 292.58
10            | -62.55
2             | 321.40
3             | 278.24
4             | 308.68
5             | 267.48
6             | 229.36
7             | 165.18
8             | 105.14
9             | 65.68
TOTAL         | 149.95

is there any way to sort properly the 4th query

3

The Order by clause is the only one that guarantee the order.

But if you really want in back end , then you can add some additional columns/attributes to make it easier.
I added a grp = group level and a subgroup grp_2 =group level 2.

For 0 ( 01-06 M ),0 ( 07-12 M ),0 ( 12 < M ), the grp = 0
For monthly records, grp = 1
For TOTAL, grp = 2

And for grp_2, we have: 0 ( 01-06 M )grp_2 = 1,0 ( 07-12 M ) grp_2 = 2,0 ( 12 < M ) grp_2 = 3
For monthly records, grp_2 = LAC
For TOTAL, grp_2 = 0

And so, we are ordering by ORDER BY a.grp,a.grp_2

SELECT a.YDS,a.DataSet
FROM
(
    select N'0 ( 01-06 M )' as YDS ,CAST(AVG(FLUID_MERIT) as decimal(10, 2)) AS DataSet ,0 as grp,1 as grp_2
    from HerdAnalytics_tbl 
    where GRP = 12 and LAC = 0 and DATEDIFF(MONTH, FA_BDATE , GETDATE()) <= 6 

    union 

    select N'0 ( 07-12 M )' as YDS ,CAST(AVG(FLUID_MERIT) as decimal(10, 2)) AS DataSet,0 ,2
    from HerdAnalytics_tbl 
    where GRP = 12 and LAC = 0 and DATEDIFF(MONTH, FA_BDATE , GETDATE()) > 6 and DATEDIFF(MONTH, FA_BDATE , GETDATE()) <= 12 

    union 

    select N'0 ( 12 < M )' as YDS ,CAST(AVG(FLUID_MERIT) as decimal(10, 2)) AS DataSet ,0,3
    from HerdAnalytics_tbl 
    where GRP = 12 and LAC = 0 and DATEDIFF(MONTH, FA_BDATE , GETDATE()) > 12 

    union 

    select CAST(LAC as varchar),CAST(AVG(FLUID_MERIT) as decimal(10, 2)) AS DataSet ,1,Lac
    from HerdAnalytics_tbl 
    where GRP = 12 
        and LAC in (select LAC from HerdAnalytics_tbl where FARM_FK = 10048 and LAC != 0)  
    group by LAC

    union 

    select 'TOTAL' as YDS ,CAST(AVG(FLUID_MERIT) as decimal(10, 2)) AS DataSet ,2,0
    from HerdAnalytics_tbl 
    where GRP = 12
)as a
ORDER BY a.grp,a.grp_2

output:

YDS             DataSet
0 ( 01-06 M )   NULL
0 ( 07-12 M )   NULL
0 ( 12 < M )    234.56
3               234.56
4               234.56
10              234.56
TOTAL           234.56

dbfiddle

4
  • Brilliant and simple. thank you Feb 23 at 8:41
  • You're welcome!
    – Sabin B
    Feb 23 at 9:09
  • 3
    this order by should be done in front end. Highly debatable. The database engine is designed to sort efficiently as it is an inherent part of SQL execution. Won't make much difference with a small number of rows but such statements are generally foolish to make.
    – SMor
    Feb 23 at 13:58
  • @SMor agreed,the above data set is a part of large data model and for the same project i did some preparation in data layer and also front which results in poor performance on large data (but not big data) from that moment i do all requirements in database level even serializing to json which i observed huge deference with respect to serializing in code level. Feb 24 at 1:22
1

The above answer clearly works an alternative (simpler ?) solution would be if you add leading zeros to the varchar YDS representation of the number then everything will sort correctly, simple example below you would probably need to adjust the update to exclude the values like 0 ( 01-06 M ).

create table #t (num int, strnumber varchar(10))

insert #t (num, strnumber) values (2,''), (4,''),(7,''),(21,''),(33,''),(5,''),(8,'')

update #t set strnumber = right('00'+ convert(varchar(10), num), 2)

select *  from #t order by strnumber

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.